Award Winning Plant Industry Community

Excel VBA – Create A Basic Array Part 2

By on June 1, 2011 in Programming, SPPID, Tutorial with 1 Comment

‘Yesterday, we learned how to create a basic array in Excel VBA.
‘Today we are going to actually do something with it.
‘Let’s highlight the rows of the people that are over 35. Here we go.

Sub CollectRowValuesInAnArray()
Dim startrow As Long
Dim lastrow As Long
Dim myArray()
Dim a As Long
Dim i As Long
Dim myRowValue As String
Dim myColumn As Long

'In your dim statements add

Dim myRow As Long
Dim mySecondRowValue As String
Dim myThirdRowValue As String
dim myName as string
dim myAge as long
dim myFood as string

'set your startrow to the begining of your row data
'this could be row 1 but sometimes there is a header to consider.
'my header is on row 1, so I start on row 2
startrow = 2

'we want to find the last row that contains data
lastrow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row 'the last row with data

'my starting column is column1 (A)

myColumn = 1

'Let's start the loop!

For i = startrow To lastrow

'store the row values in the myRowValue variables
myRowValue = Cells(i, myColumn).Value
mySecondRowValue = Cells(i, myColumn + 1).Value
myThirdRowValue = Cells(i, myColumn + 2).Value
myRow = i 'the row number is now stored in the myRow variable

'next, we are going to write them to the array
'you must have this line to preserve what's in the array so when you add a new
'value it doesn't overwrite the old value
ReDim Preserve myArray(a)

'Now we have to check if there is anything in the array
'before we add to it
If Len(myArray(a)) > 0 Then 'if there is anything in the array then
' then we need to make the array equal to what's already in the array plus the new stuff

myArray(a) = myArray(a) & myRow & "," & myRowValue & "," & mySecondRowValue & "," & myThirdRowValue
a = a + 1 'we are using "a" as a counter to increment the array
'so now, our array looks something like this
' "2,Dave,52,Squash" ...etc

Else 'if not this means it's the first value and we just need the row and values

myArray(a) = myRow & "," & myRowValue & "," & mySecondRowValue & "," & myThirdRowValue

a = a + 1

End If

Next

For n = LBound(myArray) To UBound(myArray)

c = Split(myArray(n), ",") 'Split allows us to find what's in between the commas

'Here comes the fun part
'we need to loop through the now split values
'to do that, simply follow the code below
For b = LBound(c) To UBound(c)
Debug.Print c(b) 'now we can set variables for the returned values
myRow = c(b) 'my row number
myName = c(b + 1) 'my name
myAge = c(b + 2) 'my age
myFood = c(b + 3) 'my favorite food

'now let's perform a simple operation

If myAge > 35 Then 'if my age is greater than 35
Rows(myRow).Interior.ColorIndex = 45 'color my row
GoTo nextN 'go to the next item in the array
Else
GoTo nextN 'my age is less then or equal to 35, skip me
End If

Next

nextN:
Next

End Sub

There are far easier ways to do this if you take this tutorial at face value. However, if you dig a little deeper and apply this method to your reports, you will find that you have much greater flexibility in comparing values and then performing calculations/comparisons on them once you do.

Hope this helps, feel free to ask me questions if you have any. :)

Tags: , , , , ,

About the Author

About the Author: Dave Tyner is a 3DS Max 2013 Certified Professional working as a visualization artist for one of the worlds largest EPC firms. dave@davetyner.com @davetyner3D www.davetyner.com .

Subscribe

If you enjoyed this article, subscribe now to receive more just like it.

There is 1 Brilliant Comment

Trackback URL | Comments RSS Feed

Post a Comment

Your email address will not be published. Required fields are marked *

Top