VBA标错误 [英] vba subscript error

问题描述

更新：我一直在阅读一些网站和论坛上关于通过潜艇和功能之间的阵列。但它让我想起了我的变量声明是否是问题？目前我所有的阵列（Results1,2,3，FinalResults，X安培; Y）的无不被声明为变体。而且我认为功能之间传递数组时，这可能会引起问题。任何人都知道，如果这个问题会涉及到我的code？此外，只是为了澄清我想在Results1,2,3的值传递到函数。

我不断收到下标越界当我尝试运行VBA下面的函数。 X和Y两个都是我试图数据合并到一个新的数组的一维数组。当我尝试指定阵列X的上限和下限出现的错误

 功能lnArray（X（）作为变型，Y（）为Variant）为Variant
昏暗C1的长
昏暗XCOUNT只要
昏暗T作为长
昏暗的FinalResults（）为Variant计数器1 = 0
    对于XCOUNT = LBOUND（X）为UBound函数（X）
        在错误恢复下一页
        T = Application.Match（Ⅹ（XCOUNT，1）中，Y，0）
        如果Err.Number的= 0则
            如果（叔大于0）然后
                计数器1 =计数器1 + 1
                使用ReDim preserve FinalResults（计数器1）
                FinalResults（计数器1）= X（XCOUNT，1）
            万一
        万一
        对错误转到0
    接下来XCOUNTlnArray = FinalResults
结束功能
 

更新 - 这是当前code我现在，我已经做了一些修改。即确保数组是通过引用传递给函数，并改变了一切为一维数组。但同样的问题仍然存在。我检查了，我的结果：1（）和结果2（）数组都存储值，但它没有被传递给我的UDF X（）和Y（）变量。我包括在我的子code传递函数的部分，请看一看。

 子搜索（）
昏暗结果：1（）作为变，结果2（）作为变，FinalResults（）为Variant        FinalResults = lnArray（结果1，结果2）
结束小组功能lnArray（为ByRef X（）为Variant，为ByRef Y（）为Variant）为Variant
昏暗C1的长
昏暗XCOUNT只要
昏暗T作为长
昏暗的FinalResults（）为Variant计数器1 = 0
    对于XCOUNT = LBOUND（X）为UBound函数（X）
        在错误恢复下一页
        T = 0
        T = Application.Match（X（XCOUNT），Y，0）
        如果Err.Number的= 0则
            如果（叔大于0）然后
                计数器1 =计数器1 + 1
                使用ReDim preserve FinalResults（计数器1）
                FinalResults（计数器1）= X（XCOUNT）
            万一
        万一
        对错误转到0
    接下来XCOUNTlnArray = FinalResults
结束功能
 

编辑 - 下面是我如何填充数据，我的结果：1（）和结果2（）阵列。请让我知道，如果需要更多的信息。

 子搜索（）昏暗的TextBox1中只要
昏暗TextBox3只要
昏暗结果：1（）为Variant
昏暗结果2（）为Variant
昏暗FindRange1由于范围
昏暗FIND1由于范围
昏暗FindRange2由于范围
昏暗Find2由于范围
昏暗的i1只要
昏暗I2只要TextBox1的= ILsea​​rch.TextBox1.Value
TextBox3 = ILsea​​rch.TextBox3.Value 设置FindRange1 =工作表（属性）的范围。（P7：P1000）
            如果ILsea​​rch.P1B1.Value = TRUE然后
                对于每个FIND1在FindRange1
                    如果（Find1.Value＆下; TextBox1中）和（Find1.Value大于0）然后
                        I1 = I1 + 1
                        使用ReDim preserve结果：1（I1）
                        结果：1（I1）= Find1.Address
                    万一
                接下来FIND1
            万一 设置FindRange2 =工作表（属性）的范围。（P7：P1000）
            如果ILsea​​rch.P2B1.Value = TRUE然后
                对于每个Find2在FindRange2
                    如果（Find2.Value＆下; TextBox3）和（Find2.Value大于0）然后
                        I2 = I2 + 1
                        使用ReDim preserve结果2（I2）
                        结果2（I2）= Find2.Address
                    万一
                接下来Find2
            万一
