VBA彼此减去两个不同的3维数组 [英] VBA Subtract two different 3 dimensional Arrays from eachother

问题描述

我是Vba的一个新手，需要使用宏和vba解决特定的问题。希望您能为我解决这个问题！

I am a totally newbie in Vba and need to solve a specific problem with a macro and vba. I hope, you can help me with this problem!

我尝试建立一个宏，可以通过以下步骤帮助我：

I try to built a macro which should help me with this steps:

1. 我使用一个 cockpit-file，要彼此减去两个工作表中的所有单元格。我从两个不同的工作簿中获取工作表。 ;-)例如：我想从F11（Workbook2.Worksheet1）中减去单元格F11（Workbook1.Worksheet1），而不是从F12（Workbook2.Worksheet1）中减去F12（workboosk1.worksheet1），... J34（Wb1 J34（Wb2.ws.1）中的.ws1。）


2. 我要更改并选择文件。因此，我需要一个可以在其中选择特定文件的窗口。


3. 为避免错误，应通过vba中的数组完成数学运算。并且应该在其中一本工作簿中添加新的值

我尝试使用循环来解决数学问题，但是不起作用。当我进入减法公式时，出现运行时错误13。

I tried to use a Loop to solve the problem with the math but it doesn't work. When I come to the subtractionformula I get the runtime error 13.

希望您能为我提供帮助！抱歉我的英语不好

Hope you can help me! Sorry for my bad english

那是我的代码

Sub Makro4()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

    'Variabledef
    Dim i As Long 'Index
    Dim j As Long 'Index
    Dim k As Long 'Index
    Dim ArrayA As Variant 'Array
    Dim ArrayB As Variant 'Array
    Dim ArrayC As Variant 'Array
    Dim MyFile1 As String 'Workbookname
    Dim MyFile2 As String 'Workbookname
    Dim wb1 As String 'Workbookname
    Dim wb2 As String 'Workbookname
    Dim WS_Count1 As Integer 'Count Worksheets
    Dim WS_Count2 As Integer 'Count Worksheets
    Dim arrays1 As String 'Dimension
    Dim arrays2 As String 'Dimension


    'Change the actual path

    ChDrive "O:\"
    ChDir "O:[.......]\VBA"


    'Selection first File

    MyFile1 = Application.GetOpenFilename
    Workbooks.Open Filename:=MyFile1, ReadOnly:=True, IgnoreReadOnlyRecommended:=True
    wb1 = ActiveWorkbook.Name
    ArrayA = Workbooks(wb1).Worksheets("01").Range("F11:GL46").Value
    WS_Count1 = ActiveWorkbook.Worksheets.Count


    'Selection second File

    MyFile2 = Application.GetOpenFilename
    Workbooks.Open Filename:=MyFile2, ReadOnly:=True, IgnoreReadOnlyRecommended:=True
    wb2 = ActiveWorkbook.Name
    ArrayB = Workbooks(wb2).Worksheets("01").Range("F11:GL46").Value
    WS_Count2 = ActiveWorkbook.Worksheets.Count



' Calculation of the math - Runtime Error 13

    For k = 1 To WS_Count1
        For i = LBound(ArrayA, 1) To UBound(ArrayA, 1)
            For j = LBound(ArrayA, 2) To UBound(ArrayA, 2)
                ArrayC(i, j) = ArrayA(i, j) - ArrayB(i, j)
            Next j
        Next i

        Worksheets("k").Range("F11:GL34").Value = ArrayC

    Next k


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

推荐答案

ArrayC 是尚未初始化。它的定义为 Variant ，这意味着在将某些内容分配给变量之前，类型是未知的。

ArrayC is not initialized yet. It's defined as Variant, which means, the type is unknown until something gets assigned to the variable.

行 ArrayC（i，j）= ArrayA（i，j）-ArrayB（i，j）您已经假设 ArrayC 拥有一个还没有的数组。

With this line ArrayC(i, j) = ArrayA(i, j) - ArrayB(i, j) you already assume that ArrayC holds an array, which it doesn't yet.

首先像这样在您的头上定义 ArrayC Dim ArrayC（）。这样，它就明确地定义为数组。

First define ArrayC in your head like this Dim ArrayC(). this way it's clearly defined as an array. Still without size though.

现在在行之前对于k = 1对于WS_Count1 ，您可以设置尺寸这样的数组 ReDim ArrayC（UBound（ArrayA，1），UBound（ArrayA，2））这应该创建一个与大小相同的二维数组ArrayA 。
现在您有了一个完全初始化的数组

Now before the line For k = 1 To WS_Count1, you set the dimension of your array this way ReDim ArrayC(UBound(ArrayA,1) ,UBound(ArrayA,2)) This should create an 2D array with the same size as ArrayA. Now you have a fully initialized array

现在您的程序应该可以工作了。

Now your program should work.

这篇关于VBA彼此减去两个不同的3维数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！