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

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" <(E-Mail Removed)> wrote in message
news:9CC5B044-004B-4909-8837-(E-Mail Removed)...
> 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

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

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" <(E-Mail Removed)> wrote in message
news:20DF5CCA-94A8-4372-9488-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:9CC5B044-004B-4909-8837-(E-Mail Removed)...
>> 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
>

Yup, and thanks
Got confused re: 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

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.

--
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" <(E-Mail Removed)> wrote in message
> news:9CC5B044-004B-4909-8837-(E-Mail Removed)...
> > 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
>

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" <(E-Mail Removed)> wrote in message
> news:20DF5CCA-94A8-4372-9488-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:9CC5B044-004B-4909-8837-(E-Mail Removed)...
> >> 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
> >
>