结束小组
 

EDIT2 - 这是目前如何我选择在我的成果，巩固和显示器的阵列。我有3个搜索变量（Re​​sults1,2和3），并且如果仅1被选择，显示它是容易的。但是这取决于变量选择我还需要巩固阵列（1 + 2,1 + 3,2 + 3，或所有3阵列）。我知道这可能是如何混乱它，效率低下，但我不能想出更好的办法。

 对于一个单一的属性选择
昏暗p1results由于范围
昏暗shProperties作为工作表
昏暗shSearchResult作为工作表设置shProperties = ActiveWorkbook.Worksheets（属性）
设置shSearchResult = ActiveWorkbook.Worksheets（信息搜索结果）如果（ILsea​​rch.ComboBox1.Enabled =真）（ILsea​​rch.ComboBox2.Enabled = FALSE），并且（ILsea​​rch.ComboBox3.Enabled = FALSE）然后
   在错误恢复下一页
   对于I1 = LBOUND（结果：1）为UBound函数（结果：1）
        设置NextRow = shSearchResult.Cells（shSearchResult.Rows.Count，4）.END（xlUp）.Offset（1，-3）
        shProperties.Range（结果：1（I1））。EntireRow.Copy NextRow
    接下来I1
万一重复同样的如果结果2和结果3 /然后code昏暗的FinalResults（）为Variant
昏暗FinCount作为整数
昏暗C1的长
昏暗T作为长如果（ILsea​​rch.ComboBox1.Enabled =真）（ILsea​​rch.ComboBox2.Enabled =真）（ILsea​​rch.ComboBox2.Enabled = FALSE）然后
    如果IsArrayAllocated（结果：1）= TRUE且IsArrayAllocated（结果2）= true，那么
    其他
         Debug.Print空阵
    万一    FinalResults = lnArray（结果1，结果2）
        在错误恢复下一页
        对于FinCount = LBOUND（FinalResults）为UBound函数（FinalResults）
            设置NextRow = shSearchResult.Cells（shSearchResult.Rows.Count，4）.END（xlUp）.Offset（1，-3）
            shProperties.Range（结果3（I3））。EntireRow.Copy NextRow
        接下来FinCount
万一
重复相同的if / then为（1 + 3）安排和（2 + 3）安排昏暗的intResults（）为Variant如果（ILsea​​rch.ComboBox1.Enabled =真）（ILsea​​rch.ComboBox2.Enabled =真）（ILsea​​rch.ComboBox2.Enabled = TRUE）然后
intResults = lnArray（结果1，结果2）
FinalResults = lnArray（intResults，结果3）
    在错误恢复下一页
    对于FinCount = LBOUND（FinalResults）为UBound函数（FinalResults）
        设置NextRow = shSearchResult.Cells（shSearchResult.Rows.Count，4）.END（xlUp）.Offset（1，-3）
        shProperties.Range（结果3（I3））。EntireRow.Copy NextRow
    接下来FinCount
万一
 

解决方案

有在你的code混合消息：

您的状态，您的code 有关XCOUNT = LBOUND（X）的线要UBound函数（X）预计1维数组

但是， Application.Match（X（XCOUNT，1），Y，0）指两个或多个维度（即 1 位）。这是由错误，这将被返回，如果X是实际上二维

的支持

当code运行和错误，检查中X监视窗口，以确定其真实形态

修改
看到Phydaux的评论 - LBOUND（X）默认为维A多维数组的1。

EDIT2

两个潜在的问题：

如果不是 P1B1 或 P2B1 = FALSE ，或者没有匹配在，那么结果1 或结果2 分别从未尺寸数据中发现。调用 LBOUND 或 UBound函数在undimensioned阵列将导致错误

