PDA

View Full Version : Creating a Simple Array



Opv
07-11-2010, 08:01 PM
Try as I may I still can't grasp the concept of creating an array. What is the syntax for creating a simple array with a group of column numbers, say, columns 1, 2, 4, 7, 10, so that I could check against the array to determine if the activecell.column is within the array?

Blade Hunter
07-11-2010, 09:49 PM
Sub temp()
Dim Arr() As Variant
Dim InArray As Boolean
Arr = Array(1, 2, 4, 7, 10)
InArray = False
For X = LBound(Arr) To UBound(Arr)
If ActiveCell.Column = Arr(X) Then InArray = True
Next
If InArray Then MsgBox "Found in Array"
End Sub


Hope that helps

Dan

Edit: Quote: mikerickson: the resulting array's lowest index is 0.

I ALWAYS forget this :(. Code updated :) Thanks Mike

mikerickson
07-11-2010, 10:01 PM
This is one way. Note that when using the function Array, the resulting array's lowest index is 0. (This is called a "zero based array")
Dim arrayOfNumbers as Variant, i as Long

arrayOfNumbers = Array(1, 2, 4, 7, 10)

For i = 0 to 4
MsgBox arrayOfNumbers(i)
Next i
Another way would be. This explicitly sets the bounds of arrayOfNumbers. This arrayOfNumbers is "one based".
Dim arrayOfNumbers(1 to 5) As Long, i as Long

arrayOfNumbers(1) = 1
arrayOfNumbers(2) = 2
arrayOfNumbers(3) = 4
arrayOfNumbers(4) = 7
arrayOfNumbers(5) = 10

For i = 1 to 5
MsgBox arrayOfNumbers(i)
Next i
Arrays can be based on any number (including negative). This uses the ReDim statement to force arrayOfNumbers to have indices between 3 and 7.
Dim arrayOfNumbers() As Long, i as Long

ReDim arrayOfNumbers(3 to 7)

arrayOfNumbers(3) = 1
arrayOfNumbers(4) = 2
arrayOfNumbers(5) = 4
arrayOfNumbers(6) = 7
arrayOfNumbers(7) = 10

For i = 3 to 7
MsgBox arrayOfNumbers(i)
Next i
To test if the ActiveCell is in any of the columns, I'd use code like
If IsNumeric(Application.Match(ActiveCell.Column, Array(1, 2, 4, 7, 10), 0)) Then
MsgBox Active Cell.Address & " is in one of the columns."
Else
MsgBox ActiveCell.Address & " is not."
End If

Aussiebear
07-12-2010, 12:17 AM
Ever thought about tutoring Mike?

mikerickson
07-12-2010, 12:31 AM
I've tutored enough to know that every math tutee will need help with logarithms.

Opv
07-12-2010, 09:37 AM
To test if the ActiveCell is in any of the columns, I'd use code like
If IsNumeric(Application.Match(ActiveCell.Column, Array(1, 2, 4, 7, 10), 0)) Then
MsgBox Active Cell.Address & " is in one of the columns."
Else
MsgBox ActiveCell.Address & " is not."
End If

Thanks to everyone for the help. It makes sense. I like the above suggestion for testing whether the active cell is in one of the defined columns. How would the above script be revised to accommodate the array being defined outside the Match statement, for example, if an array called dateCols has already been created and defined prior to getting to the Match test?

mikerickson
07-12-2010, 12:47 PM
It would become
Dim arrayOfColumnNumbers
arrayOfColumnNumbers = Array(1, 2, 4, 7, 10)

If IsNumberic(Application.Match(ActiveCell.Column, arrayOfColumnNumers,0)) Then
' etc.

Opv
07-12-2010, 01:10 PM
It would become
Dim arrayOfColumnNumbers
arrayOfColumnNumbers = Array(1, 2, 4, 7, 10)

If IsNumberic(Application.Match(ActiveCell.Column, arrayOfColumnNumers,0)) Then
' etc.

Thanks. I appreciate the help.

Opv