Two part question....

I have the following code driver to show the example

The Links array will have been populated with values
I then want to call my Filter subroutine, to find links which match
sFilter, and add them to the Target array - called filter

The code below works, but I'd like to avoid the "On error" if I could.
In the debugger I see first time in that sTarget has a value of Nothing,
but I can't work out how to test for this. Both
if sTarget is Nothing and
if sTarget() is Nothing
fail. What do I need to do to solve this

Option Explicit
Option Base 1

Type LinkInfo
href As String
InnerText As String
End Type

Dim Links() As LinkInfo
Dim Filtered() As LinkInfo
Sub Setup()

ReDim Links(4)
Links(1).href = "AAAAA"
Links(2).href = "ABBBA"
Links(3).href = "AAACCC"
Links(4).href = "ADDDA"

Filter Links, Filtered, "AA"
Filter Links, Filtered, "DDD"
Links(4).href = "ADDDA"

End Sub
Sub Filter(sSource() As LinkInfo, sTarget() As LinkInfo, sFilter As
String)

Dim iIndex As Integer
Dim iLength As Integer

On Error Resume Next
iLength = UBound(sTarget)
On Error GoTo 0

For iIndex = 1 To UBound(sSource)
If sSource(iIndex).href Like "*" & sFilter & "*" Then
iLength = iLength + 1

If iLength = 1 Then
ReDim sTarget(1)
Else
ReDim Preserve sTarget(iLength)
End If

sTarget(iLength) = sSource(iIndex)
End If
Next

End Sub


Part Two...
Suppose Filtered() was a global variable.
How would I "clear it down" so that I could manipulate a further set of
links and effectively start over?
The only code that will work is
ReDim Filtered(1)


so I suppose all of the above is going to point to me using an option
base of 0 - and wasting that element of the array. This will then
provide a solution for my uninitialised array in my Filter routine. Is
that what you reckon?
--
Mike
Please post replies to newsgroup to benefit others
Replace dead spam with ntl world to reply by email

I do the On Error Resume Next thing to determine the length of an array...
I've seen others do it to.

To wipe an array use the Erase statement.

Sub test()
Dim arr() As String, lng As Long

' ReDim arr(0)

lng = -1
On Error Resume Next
lng = UBound(arr)
On Error GoTo 0

MsgBox "array length is " & IIf(lng = -1, "empty", lng)

Erase arr
End Sub

Just a note. If I were you, I would start working in Option Base 0. It is
more compatible with other programming languages.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Mike NG" wrote in message
...
Two part question....

I have the following code driver to show the example

The Links array will have been populated with values
I then want to call my Filter subroutine, to find links which match
sFilter, and add them to the Target array - called filter

The code below works, but I'd like to avoid the "On error" if I could.
In the debugger I see first time in that sTarget has a value of Nothing,
but I can't work out how to test for this. Both
if sTarget is Nothing and
if sTarget() is Nothing
fail. What do I need to do to solve this

Option Explicit
Option Base 1

Type LinkInfo
href As String
InnerText As String
End Type

Dim Links() As LinkInfo
Dim Filtered() As LinkInfo
Sub Setup()

ReDim Links(4)
Links(1).href = "AAAAA"
Links(2).href = "ABBBA"
Links(3).href = "AAACCC"
Links(4).href = "ADDDA"

Filter Links, Filtered, "AA"
Filter Links, Filtered, "DDD"
Links(4).href = "ADDDA"

End Sub
Sub Filter(sSource() As LinkInfo, sTarget() As LinkInfo, sFilter As
String)

Dim iIndex As Integer
Dim iLength As Integer

On Error Resume Next
iLength = UBound(sTarget)
On Error GoTo 0

For iIndex = 1 To UBound(sSource)
If sSource(iIndex).href Like "*" & sFilter & "*" Then
iLength = iLength + 1

If iLength = 1 Then
ReDim sTarget(1)
Else
ReDim Preserve sTarget(iLength)
End If

sTarget(iLength) = sSource(iIndex)
End If
Next

End Sub


Part Two...
Suppose Filtered() was a global variable.
How would I "clear it down" so that I could manipulate a further set of
links and effectively start over?
The only code that will work is
ReDim Filtered(1)


