Q: Better syntax to cycle through elements of an array?
 

? 2 mod 1
0
? 2.5 mod 1
0

--
Regards.
Tom Ogilvy

Alan Beban wrote in message
...
Keith R wrote:
Oops on the size of the array!
again, snippets are just for display, I've been trying all kinds of
syntax variations with no luck :(

Code snippet 1: throws a compile error, can't assign range to array.

Sub PullArray
Dim TArray(1 To 37) As Variant
TArray = ActiveSheet.Range("C5:AM5").Value '<--
End Sub

Sub PullArray()
Dim TArray() As Variant
ReDim TArray(1 To 1, 1 To 37)
TArray = ActiveSheet.Range("C5:AM5").Value '<--
End Sub


Code snippet 2: If I dim TArray as variant it runs, but then
I can't access the result as an array, e.g. the following
throws a runtime error 9: subscript out of range

Sub PullArray
Dim TArray As Variant '(no longer an array)
TArray = ActiveSheet.Range("C5:AM5").Value
Msgbox TArray(1).value '<--
End Sub

You need TArray(1,1). All arrays loaded directly from worksheet ranges
are 2-dimensional, even "one-row" arrays


Code Snippet 3: Assuming I get the range captured into
my array, I want to validate each bucket, and only allow
integers or blanks, everything else should throw a
messagebox to me to find the problem cell. I was
looking to loop through the collection with a "for/each"
instead of a "for i to x":

Dim r as "element of array" '(not sure what syntax to use here)

Dim r as Variant. You need to accommodate whatever types of values might
have been in the range.

For each r in TArray
If isnumeric(r.value) AND (mod(r.value,1)=0) then
'perform my calculations
Else
Msgbox ("show cell address of bad data so I can fix it")
End if
Next

' versus my current alternative, which is

For i = 1 to 37
If isnumeric(TArray(i).value) AND (mod(TArray(i),1)=0) then
'perform my calculations
Else
Msgbox ("show cell address of bad data so I can fix it")
End if
Next

I would use the current alternative so that I could use the index
numbers. But I would precede the loop with

Set rng = ActiveSheet.Range("C5"); then
For i = 1 to 37
If isnumeric(TArray(1,i)) AND (TArray(1,i) Mod 1) = 0) Then
'perform my calculations
Else
Msgbox rng(1,i).Address
End if
Next

I don't really understand the logic of the

If isnumeric(TArray(1,i)) AND (TArray(1,i) Mod 1)=0) Then

What do you think it's doing?

Alan Beban

Q: Better syntax to cycle through elements of an array?
 

I was trying to determine if each member of the array was
an integer, or blank, or "other". That particular line was
trying to identify if the value was an integer - that particular
sytax was untested because I hadn't gotten the array to
load yet when I wrote the original post.

It was intended to perform the mod function to determine
whether the number was an integer, e.g. like on a
worksheet function
=mod(22,1) = 0
=mod(22.5,1)=.5

but I forgot that the VBA 'mod' rounds to the nearest whole,
so I'll have to look to see what my alternatives are.
Interestingly, the Mod keyword doesn't show up under
Excel.worksheetfunction.(mod), so I can't avoid the VBA
function and get my decimal like I can on the worksheet.
I'm sure I've identified integers somewhere in another VBA
workbook, I just need to remember where :)

I suppose an easier way to check would be something like:
if (TArray(1,i) is integer) then...
or
if (isinteger(TArray(1,i)) then...

but alas, VBA doesn't like me making up my own
commands without creating a function that actually does
what I'm trying to figure out how to do anyway ;)

Keith R

Thank you again for the syntax assistance!


Tom Ogilvy wrote in article
...
? 2 mod 1
0
? 2.5 mod 1
0

--
Regards.
Tom Ogilvy

<snip
If isnumeric(TArray(1,i)) AND (TArray(1,i) Mod 1) = 0) Then
<snip

I don't really understand the logic of the

If isnumeric(TArray(1,i)) AND (TArray(1,i) Mod 1)=0) Then

What do you think it's doing?

Alan Beban

Q: Better syntax to cycle through elements of an array?
 

If TArray(1,i)=Int(TArray(1,i))

Alan Beban

Keith R wrote:
I was trying to determine if each member of the array was
an integer, or blank, or "other". That particular line was
trying to identify if the value was an integer - that particular
sytax was untested because I hadn't gotten the array to
load yet when I wrote the original post.

It was intended to perform the mod function to determine
whether the number was an integer, e.g. like on a
worksheet function
=mod(22,1) = 0
=mod(22.5,1)=.5

but I forgot that the VBA 'mod' rounds to the nearest whole,
so I'll have to look to see what my alternatives are.
Interestingly, the Mod keyword doesn't show up under
Excel.worksheetfunction.(mod), so I can't avoid the VBA
function and get my decimal like I can on the worksheet.
I'm sure I've identified integers somewhere in another VBA
workbook, I just need to remember where :)

I suppose an easier way to check would be something like:
if (TArray(1,i) is integer) then...
or
if (isinteger(TArray(1,i)) then...

but alas, VBA doesn't like me making up my own
commands without creating a function that actually does
what I'm trying to figure out how to do anyway ;)

Keith R

Thank you again for the syntax assistance!


Tom Ogilvy wrote in article
...

? 2 mod 1
0
? 2.5 mod 1
0

--
Regards.
Tom Ogilvy


<snip

If isnumeric(TArray(1,i)) AND (TArray(1,i) Mod 1) = 0) Then

<snip

I don't really understand the logic of the

If isnumeric(TArray(1,i)) AND (TArray(1,i) Mod 1)=0) Then

What do you think it's doing?

Alan Beban

Q: Better syntax to cycle through elements of an array?
 

If TArray is not numeric, what I last previously posted will redturn an
error; one way to deal with that is something like the following:

On Error Resume Next
For Each Elem In rng
If Elem = Int(Elem) Then
If Err = 0 Then
Do whatever
End If
Err = 0
End If
Next

Alan Beban

Alan Beban wrote:
If TArray(1,i)=Int(TArray(1,i))

Alan Beban

Keith R wrote:

I was trying to determine if each member of the array was an integer,
or blank, or "other". That particular line was trying to identify if
the value was an integer - that particular sytax was untested because
I hadn't gotten the array to load yet when I wrote the original post.

It was intended to perform the mod function to determine whether the
number was an integer, e.g. like on a worksheet function
=mod(22,1) = 0
=mod(22.5,1)=.5

but I forgot that the VBA 'mod' rounds to the nearest whole, so I'll
have to look to see what my alternatives are. Interestingly, the Mod
keyword doesn't show up under Excel.worksheetfunction.(mod), so I
can't avoid the VBA function and get my decimal like I can on the
worksheet. I'm sure I've identified integers somewhere in another VBA
workbook, I just need to remember where :)

I suppose an easier way to check would be something like:
if (TArray(1,i) is integer) then...
or if (isinteger(TArray(1,i)) then...

but alas, VBA doesn't like me making up my own commands without
creating a function that actually does what I'm trying to figure out
how to do anyway ;)

Keith R

Thank you again for the syntax assistance!


Tom Ogilvy wrote in article
...

? 2 mod 1
0
? 2.5 mod 1
0

--
Regards.
Tom Ogilvy



<snip

If isnumeric(TArray(1,i)) AND (TArray(1,i) Mod 1) = 0) Then


<snip

I don't really understand the logic of the

If isnumeric(TArray(1,i)) AND (TArray(1,i) Mod 1)=0) Then

What do you think it's doing?

Alan Beban