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

  • Thread starter Thread starter Keith R
  • Start date Start date
K

Keith R

I'm writing some code to pull ranges from different data worksheets (always
one row, column C to AM) into an array, validate the data (make sure only
integers are present), do some calculations on the data (simple division),
put the results in a new array (two dimensional), then drop the contents of
that new array into a new target range.

When I pull the data in, I need to validate it to make sure that everything
that is pulled in is an integer (or blank). I'm wondering what syntax I
could use to cycle through an array in VBA, similar to:

For each (object) in (collection). What is the "name" of the object of one
"bucket" in an array?

I'm also having trouble with the syntax to pull the range into the array,
if anyone wants to give me a syntax hint there :)
is it because I'm declaring the size of the array, rather than an open
variant? If I did use a variant, can I treat it like an array without
cycling through anyway to dump it into an array? (I've tried dimming the
array as 2 dimensional as well, e.g. (1 to 1, 1 to 38) and that didn't seem
to work either)

Thanks,
Keith
XL97


Snippet:

Dim TArray(1 To 38) ' to hold my values from column C to AM

For RVal = 1 To 5 ' row number to pull the range from
UseRange = "C" & Trim(Str(RVal)) & ":AM" & Trim(Str(RVal))
' next line has had many tweaks, but still not working
TArray = ActiveSheet.Range(UseRange).Value
' I want to insert better syntax here, vs:
For i = 1 to 38
if isnumeric(TArray(i).value) then
if mod(TArray(i).value,1)=0 then
'do stuff
end if
end if
Next
Next
 
Can you simplify your code snippet? I'm having difficulty focussing on
exactly what your question is and how the code snippet is relevant to it.

By the way, C:AM is only 37 columns, not 38.

Alan Beban
 
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

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

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)
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

But perhaps there is no functional difference between these two?

Thanks again,
Keith
 
Keith said:
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
Click to expand...

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
Click to expand...

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)
Click to expand...

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
Click to expand...

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
 
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!
 
If TArray(1,i)=Int(TArray(1,i))

Alan Beban

Keith said:
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!
Click to expand...
 
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
 
A method that does not require any iteration...

MsgBox Evaluate("sum(abs(trunc(C1:I1)-C1:I1))") = 0

Adjust for your own needs.

--
Regards,

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