so I suppose all of the above is going to point to me using an option
base of 0 - and wasting that element of the array. This will then
provide a solution for my uninitialised array in my Filter routine. Is
that what you reckon?
--
Mike
Please post replies to newsgroup to benefit others
Replace dead spam with ntl world to reply by email

In article ,
says...

The code below works, but I'd like to avoid the "On error" if I could.

Why? Proper error trapping & handling (i.e., sans indiscriminate
GoTos) is a perfectly useful programming technique.

--
Regards,

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

In article ,
says...
Two part question....

I have the following code driver to show the example

The Links array will have been populated with values
I then want to call my Filter subroutine, to find links which match
sFilter, and add them to the Target array - called filter

The code below works, but I'd like to avoid the "On error" if I could.
In the debugger I see first time in that sTarget has a value of Nothing,
but I can't work out how to test for this. Both
if sTarget is Nothing and
if sTarget() is Nothing
fail. What do I need to do to solve this

Option Explicit
Option Base 1

Type LinkInfo
href As String
InnerText As String
End Type

Dim Links() As LinkInfo
Dim Filtered() As LinkInfo
Sub Setup()

ReDim Links(4)
Links(1).href = "AAAAA"
Links(2).href = "ABBBA"
Links(3).href = "AAACCC"
Links(4).href = "ADDDA"

Filter Links, Filtered, "AA"
Filter Links, Filtered, "DDD"
Links(4).href = "ADDDA"

End Sub
Sub Filter(sSource() As LinkInfo, sTarget() As LinkInfo, sFilter As
String)

Dim iIndex As Integer
Dim iLength As Integer

On Error Resume Next
iLength = UBound(sTarget)
On Error GoTo 0

For iIndex = 1 To UBound(sSource)
If sSource(iIndex).href Like "*" & sFilter & "*" Then
iLength = iLength + 1

If iLength = 1 Then
ReDim sTarget(1)
Else
ReDim Preserve sTarget(iLength)
End If

sTarget(iLength) = sSource(iIndex)
End If
Next

End Sub


Part Two...
Suppose Filtered() was a global variable.
How would I "clear it down" so that I could manipulate a further set of
links and effectively start over?
The only code that will work is
ReDim Filtered(1)


so I suppose all of the above is going to point to me using an option
base of 0 - and wasting that element of the array. This will then
provide a solution for my uninitialised array in my Filter routine. Is
that what you reckon?

While this would work for 99.999% of the cases, it would report an
erroneous result if the upper bound were -1! {g}

Sub testIt()
Dim x(), UB As Long
On Error Resume Next
UB = UBound(x)
MsgBox IIf(Err.Number < 0, "X is empty", "X ubound=" & UB)
On Error GoTo 0
ReDim x(-10 To -1)
On Error Resume Next
UB = UBound(x)
MsgBox IIf(Err.Number < 0, "X is empty", "X ubound=" & UB)
On Error GoTo 0
End Sub

--
Regards,

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

In article ,
says...
I do the On Error Resume Next thing to determine the length of an array...
I've seen others do it to.

To wipe an array use the Erase statement.

Sub test()
Dim arr() As String, lng As Long

' ReDim arr(0)

lng = -1
On Error Resume Next
lng = UBound(arr)
On Error GoTo 0

MsgBox "array length is " & IIf(lng = -1, "empty", lng)

Erase arr
End Sub

Just a note. If I were you, I would start working in Option Base 0. It is
more compatible with other programming languages.

I was wondering if anyone would point that out...

In my view it's wrong to set lbound to anything other than zero - I hinted
at that with my Base 0 comment.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Tushar Mehta" wrote in message
...
While this would work for 99.999% of the cases, it would report an
erroneous result if the upper bound were -1! {g}

Sub testIt()
Dim x(), UB As Long
On Error Resume Next
UB = UBound(x)
MsgBox IIf(Err.Number < 0, "X is empty", "X ubound=" & UB)
On Error GoTo 0
ReDim x(-10 To -1)
On Error Resume Next
UB = UBound(x)
MsgBox IIf(Err.Number < 0, "X is empty", "X ubound=" & UB)
On Error GoTo 0
End Sub

--
Regards,

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

