Bound Test in Variant Array

[Guest]

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

 

[Chip Pearson]

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)

 

[Guest]

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

 

[Chip Pearson]

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)

 

[Guest]

Yup, and thanks
Got confused re: which stmt execs after an on error resume next.
appeciate it.

 

[Guest]

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 re: not allocated arrays, and I have the code from your website.

Stupid me didn't use it here.
Thanks again.

 

[Guest]

Thanks again, I'll remember to use it this time.