不管你信不信，称X（XCOUNT，1）一维数组的错误。但是，由于上的错误继续下一步是积极的，不报告的错误。

所以，你需要：

• 处理情况，X或Y不是尺寸的情况下




• 删除该 1 从 X（XCOUNT，1）

我建议你看一下芯片皮尔逊的优秀的网站数组处理code

UPDATE: I have been reading some websites and forums about passing arrays between subs and functions. But it got me thinking about whether my variable declarations were the problem? Currently all of my arrays (Results1,2,3,FinalResults, X & Y) are all being declared as variant. And I think this may cause a problem when passing arrays between functions. Anyone know if this problem will pertain to my code? ALso, just to clarify I want the values in Results1,2,3 to pass into the function.

I keep getting 'subscript out of range' when I try to run the following function in VBA. Both X and Y are 1-dimensional arrays that I am trying to merge data into a new array. The error occurs when I try to specify the lower and upper bounds for array X.

Function lnArray(X() As Variant, Y() As Variant) As Variant
Dim counter1 As Long
Dim xcount As Long
Dim t As Long
Dim FinalResults() As Variant

counter1 = 0
    For xcount = LBound(X) To UBound(X)
        On Error Resume Next
        t = Application.Match(X(xcount, 1), Y, 0)
        If Err.Number = 0 Then
            If (t > 0) Then
                counter1 = counter1 + 1
                ReDim Preserve FinalResults(counter1)
                FinalResults(counter1) = X(xcount, 1)
            End If
        End If
        On Error GoTo 0
    Next xcount

lnArray = FinalResults
End Function

Update - This is the current code I have right now, I've made a few corrections. Namely making sure the arrays are passed to the function by reference and changed everything into a 1-dimensional array. However the same problem still persists. I've checked and my Results1() and Results2() array both store values but it is not being passed to my UDF X() and Y() variable. I included the portion of the code in my sub that passes the function, please take a look.

Sub search()
Dim Results1() As Variant, Results2() As Variant, FinalResults() As Variant

        FinalResults = lnArray(Results1, Results2)
End Sub

Function lnArray(ByRef X() As Variant, ByRef Y() As Variant) As Variant
Dim counter1 As Long
Dim xcount As Long
Dim t As Long
Dim FinalResults() As Variant

counter1 = 0
    For xcount = LBound(X) To UBound(X)
        On Error Resume Next
        t = 0
        t = Application.Match(X(xcount), Y, 0)
        If Err.Number = 0 Then
            If (t > 0) Then
                counter1 = counter1 + 1
                ReDim Preserve FinalResults(counter1)
                FinalResults(counter1) = X(xcount)
            End If
        End If
        On Error GoTo 0
    Next xcount

lnArray = FinalResults
End Function

Edit - The following is how I populate data for my Results1() and Results2() array. Please let me know if more information is required.

Sub Search()

Dim TextBox1 As Long
Dim TextBox3 As Long
Dim Results1() As Variant
Dim Results2() As Variant
Dim FindRange1 As Range
Dim Find1 As Range
Dim FindRange2 As Range
Dim Find2 As Range
Dim i1 As Long
Dim i2 As Long

TextBox1 = ILsearch.TextBox1.Value
TextBox3 = ILsearch.TextBox3.Value

 Set FindRange1 = Worksheets("Properties").Range("P7:P1000")
            If ILsearch.P1B1.Value = True Then
                For Each Find1 In FindRange1
                    If (Find1.Value < TextBox1) And (Find1.Value > 0) Then
                        i1 = i1 + 1
                        ReDim Preserve Results1(i1)
                        Results1(i1) = Find1.Address
                    End If
                Next Find1
            End If

 Set FindRange2 = Worksheets("Properties").Range("P7:P1000")
            If ILsearch.P2B1.Value = True Then
                For Each Find2 In FindRange2
                    If (Find2.Value < TextBox3) And (Find2.Value > 0) Then
                        i2 = i2 + 1
                        ReDim Preserve Results2(i2)
                        Results2(i2) = Find2.Address
                    End If
                Next Find2
            End If