In article ,
says...
I do the On Error Resume Next thing to determine the length of an
array...
I've seen others do it to.

To wipe an array use the Erase statement.

Sub test()
Dim arr() As String, lng As Long

' ReDim arr(0)

lng = -1
On Error Resume Next
lng = UBound(arr)
On Error GoTo 0

MsgBox "array length is " & IIf(lng = -1, "empty", lng)

Erase arr
End Sub

Just a note. If I were you, I would start working in Option Base 0. It is
more compatible with other programming languages.

Rob,

I agree with you that in many cases it's preferable to work with 0
based arrays (that is with a LOWERbound of 0)

However Excel will return 1 based arrays on many of it's objects
properties

I believe that Tushar's comment re an UPPERBOUND of -1 may relate
to some functions like split/filter or a scripting dictionary's items
array which return an (0 to -1) array if no results were found.

I've just written following function which gives the DIMENSIONS of an
array. -1 for NO array, 0 for uninitialized etc. According to VBA help
vb can handle a max of 60 dimensions. (if you dont run out of memory)

Function ArrDim(vArr As Variant) As Integer
Dim i%
On Error Resume Next
If IsArray(vArr) Then
For i = 0 To 59
If IsError(LBound(vArr, i + 1)) Then Exit For
Next
Else
i = -1
End If
ArrDim = i
End Function

Once you know the dimensions you can safely test the lbound and ubound.


HTH




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Rob van Gelder wrote :

I was wondering if anyone would point that out...

In my view it's wrong to set lbound to anything other than zero - I
hinted at that with my Base 0 comment.

keepITcool,

I do realise when it's approriate to use non 0 based - I just wont design
apps that way myself...
You have some valid points about possible uses.

Your ArrDim is very similar to my own Number of Dimensions routine - though
I don't use IsArray

Sub test()
Dim arr(1, 2, 3, 4) As Long
Dim i As Long

On Error Resume Next
Do: i = i - (LBound(arr, i + 1) * 0 = 0): Loop Until Err.Number
On Error GoTo 0

MsgBox i
End Sub

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"keepITcool" wrote in message
ft.com...
Rob,

I agree with you that in many cases it's preferable to work with 0
based arrays (that is with a LOWERbound of 0)

However Excel will return 1 based arrays on many of it's objects
properties

I believe that Tushar's comment re an UPPERBOUND of -1 may relate
to some functions like split/filter or a scripting dictionary's items
array which return an (0 to -1) array if no results were found.

I've just written following function which gives the DIMENSIONS of an
array. -1 for NO array, 0 for uninitialized etc. According to VBA help
vb can handle a max of 60 dimensions. (if you dont run out of memory)

Function ArrDim(vArr As Variant) As Integer
Dim i%
On Error Resume Next
If IsArray(vArr) Then
For i = 0 To 59
If IsError(LBound(vArr, i + 1)) Then Exit For
Next
Else
i = -1
End If
ArrDim = i
End Function

Once you know the dimensions you can safely test the lbound and ubound.


HTH




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Rob van Gelder wrote :

I was wondering if anyone would point that out...

In my view it's wrong to set lbound to anything other than zero - I
hinted at that with my Base 0 comment.

I've never understood the dogma with zero-based indexing. Yes,
computer efficiency requires we don't 'throw away' zero values, but in
high level languages?

When planning a rolling horizon program that looks from day 31 to day
100, I unequivocally prefer dim x(31 to 100). An artificial zero or
even 1 lower bound is an invitation to programming errors, especially
boundary condition errors.

In any case, you are in good company. .Net enforces a zero lower
bound.

--
Regards,

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

In article ,
says...
I was wondering if anyone would point that out...

In my view it's wrong to set lbound to anything other than zero - I hinted
at that with my Base 0 comment.

The simplest function that I can think of for finding the number of
dimensions:

Function ArrDim(vArr As Variant) As Integer
Dim i As Long, x As Long
On Error GoTo XIT
i = 1
Do
x = LBound(vArr, i)
i = i + 1
Loop While True
XIT:
ArrDim = i - 1
End Function

