遍历数组并将它们放入一个数组中-解网

问：

我正在尝试创建一个函数，该函数返回一个由未指定数量的元素组成的数组。元素的数量取决于通过我的函数传递的元素的数量。

Public Function dekonstruer_update_statement(oppdateringsfelter As Variant, oppdateringsverdier As Variant, oppdateringsteller As Long, oppdateringstabell As String, oppdateringskriteriefelter As Variant, oppdateringskriterier As Variant, oppdateringskriterieteller As Long) As Variant

函数中的所有“变体”都应该是 x 元素的数组，如下所示

(Array(value1, value2, value3...etc.))

我想遍历“数组”并将它们放入一个数组中。

Dim dekonstruerArray() As Variant
Dim i, j, k, l, m As Long

i = 0

For j = 0 To oppdateringsteller - 1
    dekonstruerArray(i) = oppdateringsfelter(j)
    i = i + 1
Next j

For k = 0 To oppdateringsteller - 1
    dekonstruerArray(i) = oppdateringsfelter(k)
    i = i + 1
Next k

dekonstruerArray(i) = oppdateringstabell
i = i + 1

For l = 0 To oppdateringskriterieteller - 1
    dekonstruerArray(i) = oppdateringskriteriefelter(l)
    i = i + 1
Next l

For m = 0 To oppdateringskriterieteller - 1
    dekonstruerArray(i) = oppdateringskriterier(m)
    i = i + 1
Next m

dekonstruer_update_statement = dekonstruerArray

我明白了

下标超出范围（错误 9）

因为我无法访问数组中的（i）位置。

我试图将变体分配给 where 是一个变量，通过计算传递给函数的“数组”中的所有值来计算。这个数字将在我的整个计划中有所不同。dekonstruerArray()dekonstruerArray(0 to x)x

我明白了

编译错误：需要常量表达式

我可以去一个集合，但我想了解数组是如何工作的。

阵列 VBA MS-ACCESS-2016

FUNCTION Concat(X AS VARIANT, Y AS VARIANT, Z AS VARIANT) AS VARIANT
    CONST K = 2
    ' K= number of arrays-1
    DIM A AS VARIANT
    REDIM A(UBOUND(X) + UBOUND(Y) + UBOUND(Z)+ K)

    DIM i,j AS INTEGER

    j = 0
    FOR i= 0 TO UBOUND(X)
        A(j)= X(i)
        j= j + 1
    NEXT

    FOR i= 0 TO UBOUND(Y)
        A(j)= Y(i)
        j= j + 1
    NEXT

    FOR I = 0 TO UBOUND(Z)
        A(j)= Z(i)
        j= j + 1
    NEXT

    Concat= A
END FUNCTION

1赞 freeflow 11/9/2023 #2

有许多关于在 VBA 中使用数组的提示。

如果你要遍历整个数组，那么使用，因为这允许你在不知道数组的开始和结束索引是什么的情况下使用数组。For myIndex = Lbound(myArray) to Ubound(myArray)
如果您的数组来自 Excel 范围，那么它将始终是二维数组（即使您只想要一行或一列）。
尝试将数组作为单个实体使用，而不是逐项使用。

代码的一大问题是需要创建一个正确大小的数组，这就是为什么其他注释将您引导到 redim 的原因。但是，redim 可能是一项成本高昂的操作，因此如果可以的话，最好编写可以避免 ReDim 的代码。

下面的代码显示了如何使用 Scripting.Dictionary 来实现您的目标。两个版本。一种是代码的“直接”翻译。第二个版本展示了如何以更灵活的方式编写，因为不再需要预先指定要合并的数组。

Option Explicit

Public Function dekonstruer_update_statement _
( _
    oppdateringsfelter As Variant, _
    oppdateringsverdier As Variant, _
    oppdateringsteller As Long, _
    oppdateringstabell As String, _
    oppdateringskriteriefelter As Variant, _
    oppdateringskriterier As Variant, _
    oppdateringskriterieteller As Long _
) As Variant

    Dim myD As Scripting.Dictionary
    Set myD = New Scripting.Dictionary
    
    Dim myItem As Variant
    With myD
        ' using only those parameters declared as Variant
        ' Using the .count method of the scripting.dictionary to provide the needed Key
        For Each myItem In oppdateringsfelter
            myD.Add .Count, myItem
        Next
        
        For Each myItem In oppdateringsverdier
            myD.Add .Count, myItem
        Next
           
        For Each myItem In oppdateringskriteriefelter
            .Add .Count, myItem
        Next
    
        For Each myItem In oppdateringskriterier
            .Add .Count, myItem
        Next
    End With
    
    ' Return the combined arrays by using the .Items method of  the scripting.dictionary
    dekonstruer_update_statement = myD.Items
    
End Function

' Which of course would be more flexible when written as

'in your code
dekonstruer_update_statement oppdateringsfelter, oppdateringsverdier, oppdateringskriteriefelter, oppdateringskriterier

Public Function dekonstruer_update_statement(ParamArray ipParamArray() As Variant) As Variant

    Dim myD As Scripting.Dictionary
    Set myD = New Scripting.Dictionary
    
    Dim myArray As Variant
    For Each myArray In ipParamArray
        If Not VBA.IsArray(myArray) Then
            Err.Raise 17 ' expand this line to the error you wish to return
            Exit Function
        End If
        ConsolidateArray myArray
    Next
    
    dekonstruer_update_statement = myD.Items
        
End Function

Public Sub ConsolidateArray(ByRef iopD As Scripting.Dictionary, ByRef ipArray As Variant)
    
    Dim myItem As Variant
    For Each myItem In ipArray
        iopD.Add iopD.Count, myItem
    Next
    
End Sub

只是为了好玩，如果我使用我自己的 VBA 结构和函数库，上述内容将在一行 VBA 代码中实现如下。

Set dekonstruer_update_statement = SeqA.Deb _
    .AddRange(oppdateringsfelter) _
    .AddRange(oppdateringsverdier) _
    .AddRange(oppdateringskriteriefelter) _
    .AddRange(oppdateringskriterier)

上一个：RowSourceType 用户定义函数 - “code” 参数常量

下一个：我的子表单有两个问题

遍历数组并将它们放入一个数组中

Iterate through arrays and put them into one array

评论