Primeri makrov v programu Visual Basic za delo s polji

Če želite zapolniti polje in ga nato kopirati na delovni list

1. Odprite nov delovni zvezek in vstavite list modula Visual Basic.

2. Na list modula vnesite to kodo.

   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
   

3. Izberite List1.

4. V meniju Orodja pokažite na Makro in nato kliknite Makri.

5. V pogovornem oknu Makro kliknite Sheet_Fill_Array in nato Zaženi.

Prevzem vrednosti z delovnega lista in zapolnjenje matrike

1. Vnesite vrednosti na List1 v celice A1:A10.

2. Na list modula Visual Basic vnesite to kodo:

   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
   

3. Izberite List1.

4. V meniju Orodja pokažite na Makro in nato kliknite Makri.

5. V pogovornem oknu Makro kliknite from_sheet_make_array in nato Zaženi.

Podajanje in prejemanje polja

1. Na list modula vnesite to kodo:

   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
   

2. Izberite List1 in označite obseg A1:A10.

3. V meniju Orodja pokažite na Makro in nato kliknite Makri.

4. V pogovornem oknu Makro kliknite pass_array in nato Zaženi.

Primerjava dveh polj

1. Ustvarite dva poimenovana obsega na Listu1. Poimenujte en obseg1 in drugi obseg2.
   
   Označite na primer obseg celic A1:A10 in ga poimenujte obseg1; označite obseg celic B1:B10 in ga poimenujte obseg2.

2. Na list modula vnesite to kodo.

   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
   

3. Izberite List2.

4. V meniju Orodja pokažite na Makro in nato kliknite Makro.

5. V pogovornem oknu Makro kliknite Compare_two_array in nato Zaženi.
   
   Za vsako primerjavo boste videli eno polje s sporočilom.

Zapolnitev dinamičnega polja

1. Na list modula vnesite to kodo:

   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
   

2. V meniju Orodja pokažite na Makro in nato kliknite Makri.

3. V pogovornem oknu Makro kliknite fill_array in nato Zaženi.

OPOMBA: Če spremenite spremenljivko »number_of_elements«, določite velikost matrike.