Of course, it doesn't distinguish between an uninitialized array and a
non-array, returning zero in both cases. To distinguish between the
two:
Function ArrDim(vArr As Variant) As Integer
Dim i As Long, x As Long
If Not IsArray(vArr) Then ArrDim = -1: Exit Function '<<<<<
On Error GoTo XIT
i = 1
Do
x = LBound(vArr, i)
i = i + 1
Loop While True
XIT:
ArrDim = i - 1
End Function


--
Regards,

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

In article om,
says...
Rob,

I agree with you that in many cases it's preferable to work with 0
based arrays (that is with a LOWERbound of 0)

However Excel will return 1 based arrays on many of it's objects
properties

I believe that Tushar's comment re an UPPERBOUND of -1 may relate
to some functions like split/filter or a scripting dictionary's items
array which return an (0 to -1) array if no results were found.

I've just written following function which gives the DIMENSIONS of an
array. -1 for NO array, 0 for uninitialized etc. According to VBA help
vb can handle a max of 60 dimensions. (if you dont run out of memory)

Function ArrDim(vArr As Variant) As Integer
Dim i%
On Error Resume Next
If IsArray(vArr) Then
For i = 0 To 59
If IsError(LBound(vArr, i + 1)) Then Exit For
Next
Else
i = -1
End If
ArrDim = i
End Function

Once you know the dimensions you can safely test the lbound and ubound.


HTH




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Rob van Gelder wrote :

I was wondering if anyone would point that out...

In my view it's wrong to set lbound to anything other than zero - I
hinted at that with my Base 0 comment.

It's tempting to argue this further, but I don't think I could provide a
strong case to using zero-based indexing.
I've only ever used zero-based - I've read no studies on bugcount
for/against zero-based. I'm just not the guy to push the point :)

The only thing I could think of is reusing the index variable.. eg. Copying
your x array to a range might require two indexes where zero-based requires
just one.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Tushar Mehta" wrote in message
...
I've never understood the dogma with zero-based indexing. Yes,
computer efficiency requires we don't 'throw away' zero values, but in
high level languages?

When planning a rolling horizon program that looks from day 31 to day
100, I unequivocally prefer dim x(31 to 100). An artificial zero or
even 1 lower bound is an invitation to programming errors, especially
boundary condition errors.

In any case, you are in good company. .Net enforces a zero lower
bound.

--
Regards,

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

In article ,
says...
I was wondering if anyone would point that out...

In my view it's wrong to set lbound to anything other than zero - I
hinted
at that with my Base 0 comment.

Rob,

if you want to dump a simple array afaik excel doesnt care about upper
and lower bounds.