End Sub

Edit2 - This is currently how I'm choosing which arrays to consolidate and display in my results. I have 3 search variables (Results1,2 and 3) and if only 1 is selected, displaying it is easy. However depending on which variables are selected I also need to consolidate the arrays (1+2,1+3,2+3, or all 3 arrays) . I realize how cluttered it is and inefficient it probably is but I could not come up with a better way.

'For a single property selection
Dim p1results As Range
Dim shProperties As Worksheet
Dim shSearchResult As Worksheet

Set shProperties = ActiveWorkbook.Worksheets("properties")
Set shSearchResult = ActiveWorkbook.Worksheets("searchresult")

If (ILsearch.ComboBox1.Enabled = True) And (ILsearch.ComboBox2.Enabled = False) And (ILsearch.ComboBox3.Enabled = False) Then
   On Error Resume Next
   For i1 = LBound(Results1) To UBound(Results1)
        Set NextRow = shSearchResult.Cells(shSearchResult.Rows.Count, 4).End(xlUp).Offset(1, -3)
        shProperties.Range(Results1(i1)).EntireRow.Copy NextRow
    Next i1
End If

'repeat same if/then code for Results2 and Results3

Dim FinalResults() As Variant
Dim FinCount As Integer
Dim Counter1 As Long
Dim t As Long

If (ILsearch.ComboBox1.Enabled = True) And (ILsearch.ComboBox2.Enabled = True) And (ILsearch.ComboBox2.Enabled = False) Then
    If IsArrayAllocated(Results1) = True And IsArrayAllocated(Results2) = True Then
    Else
         Debug.Print "Empty Array"
    End If

    FinalResults = lnArray(Results1, Results2)
        On Error Resume Next
        For FinCount = LBound(FinalResults) To UBound(FinalResults)
            Set NextRow = shSearchResult.Cells(shSearchResult.Rows.Count, 4).End(xlUp).Offset(1, -3)
            shProperties.Range(Results3(i3)).EntireRow.Copy NextRow
        Next FinCount
End If
'repeat same if/then for (1+3) arrangement and (2+3)arrangement

Dim intResults() As Variant

If (ILsearch.ComboBox1.Enabled = True) And (ILsearch.ComboBox2.Enabled = True) And (ILsearch.ComboBox2.Enabled = True) Then
intResults = lnArray(Results1, Results2)
FinalResults = lnArray(intResults, Results3)
    On Error Resume Next
    For FinCount = LBound(FinalResults) To UBound(FinalResults)
        Set NextRow = shSearchResult.Cells(shSearchResult.Rows.Count, 4).End(xlUp).Offset(1, -3)
        shProperties.Range(Results3(i3)).EntireRow.Copy NextRow
    Next FinCount
End If

解决方案

There is a mixed message in your code:

You state, and your line of code For xcount = LBound(X) To UBound(X) expects 1 dimensional arrays

But, Application.Match(X(xcount, 1), Y, 0) implies two or more dimensions (the , 1 bit). This is supported by the error, which will be returned if X is in fact two dimensional.

When the code runs and errors, examine X in the watch window to determine its true form

EDIT see Phydaux's comment - LBound(X) defaults to dimension 1 of a multi-dimensional array.

EDIT2

Two potential issues:

If either P1B1 or P2B1 = FALSE, or no matches are found in the data, then Results1 or Results2 respectively are never dimensioned. Calling LBound or UBound on a undimensioned array will cause the error

Believe it or not, calling X(xcount, 1) on a one dimensional array errors. But since On Error Resume Next is active, the error is not reported.

So, you need to:

• Handle the case where X or Y are not dimensioned

• Drop the ,1 from X(xcount, 1)

I suggest you look at Chip Pearson's excellent site for array handling code

这篇关于VBA标错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！