Home > Excel > Visual Basic macro examples for working with arrays

Visual Basic macro examples for working with arrays

To Fill an Array and Then Copy It to a Worksheet

   Sub Sheet_Fill_Array()
     Dim myarray As Variant
     myarray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
     Range("a1:a10").Value = Application.Transpose(myarray)
   End Sub

To Take Values from a Worksheet and Fill the Array

Sub from_sheet_make_array()
   Dim thisarray As Variant
   thisarray = Range("a1:a10").Value

   counter = 1                'looping structure to look at array
   While counter <= UBound(thisarray)
      MsgBox thisarray(counter, 1)
      counter = counter + 1
   Wend
End Sub

To Pass and Receive an Array

Sub pass_array()
   Dim thisarray As Variant
   thisarray = Selection.Value
   receive_array (thisarray)
End Sub

Sub receive_array(thisarray)
   counter = 1
   While counter <= UBound(thisarray)
      MsgBox thisarray(counter, 1)
      counter = counter + 1
   Wend
End Sub

To Compare Two Arrays

Sub compare_two_array()
   Dim thisarray As Variant
   Dim thatarray As Variant

   thisarray = Range("range1").Value
   thatarray = Range("range2").Value
   counter = 1
   While counter <= UBound(thisarray)
      x = thisarray(counter, 1)
      y = thatarray(counter, 1)
      If x = y Then
         MsgBox "yes"
      Else MsgBox "no"
      End If
      counter = counter + 1
   Wend
End Sub

To Fill a Dynamic Array

Sub fill_array()

   Dim thisarray As Variant
   number_of_elements = 3     'number of elements in the array

   'must redim below to set size
   ReDim thisarray(1 To number_of_elements) As Integer
   'resizes this size of the array
   counter = 1
   fillmeup = 7
   For counter = 1 To number_of_elements
      thisarray(counter) = fillmeup
   Next counter

   counter = 1         'this loop shows what was filled in
   While counter <= UBound(thisarray)
      MsgBox thisarray(counter)
      counter = counter + 1
   Wend

End Sub
Categories: Excel Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: