对于对象数组中的每个变体

I think I am missing something fundamental error here, but I can't figure it out.

我想我在这里遗漏了一些基本错误,但我无法弄明白。

I have about 64 cells in a spreadsheet that I want to set as variables to use in other equations throughout the spreadsheet. It's important that they are variables for ease of calculations, and because their location changes dynamically depending on other user input. I initially had the following code which worked perfectly fine:

我在电子表格中有大约64个单元格,我想将其设置为整个电子表格中其他方程式中使用的变量。重要的是它们是易于计算的变量,并且因为它们的位置根据其他用户输入而动态变化。我最初有以下代码,完全正常:

Set Ap1thV = range("B32")
Set Ap1tuV = range("B33")
Set Ap1thVdef = range("C32")
Set Ap1tuVdef = range("C33")

and so on and so on for 64 cells (8 columns by 8 rows). The variables are set publicly as objects. Is there a faster way to set all of these variables? It slows down my macro significantly each time it runs, but it needs to run often during the course of the program.

等等64个单元格(8列乘8行)。变量公开设置为对象。是否有更快的方法来设置所有这些变量?每次运行时它都会显着减慢我的宏,但它需要在程序过程中经常运行。

I have tried to consolidate by using the following:

我试图通过使用以下内容进行整合:

cellnums = 32
For Each Vcelldefs In Array(Ap1thV, Ap1tuV, Ap2thV, Ap2tuV, Ap3thV, Ap3tuV, Ap4thV, Ap4tuV)
    Set Vcelldefs = Range("B" & cellnums)
    cellnums = cellnums + 1
Next Vcelldefs
cellnums = 32
For Each Vdefcelldefs In Array(Ap1thVdef, Ap1tuVdef, Ap2thVdef, Ap2tuVdef, Ap3thVdef, Ap3tuVdef, Ap4thVdef, Ap4tuVdef)
    Set Vdefcelldefs = Range("C" & cellnums)
    cellnums = cellnums + 1
Next Vdefcelldefs
cellnums = 32

This code runs, but the variables are set to Nothing, which throws back errors any time these cells/variables are referenced or used in calculations.

此代码运行,但变量设置为Nothing,只要在计算中引用或使用这些单元格/变量,就会抛出错误。

Any help with this would be very much appreciated! If I can explain it better, just ask. Thanks,

任何帮助都将非常感谢!如果我能更好地解释,请问。谢谢,

3 个解决方案

#1

Each time those for loops iterate, it's overwriting the current object. You would need something like separate arrays to hold those range objects. You'd have to start indexing the arrays at 0, if the following makes sense:

每次for循环迭代时,它都会覆盖当前对象。您需要一些像单独的数组来保存这些范围对象。如果以下内容有意义,您必须开始将数组索引为0:

Dim arrVcelldefs(32)
Dim arrVdefcelldefs(32)

cellnums = 32
For Each Vcelldefs In Array(Ap1thV, Ap1tuV, Ap2thV, Ap2tuV, Ap3thV, Ap3tuV, Ap4thV, Ap4tuV)
    arrVcelldefs(cellnums-32) = Range("B" & cellnums)
    cellnums = cellnums + 1
Next Vcelldefs
cellnums = 32
For Each Vdefcelldefs In Array(Ap1thVdef, Ap1tuVdef, Ap2thVdef, Ap2tuVdef, Ap3thVdef, Ap3tuVdef, Ap4thVdef, Ap4tuVdef)
    arrVdefcelldefs(cellnums-32) = Range("C" & cellnums)
    cellnums = cellnums + 1
Next Vdefcelldefs
cellnums = 32

#2

Consider Dim ing them as Static and then Setting them only once.

考虑将它们变暗为静态,然后仅将它们设置一次。

#3

I assume you're storing those Range objects, but all you really want is the value in the cell. You're probably doing

我假设您正在存储那些Range对象,但您真正想要的只是单元格中的值。你可能正在做

Set Ap1thVdef = Range("B23")

then later using it as

然后用它作为

MyResult = 100 * Ap1thVdef.Value

It would be better if instead of storing the Range object to just store the value. Also, since it appears from your example that the at least some of the cells are contiguous, you can assign the Value of a multi-cell range to an array and get a 2-dim array with all of the values.

如果不存储Range对象而只存储值,那会更好。此外,由于您的示例中显示至少一些单元格是连续的,因此您可以将多单元格范围的值分配给数组,并获得包含所有值的2-dim数组。

Assume you have 16 values you need to use from a spreadsheet. You don't need to populate those values every time it runs, but you do need to change them on occasion. Set up an IntitializeVars procedure that will populate two 8 row arrays based on the ranges you provide to the procedure.

假设您需要在电子表格中使用16个值。每次运行时都不需要填充这些值,但有时需要更改它们。设置IntitializeVars过程,该过程将根据您为过程提供的范围填充两个8行数组。

Also, include an optional Boolean argument to force the arrays to repopulate even if they already have something in them.

此外,包含一个可选的布尔参数,以强制数组重新填充,即使它们已经包含了某些内容。

Public vaAp As Variant
Public vaApDef As Variant

Public Sub InitializeVars(rAp As Range, rApDef As Range, Optional ByVal Force As Boolean = False)

    If Not IsArray(vaAp) Or Force Then
        'This creates a two dimensional array
        vaAp = rAp.Value
    End If

    If Not IsArray(vaApDef) Or Force Then
        'This creates a two dimensional array
        vaApDef = rApDef.Value
    End If

    'Now you have two, 2-dim arrays that are full of the values
    'from the ranges you passed in

End Sub

Sub test()

    'Put your values into the public arrays
    InitializeVars Sheet1.Range("B32").Resize(8, 1), Sheet1.Range("C32").Resize(8, 1)

    'Use the values from the arrays in your business logic
    Debug.Print 100 * vaAp(2, 1)
    Debug.Print 3 + vaApDef(6, 1)

    'Now the something changed and I need to fix the arrays
    'to point to new locations. I set the last argument to TRUE
    'so the arrays repopulate even if there's already something
    'in them
    InitializeVars Sheet1.Range("D32").Resize(8, 1), Sheet1.Range("E32").Resize(8, 1), True

    'use the new arrays
    Debug.Print 100 * vaAp(2, 1)
    Debug.Print 3 + vaApDef(6, 1)

End Sub

#1