| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bound Test in Variant Array
Hi -
I don't understand why in the vACNay_Init sub, when I do NOT initialize the vACNay array via the ReDim in the calling macro, the Lbound and Ubound test 'fails' and the lines below the bound tests execute. When I step thru the code, I see the <subscript out of range msg when I 'cursor' the xbound phrases. When I execute the ReDim in sub test, called macro works as anticipated. Thanks. Neal Z. sub test() dim vACNay() as variant, Col as integer, AyRow as integer dim Status as string 'Note spaxxxcol series of vars are public constants as 'is gPaQty. 'ReDim vACNay(1, spacolqty) AyRow = 1 Call vACNay_Init(AyRow, 6, vACNay, Status) 'test show shows nothing when above ReDim is commented out, 'even tho' called lines seem to execute. For Col = 1 To spacolqty Debug.Print Col & ") -" & vACNay(AyRow, Col) & "-" Next Col End Sub Sub vACNay_Init(ByVal AyRow As Integer, ByVal PaNum As Integer, _ vACNay() As Variant, Status As String) Dim AyCol As Integer Status = "" If PaNum < 1 Or PaNum gPaQty Then Status = "PaNum = " & PaNum & " is invalid." Exit Sub End If On Error Resume Next If LBound(vACNay, 1) <= AyRow And AyRow <= UBound(vACNay, 1) _ And LBound(vACNay, 1) 0 Then vACNay(AyRow, SPaPaNumCol) = PaNum vACNay(AyRow, SPaPaAbbrCol) = PaAbr3_vPaNum(PaNum) 'Abr3 function vACNay(AyRow, SPaDlvCdCol) = "" 'DlvCd vACNay(AyRow, SPaACNCol) = "No Acct" 'ACN For AyCol = SPaSubscrCol To SPaDrawCol vACNay(AyRow, AyCol) = 0 Next AyCol For AyCol = SPaDrawCol + 1 To SPaColQty vACNay(AyRow, AyCol) = "" Next AyCol Else Err = 0 Status = "AyRow " & AyRow & " does not exist." End If End Sub -- Neal Z |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Bound Test in Variant Array
The cause is that an IF statement that evaluates to an error will execute
the THEN block. This can be illustrated quite easily with the following code: On Error Resume Next If (1 / 0) = 0 Then Debug.Print "IN THEN" Else Debug.Print "IN ELSE" End If The THEN statement is executed. In my standard library of array functions, I use Public Function IsArrayAllocated(Arr As Variant) As Boolean On Error Resume Next IsArrayAllocated = Not IsError(LBound(Arr)) End Function to determine whether an array has been allocated (statically with Dim or dynamically with ReDim). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Neal Zimm" wrote in message ... Hi - I don't understand why in the vACNay_Init sub, when I do NOT initialize the vACNay array via the ReDim in the calling macro, the Lbound and Ubound test 'fails' and the lines below the bound tests execute. When I step thru the code, I see the <subscript out of range msg when I 'cursor' the xbound phrases. When I execute the ReDim in sub test, called macro works as anticipated. Thanks. Neal Z. sub test() dim vACNay() as variant, Col as integer, AyRow as integer dim Status as string 'Note spaxxxcol series of vars are public constants as 'is gPaQty. 'ReDim vACNay(1, spacolqty) AyRow = 1 Call vACNay_Init(AyRow, 6, vACNay, Status) 'test show shows nothing when above ReDim is commented out, 'even tho' called lines seem to execute. For Col = 1 To spacolqty Debug.Print Col & ") -" & vACNay(AyRow, Col) & "-" Next Col End Sub Sub vACNay_Init(ByVal AyRow As Integer, ByVal PaNum As Integer, _ vACNay() As Variant, Status As String) Dim AyCol As Integer Status = "" If PaNum < 1 Or PaNum gPaQty Then Status = "PaNum = " & PaNum & " is invalid." Exit Sub End If On Error Resume Next If LBound(vACNay, 1) <= AyRow And AyRow <= UBound(vACNay, 1) _ And LBound(vACNay, 1) 0 Then vACNay(AyRow, SPaPaNumCol) = PaNum vACNay(AyRow, SPaPaAbbrCol) = PaAbr3_vPaNum(PaNum) 'Abr3 function vACNay(AyRow, SPaDlvCdCol) = "" 'DlvCd vACNay(AyRow, SPaACNCol) = "No Acct" 'ACN For AyCol = SPaSubscrCol To SPaDrawCol vACNay(AyRow, AyCol) = 0 Next AyCol For AyCol = SPaDrawCol + 1 To SPaColQty vACNay(AyRow, AyCol) = "" Next AyCol Else Err = 0 Status = "AyRow " & AyRow & " does not exist." End If End Sub -- Neal Z |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Bound Test in Variant Array
I wrote the function from memory. I looked it up in my library and the
actual code is Public Function IsArrayAllocated(Arr As Variant) As Boolean On Error Resume Next IsArrayAllocated = (Not IsError(LBound(Arr))) And IsArray(Arr) End Function -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Chip Pearson" wrote in message ... The cause is that an IF statement that evaluates to an error will execute the THEN block. This can be illustrated quite easily with the following code: On Error Resume Next If (1 / 0) = 0 Then Debug.Print "IN THEN" Else Debug.Print "IN ELSE" End If The THEN statement is executed. In my standard library of array functions, I use Public Function IsArrayAllocated(Arr As Variant) As Boolean On Error Resume Next IsArrayAllocated = Not IsError(LBound(Arr)) End Function to determine whether an array has been allocated (statically with Dim or dynamically with ReDim). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Neal Zimm" wrote in message ... Hi - I don't understand why in the vACNay_Init sub, when I do NOT initialize the vACNay array via the ReDim in the calling macro, the Lbound and Ubound test 'fails' and the lines below the bound tests execute. When I step thru the code, I see the <subscript out of range msg when I 'cursor' the xbound phrases. When I execute the ReDim in sub test, called macro works as anticipated. Thanks. Neal Z. sub test() dim vACNay() as variant, Col as integer, AyRow as integer dim Status as string 'Note spaxxxcol series of vars are public constants as 'is gPaQty. 'ReDim vACNay(1, spacolqty) AyRow = 1 Call vACNay_Init(AyRow, 6, vACNay, Status) 'test show shows nothing when above ReDim is commented out, 'even tho' called lines seem to execute. For Col = 1 To spacolqty Debug.Print Col & ") -" & vACNay(AyRow, Col) & "-" Next Col End Sub Sub vACNay_Init(ByVal AyRow As Integer, ByVal PaNum As Integer, _ vACNay() As Variant, Status As String) Dim AyCol As Integer Status = "" If PaNum < 1 Or PaNum gPaQty Then Status = "PaNum = " & PaNum & " is invalid." Exit Sub End If On Error Resume Next If LBound(vACNay, 1) <= AyRow And AyRow <= UBound(vACNay, 1) _ And LBound(vACNay, 1) 0 Then vACNay(AyRow, SPaPaNumCol) = PaNum vACNay(AyRow, SPaPaAbbrCol) = PaAbr3_vPaNum(PaNum) 'Abr3 function vACNay(AyRow, SPaDlvCdCol) = "" 'DlvCd vACNay(AyRow, SPaACNCol) = "No Acct" 'ACN For AyCol = SPaSubscrCol To SPaDrawCol vACNay(AyRow, AyCol) = 0 Next AyCol For AyCol = SPaDrawCol + 1 To SPaColQty vACNay(AyRow, AyCol) = "" Next AyCol Else Err = 0 Status = "AyRow " & AyRow & " does not exist." End If End Sub -- Neal Z |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Bound Test in Variant Array
Thanks again, I'll remember to use it this time.
-- Neal Z "Chip Pearson" wrote: I wrote the function from memory. I looked it up in my library and the actual code is Public Function IsArrayAllocated(Arr As Variant) As Boolean On Error Resume Next IsArrayAllocated = (Not IsError(LBound(Arr))) And IsArray(Arr) End Function -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Chip Pearson" wrote in message ... The cause is that an IF statement that evaluates to an error will execute the THEN block. This can be illustrated quite easily with the following code: On Error Resume Next If (1 / 0) = 0 Then Debug.Print "IN THEN" Else Debug.Print "IN ELSE" End If The THEN statement is executed. In my standard library of array functions, I use Public Function IsArrayAllocated(Arr As Variant) As Boolean On Error Resume Next IsArrayAllocated = Not IsError(LBound(Arr)) End Function to determine whether an array has been allocated (statically with Dim or dynamically with ReDim). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Neal Zimm" wrote in message ... Hi - I don't understand why in the vACNay_Init sub, when I do NOT initialize the vACNay array via the ReDim in the calling macro, the Lbound and Ubound test 'fails' and the lines below the bound tests execute. When I step thru the code, I see the <subscript out of range msg when I 'cursor' the xbound phrases. When I execute the ReDim in sub test, called macro works as anticipated. Thanks. Neal Z. sub test() dim vACNay() as variant, Col as integer, AyRow as integer dim Status as string 'Note spaxxxcol series of vars are public constants as 'is gPaQty. 'ReDim vACNay(1, spacolqty) AyRow = 1 Call vACNay_Init(AyRow, 6, vACNay, Status) 'test show shows nothing when above ReDim is commented out, 'even tho' called lines seem to execute. For Col = 1 To spacolqty Debug.Print Col & ") -" & vACNay(AyRow, Col) & "-" Next Col End Sub Sub vACNay_Init(ByVal AyRow As Integer, ByVal PaNum As Integer, _ vACNay() As Variant, Status As String) Dim AyCol As Integer Status = "" If PaNum < 1 Or PaNum gPaQty Then Status = "PaNum = " & PaNum & " is invalid." Exit Sub End If On Error Resume Next If LBound(vACNay, 1) <= AyRow And AyRow <= UBound(vACNay, 1) _ And LBound(vACNay, 1) 0 Then vACNay(AyRow, SPaPaNumCol) = PaNum vACNay(AyRow, SPaPaAbbrCol) = PaAbr3_vPaNum(PaNum) 'Abr3 function vACNay(AyRow, SPaDlvCdCol) = "" 'DlvCd vACNay(AyRow, SPaACNCol) = "No Acct" 'ACN For AyCol = SPaSubscrCol To SPaDrawCol vACNay(AyRow, AyCol) = 0 Next AyCol For AyCol = SPaDrawCol + 1 To SPaColQty vACNay(AyRow, AyCol) = "" Next AyCol Else Err = 0 Status = "AyRow " & AyRow & " does not exist." End If End Sub -- Neal Z |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Bound Test in Variant Array
Chip -
Sigh, I got confused, I thought the else branch would exec after the on error .... stmt. You wouldn't remember, but you answered a prior post of mine not allocated arrays, and I have the code from your website. Stupid me didn't use it here. Thanks again. -- Neal Z "Chip Pearson" wrote: The cause is that an IF statement that evaluates to an error will execute the THEN block. This can be illustrated quite easily with the following code: On Error Resume Next If (1 / 0) = 0 Then Debug.Print "IN THEN" Else Debug.Print "IN ELSE" End If The THEN statement is executed. In my standard library of array functions, I use Public Function IsArrayAllocated(Arr As Variant) As Boolean On Error Resume Next IsArrayAllocated = Not IsError(LBound(Arr)) End Function to determine whether an array has been allocated (statically with Dim or dynamically with ReDim). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Neal Zimm" wrote in message ... Hi - I don't understand why in the vACNay_Init sub, when I do NOT initialize the vACNay array via the ReDim in the calling macro, the Lbound and Ubound test 'fails' and the lines below the bound tests execute. When I step thru the code, I see the <subscript out of range msg when I 'cursor' the xbound phrases. When I execute the ReDim in sub test, called macro works as anticipated. Thanks. Neal Z. sub test() dim vACNay() as variant, Col as integer, AyRow as integer dim Status as string 'Note spaxxxcol series of vars are public constants as 'is gPaQty. 'ReDim vACNay(1, spacolqty) AyRow = 1 Call vACNay_Init(AyRow, 6, vACNay, Status) 'test show shows nothing when above ReDim is commented out, 'even tho' called lines seem to execute. For Col = 1 To spacolqty Debug.Print Col & ") -" & vACNay(AyRow, Col) & "-" Next Col End Sub Sub vACNay_Init(ByVal AyRow As Integer, ByVal PaNum As Integer, _ vACNay() As Variant, Status As String) Dim AyCol As Integer Status = "" If PaNum < 1 Or PaNum gPaQty Then Status = "PaNum = " & PaNum & " is invalid." Exit Sub End If On Error Resume Next If LBound(vACNay, 1) <= AyRow And AyRow <= UBound(vACNay, 1) _ And LBound(vACNay, 1) 0 Then vACNay(AyRow, SPaPaNumCol) = PaNum vACNay(AyRow, SPaPaAbbrCol) = PaAbr3_vPaNum(PaNum) 'Abr3 function vACNay(AyRow, SPaDlvCdCol) = "" 'DlvCd vACNay(AyRow, SPaACNCol) = "No Acct" 'ACN For AyCol = SPaSubscrCol To SPaDrawCol vACNay(AyRow, AyCol) = 0 Next AyCol For AyCol = SPaDrawCol + 1 To SPaColQty vACNay(AyRow, AyCol) = "" Next AyCol Else Err = 0 Status = "AyRow " & AyRow & " does not exist." End If End Sub -- Neal Z |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Bound Test in Variant Array
I suspect Non-zero is true, zero is false
see this test: Sub BAB() Dim v As Variant On Error Resume Next If 9 / 0 Then MsgBox "True" Else MsgBox "False" End If End Sub -- Regards, Tom Ogilvy "Neal Zimm" wrote: Hi - I don't understand why in the vACNay_Init sub, when I do NOT initialize the vACNay array via the ReDim in the calling macro, the Lbound and Ubound test 'fails' and the lines below the bound tests execute. When I step thru the code, I see the <subscript out of range msg when I 'cursor' the xbound phrases. When I execute the ReDim in sub test, called macro works as anticipated. Thanks. Neal Z. sub test() dim vACNay() as variant, Col as integer, AyRow as integer dim Status as string 'Note spaxxxcol series of vars are public constants as 'is gPaQty. 'ReDim vACNay(1, spacolqty) AyRow = 1 Call vACNay_Init(AyRow, 6, vACNay, Status) 'test show shows nothing when above ReDim is commented out, 'even tho' called lines seem to execute. For Col = 1 To spacolqty Debug.Print Col & ") -" & vACNay(AyRow, Col) & "-" Next Col End Sub Sub vACNay_Init(ByVal AyRow As Integer, ByVal PaNum As Integer, _ vACNay() As Variant, Status As String) Dim AyCol As Integer Status = "" If PaNum < 1 Or PaNum gPaQty Then Status = "PaNum = " & PaNum & " is invalid." Exit Sub End If On Error Resume Next If LBound(vACNay, 1) <= AyRow And AyRow <= UBound(vACNay, 1) _ And LBound(vACNay, 1) 0 Then vACNay(AyRow, SPaPaNumCol) = PaNum vACNay(AyRow, SPaPaAbbrCol) = PaAbr3_vPaNum(PaNum) 'Abr3 function vACNay(AyRow, SPaDlvCdCol) = "" 'DlvCd vACNay(AyRow, SPaACNCol) = "No Acct" 'ACN For AyCol = SPaSubscrCol To SPaDrawCol vACNay(AyRow, AyCol) = 0 Next AyCol For AyCol = SPaDrawCol + 1 To SPaColQty vACNay(AyRow, AyCol) = "" Next AyCol Else Err = 0 Status = "AyRow " & AyRow & " does not exist." End If End Sub -- Neal Z |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Bound Test in Variant Array
Yup, and thanks
Got confused which stmt execs after an on error resume next. appeciate it. -- Neal Z "Tom Ogilvy" wrote: I suspect Non-zero is true, zero is false see this test: Sub BAB() Dim v As Variant On Error Resume Next If 9 / 0 Then MsgBox "True" Else MsgBox "False" End If End Sub -- Regards, Tom Ogilvy "Neal Zimm" wrote: Hi - I don't understand why in the vACNay_Init sub, when I do NOT initialize the vACNay array via the ReDim in the calling macro, the Lbound and Ubound test 'fails' and the lines below the bound tests execute. When I step thru the code, I see the <subscript out of range msg when I 'cursor' the xbound phrases. When I execute the ReDim in sub test, called macro works as anticipated. Thanks. Neal Z. sub test() dim vACNay() as variant, Col as integer, AyRow as integer dim Status as string 'Note spaxxxcol series of vars are public constants as 'is gPaQty. 'ReDim vACNay(1, spacolqty) AyRow = 1 Call vACNay_Init(AyRow, 6, vACNay, Status) 'test show shows nothing when above ReDim is commented out, 'even tho' called lines seem to execute. For Col = 1 To spacolqty Debug.Print Col & ") -" & vACNay(AyRow, Col) & "-" Next Col End Sub Sub vACNay_Init(ByVal AyRow As Integer, ByVal PaNum As Integer, _ vACNay() As Variant, Status As String) Dim AyCol As Integer Status = "" If PaNum < 1 Or PaNum gPaQty Then Status = "PaNum = " & PaNum & " is invalid." Exit Sub End If On Error Resume Next If LBound(vACNay, 1) <= AyRow And AyRow <= UBound(vACNay, 1) _ And LBound(vACNay, 1) 0 Then vACNay(AyRow, SPaPaNumCol) = PaNum vACNay(AyRow, SPaPaAbbrCol) = PaAbr3_vPaNum(PaNum) 'Abr3 function vACNay(AyRow, SPaDlvCdCol) = "" 'DlvCd vACNay(AyRow, SPaACNCol) = "No Acct" 'ACN For AyCol = SPaSubscrCol To SPaDrawCol vACNay(AyRow, AyCol) = 0 Next AyCol For AyCol = SPaDrawCol + 1 To SPaColQty vACNay(AyRow, AyCol) = "" Next AyCol Else Err = 0 Status = "AyRow " & AyRow & " does not exist." End If End Sub -- Neal Z |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
| Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
| ReDim Object array as parameter of Variant array | Excel Programming | |||
| Variant Array | Excel Programming | |||
| variant array containing cel adresses convert to actual ranges-array | Excel Programming | |||
Hybrid Mode