if you do want to change the upper and lower boundaries
(while keeping the same number of elements AND preserving the data..
the variable must a declared as a variant not a variant array,
and you could do a simple redim preserve.

I'll try to illustrate:


Sub Redimming()
Dim i, arr(), var, cpy

ReDim var(10 To 19)
ReDim arr(10 To 19)
For i = LBound(arr) To UBound(arr)
var(i) = i
arr(i) = i
Next

[a1:a5].Clear
'dump the 10-based VARIANT
[a1].Resize(1, UBound(var) - LBound(var) + 1) = var
'dump the 10-based VARIANT ARRAY
[a2].Resize(1, UBound(arr) - LBound(arr) + 1) = arr

'NOTE REDIM PRESERVE BOTH BOUNDS WORKS FOR A 'PLAIN' VARIANT ONLY
'redim, dump the 1-based
ReDim Preserve var(1 To UBound(var) - LBound(var) + 1)
[a3].Resize(1, UBound(var) - LBound(var) + 1) = var
'redim, dump the 0-based
ReDim Preserve var(0 To UBound(var) - LBound(var))
[a4].Resize(1, UBound(var) - LBound(var) + 1) = var

'Copying ARR to a variant... and THEN resizing works
cpy = arr
ReDim Preserve cpy(0 To UBound(cpy) - LBound(cpy))
[a5].Resize(1, UBound(cpy) - LBound(cpy) + 1) = cpy

'resizing ARR will not work
ReDim Preserve arr(0 To UBound(cpy) - LBound(cpy))

End Sub







--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Rob van Gelder wrote :

It's tempting to argue this further, but I don't think I could
provide a strong case to using zero-based indexing.
I've only ever used zero-based - I've read no studies on bugcount
for/against zero-based. I'm just not the guy to push the point :)

The only thing I could think of is reusing the index variable.. eg.
Copying your x array to a range might require two indexes where
zero-based requires just one.

I gave a bad example but I think I'll just let it go.

I'd forgotten about the array dump to range - that's a nice feature.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"keepITcool" wrote in message
ft.com...
Rob,

if you want to dump a simple array afaik excel doesnt care about upper
and lower bounds.


if you do want to change the upper and lower boundaries
(while keeping the same number of elements AND preserving the data..
the variable must a declared as a variant not a variant array,
and you could do a simple redim preserve.

I'll try to illustrate:


Sub Redimming()
Dim i, arr(), var, cpy

ReDim var(10 To 19)
ReDim arr(10 To 19)
For i = LBound(arr) To UBound(arr)
var(i) = i
arr(i) = i
Next

[a1:a5].Clear
'dump the 10-based VARIANT
[a1].Resize(1, UBound(var) - LBound(var) + 1) = var
'dump the 10-based VARIANT ARRAY
[a2].Resize(1, UBound(arr) - LBound(arr) + 1) = arr

'NOTE REDIM PRESERVE BOTH BOUNDS WORKS FOR A 'PLAIN' VARIANT ONLY
'redim, dump the 1-based
ReDim Preserve var(1 To UBound(var) - LBound(var) + 1)
[a3].Resize(1, UBound(var) - LBound(var) + 1) = var
'redim, dump the 0-based
ReDim Preserve var(0 To UBound(var) - LBound(var))
[a4].Resize(1, UBound(var) - LBound(var) + 1) = var

'Copying ARR to a variant... and THEN resizing works
cpy = arr
ReDim Preserve cpy(0 To UBound(cpy) - LBound(cpy))
[a5].Resize(1, UBound(cpy) - LBound(cpy) + 1) = cpy

'resizing ARR will not work
ReDim Preserve arr(0 To UBound(cpy) - LBound(cpy))

End Sub







--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Rob van Gelder wrote :

It's tempting to argue this further, but I don't think I could
provide a strong case to using zero-based indexing.
I've only ever used zero-based - I've read no studies on bugcount
for/against zero-based. I'm just not the guy to push the point :)

The only thing I could think of is reusing the index variable.. eg.
Copying your x array to a range might require two indexes where
zero-based requires just one.

On Mon, 24 Jan 2005 at 12:48:45, Rob van Gelder (Rob van Gelder
) wrote:
To wipe an array use the Erase statement.

Many thanks
--
Mike
Please post replies to newsgroup to benefit others
Replace dead spam with ntl world to reply by email

On Mon, 24 Jan 2005 at 04:46:27, Tushar Mehta (Tushar Mehta
) wrote:
Of course, it doesn't distinguish between an uninitialized array and a
non-array, returning zero in both cases. To distinguish between the
two:
Function ArrDim(vArr As Variant) As Integer
Dim i As Long, x As Long
If Not IsArray(vArr) Then ArrDim = -1: Exit Function '<<<<<
On Error GoTo XIT
i = 1
Do
x = LBound(vArr, i)
i = i + 1
Loop While True
XIT:
ArrDim = i - 1
End Function

I understand the theory behind the parameter being variant, but I can't
get this to compile. Keep getting
"Only user-defined types defined in public object models can be coerced
to or from a variant or passed to late bound functions"

I had all of my original code in Module1, and added your function, then
tried to call it thus
Msgbox ArrDim(Filtered)

I've tried all combinations of the variable and the function being
normal and declared public, but I can't get the compile error to clear
--
Mike
Please post replies to newsgroup to benefit others
Replace dead spam with ntl world to reply by email

On Mon, 24 Jan 2005 at 01:13:20, Tushar Mehta (Tushar Mehta
) wrote:
In article ,
says...

The code below works, but I'd like to avoid the "On error" if I could.

Why? Proper error trapping & handling (i.e., sans indiscriminate
GoTos) is a perfectly useful programming technique.

Cos usually there is an alternate way - like the variant method when I
can get it to compile
--
Mike
Please post replies to newsgroup to benefit others
Replace dead spam with ntl world to reply by email