Does anyone know how to proactively test an array for data prior to its use
without using an error event?

If not, what is a good error test?

I have been using 'Err.Number = 9' as a test to call the population routine.

Thanks,
VBA Dabbler

That works. Do you have a problem with it, or just checking if there is
another way.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"VBA Dabbler" wrote in message
...
Does anyone know how to proactively test an array for data prior to its
use
without using an error event?

If not, what is a good error test?

I have been using 'Err.Number = 9' as a test to call the population
routine.

Thanks,
VBA Dabbler

What does "proactively test an array for data" mean?

If the array is not dynamic it always exists in the sense that memory
is allocated to it.

If the non-dynamic array is not declared as variant all data elements
are initialized to something (yes, even object or string arrays:
Nothing for the former, zero length string for the latter)

For an array of type variant use IsEmpty(x(i))

For a dynamic array that has not been dimensioned with a redim
statement a error trap is mandatory. Maybe, you can explain why you'd
rather not use one...

Once a dynamic array is dimensioned, the same rules as in para 3 above
apply.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Does anyone know how to proactively test an array for data prior to its use
without using an error event?

If not, what is a good error test?

I have been using 'Err.Number = 9' as a test to call the population routine.

Thanks,
VBA Dabbler

Why do you want to Check?

Seems like you should know.

If it is a global variable that you are checking if it is populated then how
have you declared it?

If you used

Public v as Variant

then isarray(v) should work

If
Public v() as Whatever

then you have received answers for that.

--
Regards,
Tom Ogilvy


"VBA Dabbler" wrote in message
...
Does anyone know how to proactively test an array for data prior to its
use
without using an error event?

If not, what is a good error test?

I have been using 'Err.Number = 9' as a test to call the population
routine.

Thanks,
VBA Dabbler

Just checking for a better way.

"Bob Phillips" wrote:

That works. Do you have a problem with it, or just checking if there is
another way.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"VBA Dabbler" wrote in message
...
Does anyone know how to proactively test an array for data prior to its
use
without using an error event?

If not, what is a good error test?

I have been using 'Err.Number = 9' as a test to call the population
routine.

Thanks,
VBA Dabbler

Then as Tushar says, for a dynamic array, you need to trap it, so I think
you have the best way.

Regards

Bob


"VBA Dabbler" wrote in message
...
Just checking for a better way.

"Bob Phillips" wrote:

That works. Do you have a problem with it, or just checking if there is
another way.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"VBA Dabbler" wrote in message
...
Does anyone know how to proactively test an array for data prior to
its
use
without using an error event?

If not, what is a good error test?

I have been using 'Err.Number = 9' as a test to call the population
routine.

Thanks,
VBA Dabbler

I use a publicly declared dynamic array for multiple calls of the same
function, where the data does not change from call to call - this is
substantially faster than getting the data from a range, worksheet, or query
each time the function is called. However, I only need to populate the array
on the first instance of a call to the function.

I know it's an array, but won't know which call is the first,
programmatically. Typically I've been working off of the assumption that the
array exists and is populated, and handle the 'Subscript out of range' error
when it occurs.

See my responses below in square brackets.

Thanks,
VBA Dabbler

"Tom Ogilvy" wrote:

