时间戳

首页 > 解决方案 > VBA 宏函数超出范围

excel - VBA 宏函数超出范围

问题描述

Function getNames()
    Dim  wbThis As Workbook,  arr() As Variant, i As Long
    Set wbThis = ThisWorkbook
    For i = 1 To 50
        arr(i) = wbThis.Sheets("Sheet1").Cells(6, i).Value
    Next i

    For i = 1 To 50
        MsgBox "employee names are***** ." + arr(i)
    Next i
End Function

我在 Sheet1 中有我的 VBA 宏代码。在 sheet1 中，我有一个单击按钮。单击按钮时，我调用了 getNames 函数。在 sheet1 我有所有行和列值。但是当我运行它时显示

下标超出范围/运行时错误“9”

我尝试了很多。任何人对此有任何想法。

标签： excelvba

解决方案

您需要为变量数组分配一些大小。

Function getNames()
    Dim wbThis As Workbook, i As Long

    Set wbThis = ThisWorkbook

    redim arr(1 to 50) As Variant

    For i = lbound(arr) To ubound(arr)
        arr(i) = wbThis.Sheets("Sheet1").Cells(6, i).Value
    Next i

    For i = lbound(arr) To ubound(arr)
        MsgBox "employee names are***** ." & arr(i)
    Next i
End Function

ReDim可以不用Dim先用。LBoundtoUBound是一种更通用的方法，可以通过静态数组从下边界循环到上边界。& 号&是字符串连接的首选运算符。

函数旨在返回一个值。sub作为一个程序，这可能会更好。

您可以通过从 sub 调用函数来“播种”数组。

sub main()

    dim i as long, nms as variant

    nms = getNames()

    For i = lbound(nms) To ubound(nms)
        MsgBox "employee names are***** ." & nms(i)
    Next i

end sub

Function getNames()
    Dim  wbThis As Workbook,  i As Long

    Set wbThis = ThisWorkbook

    redim arr(1 to 50) As Variant

    For i = lbound(arr) To ubound(arr)
        arr(i) = wbThis.Sheets("Sheet1").Cells(6, i).Value
    Next i

    getnames = arr

End Function

推荐阅读