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