Why do you want to Check? [So that my code doesn't fail, when I know how to handle the condition.]

Seems like you should know. [See my second paragraph above.]

If it is a global variable that you are checking if it is populated then how
have you declared it?

If you used

Public v as Variant

then isarray(v) should work [It doesn't work as I need. I know it's an array - I want to know that it's populated. If it's not populated I get the 'Subscript out of range' error.]

If
Public v() as Whatever

then you have received answers for that.

--
Regards,
Tom Ogilvy


"VBA Dabbler" wrote in message
...
Does anyone know how to proactively test an array for data prior to its
use
without using an error event?

If not, what is a good error test?

I have been using 'Err.Number = 9' as a test to call the population
routine.

Thanks,
VBA Dabbler

I use a public dynamic array for multiple calls of the same function, where
the data does not change from call to call - this is substantially faster
than getting the data from a range, worksheet, or query each time the
function is called. However, I only need to populate the array on the first
instance of a call to the function.

"proactively test an array for data" means: Test for the condition before it
happens (and deal with it) rather than experience the condition and react to
it through error trapping.

I've tried the following and still get the 'Subscript out of range' error:

If IsEmpty(ReportDataArray(1, 1)) = True Then
MsgBox "Successful test!"
End If

Thanks for your suggestions. Any other ideas?

Thanks,
VBA Dabbler

"Tushar Mehta" wrote:

What does "proactively test an array for data" mean?

If the array is not dynamic it always exists in the sense that memory
is allocated to it.

If the non-dynamic array is not declared as variant all data elements
are initialized to something (yes, even object or string arrays:
Nothing for the former, zero length string for the latter)

For an array of type variant use IsEmpty(x(i))

For a dynamic array that has not been dimensioned with a redim
statement a error trap is mandatory. Maybe, you can explain why you'd
rather not use one...

Once a dynamic array is dimensioned, the same rules as in para 3 above
apply.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Does anyone know how to proactively test an array for data prior to its use
without using an error event?

If not, what is a good error test?

I have been using 'Err.Number = 9' as a test to call the population routine.

Thanks,
VBA Dabbler

[I said if you declared it as ]

[Public v as Variant]

[then it isn't an array until you make it one. If that isn't the way you
declared it, then the suggestion isn't pertinent - no use responding with I
know its an array and if you did declare it that way, then it isn't an array
until it has been assigned one. If you don't declare it that way, then
maybe you should and the problem is solved. ]

[See my and to answer to YouGotta.B.Kidding in September of 1997. (LOL)]

--
Regards,
Tom Ogilvy

"VBA Dabbler" wrote in message
...
I use a publicly declared dynamic array for multiple calls of the same
function, where the data does not change from call to call - this is
substantially faster than getting the data from a range, worksheet, or
query
each time the function is called. However, I only need to populate the
array
on the first instance of a call to the function.

I know it's an array, but won't know which call is the first,
programmatically. Typically I've been working off of the assumption that
the
array exists and is populated, and handle the 'Subscript out of range'
error
when it occurs.

See my responses below in square brackets.

Thanks,
VBA Dabbler

"Tom Ogilvy" wrote:

Why do you want to Check? [So that my code doesn't fail, when I know how
to handle the condition.]

Seems like you should know. [See my second paragraph above.]

If it is a global variable that you are checking if it is populated then
how
have you declared it?

If you used

Public v as Variant

then isarray(v) should work [It doesn't work as I need. I know it's an
array - I want to know that it's populated. If it's not populated I get the
'Subscript out of range' error.]

If
Public v() as Whatever

then you have received answers for that.

--
Regards,
Tom Ogilvy


"VBA Dabbler" wrote in message
...
Does anyone know how to proactively test an array for data prior to
its
use
without using an error event?

If not, what is a good error test?

I have been using 'Err.Number = 9' as a test to call the population
routine.

Thanks,
VBA Dabbler

Tom,
Thanks for your persistence. I had originally declared it with [Public v()
As Variant]. However, using the [Public v As Variant] declaration you
indicated and then testing with IsArray() has done the trick.

Thanks,
VBA Dabbler

"Tom Ogilvy" wrote:

[I said if you declared it as ]

[Public v as Variant]

[then it isn't an array until you make it one. If that isn't the way you
declared it, then the suggestion isn't pertinent - no use responding with I
know its an array and if you did declare it that way, then it isn't an array
until it has been assigned one. If you don't declare it that way, then
maybe you should and the problem is solved. ]

[See my and to answer to YouGotta.B.Kidding in September of 1997. (LOL)]

--
Regards,
Tom Ogilvy

"VBA Dabbler" wrote in message
...
I use a publicly declared dynamic array for multiple calls of the same
function, where the data does not change from call to call - this is
substantially faster than getting the data from a range, worksheet, or
query
each time the function is called. However, I only need to populate the
array
on the first instance of a call to the function.

I know it's an array, but won't know which call is the first,
programmatically. Typically I've been working off of the assumption that
the
array exists and is populated, and handle the 'Subscript out of range'
error
when it occurs.

See my responses below in square brackets.

Thanks,
VBA Dabbler

"Tom Ogilvy" wrote:

Why do you want to Check? [So that my code doesn't fail, when I know how
to handle the condition.]

Seems like you should know. [See my second paragraph above.]

If it is a global variable that you are checking if it is populated then
how
have you declared it?

If you used

Public v as Variant

then isarray(v) should work [It doesn't work as I need. I know it's an
array - I want to know that it's populated. If it's not populated I get the
'Subscript out of range' error.]

If
Public v() as Whatever

then you have received answers for that.

--
Regards,
Tom Ogilvy


"VBA Dabbler" wrote in message
...
Does anyone know how to proactively test an array for data prior to
its
use
without using an error event?

If not, what is a good error test?

I have been using 'Err.Number = 9' as a test to call the population
routine.

Thanks,
VBA Dabbler