时间戳

首页 > 解决方案 > 如何将所有值从 Vector 传递给 Sub？

arrays - 如何将所有值从 Vector 传递给 Sub？

问题描述

我在 B2:D4 范围内的 3x3 矩阵中有整数值。

我做了一个嵌套循环检查每个单元格，直到找到一个空单元格（每行读取的停止条件）。

我可以从每个单元格中读取值并存储在向量的位置，但是当我检查向量中包含的值时，只显示向量最后一个位置的值。

我需要将所有值传递给一个可以处理这些小向量的子程序。

Option Explicit
Option Base 1

Sub Main()

    Dim vet(1 to 3) As Variant
    Dim lin As Long
    Dim col As Long
    Dim i As Long

    Sheets("Sheet1").Select
    lin = 2
    col = 2
    i = 1

    Cells(lin, col).Activate

    Do Until Cells(lin, 1) = ""
        col = 2
        Do Until Cells(lin, col) = ""
            Cells(lin, col).Select
            vet(i) = Cells(lin, col).Value
            col = col + 1
        Loop
        ' At this point, when checking the vector, only contains the last value
        Call showVet(vet())
        lin = lin + 1
    Loop
End Sub

Sub showVet(ByRef v() As Variant)
    Dim i As Long
    For i = 1 To 3
        Debug.Print (v(i))
    Next i
    ' And at this point, only the last value that was passed ...
End Sub

标签： arraysexcelvbavector

解决方案

看这个：

Option Base 1

Sub Main()

Dim vet(1 To 3) As Variant
Dim lin As Long
Dim col As Long
Dim i As Long

With Sheets("Sheet1")
    lin = 2

    Do Until Cells(lin, 1) = ""
        i = 1
        col = 2
        Do Until Cells(lin, col) = ""
            .Cells(lin, col).Select
            vet(i) = Cells(lin, col).Value
            i = i + 1
            col = col + 1
        Loop

        Call showVet(vet())
        lin = lin + 1
    Loop
End With

End Sub

Sub showVet(ByRef v() As Variant)
Dim i As Long
    For i = 1 To 3
        Debug.Print (v(i))
    Next i
End Sub

推荐阅读