Conto Excel Macros anu ditulis nganggo VBA

Conto makro Excel basajan di handap ieu ditulis nganggo VBA 

Perkiraan waktos maca: 3 menit

conto VBA maké Array

Prosedur Sub di handap ieu maca nilai tina sél dina kolom A lembar kerja aktip, dugi ka mendakan sél kosong. Nilai-nilai disimpen dina susunan. Conto makro Excel saderhana ieu ngagambarkeun panggunaan:

• Deklarasi variabel;
• Asép Sunandar Sunarya dinamis;
• Hiji siklus Do Until;
• Tingal sél dina lembar kerja Excel ayeuna;
• Fungsi VBA Ubound builtin (anu mulihkeun indéks pangluhurna hiji Asép Sunandar Sunarya).

' Sub procedure store values in Column A of the active Worksheet
' into an array
Sub GetCellValues()
Dim iRow As Integer            ' stores the current row number
Dim dCellValues() As Double  ' array to store the cell values
iRow = 1
ReDim dCellValues(1 To 10)
' Do Until loop to extract the value of each cell in column A
' of the active Worksheet, as long as the cell is not blank
Do Until IsEmpty(Cells(iRow, 1))
   ' Check that the dCellValues array is big enough
   ' If not, use ReDim to increase the size of the array by 10
   If UBound(dCellValues) < iRow Then
      ReDim Preserve dCellValues(1 To iRow + 9)
   End If
   ' Store the current cell in the CellValues array
   dCellValues(iRow) = Cells(iRow, 1).Value
   iRow = iRow + 1
Loop
End Sub

Prosedur nyimpen nilai-nilai dina kolom A tina lembar kerja aktip dina array, dicatet yén:

• Siklusna Do Until nimba nilai unggal sél dina kolom A tina LKS aktip, malire sél kosong
• Kaayaan"If UBound(dCellValues) < iRow"Cék yén array dCellValues ​​cukup ageung pikeun nahan inpormasi, upami henteu, anggo ReDim pikeun ningkatkeun ukuran array ku 10
• Tungtungna, atikan​​dCellValues(iRow) = Cells(iRow, 1).Value” Nyimpen sél ayeuna dina array CellValues

conto VBA kalawan operasi matematik

Prosedur Sub di handap ieu maca nilai tina kolom A tina lembar kerja anu dingaranan "Sheet2" sareng ngalaksanakeun operasi aritmetika dina nilai. Nilai anu dihasilkeun dicitak dina kolom A tina lembar kerja aktip ayeuna.

Makro ieu ngagambarkeun:

• Deklarasi variabel;
• Objék Excel (husus, pamakean kecap konci Atur sareng cara ngaksés obyék 'Kolom' tina objék 'Lambar');
• Hiji siklus Do Until;
• Aksés lembar kerja sareng rentang sél dina buku kerja Excel ayeuna.

' Sub procedure to loop through the values in Column A of the Worksheet
' "Sheet2", perform arithmetic operations on each value, and write the
' result into Column A of the current Active Worksheet ("Sheet1")
Sub Transfer_ColA()
Dim i As Integer
Dim Col As Range
Dim dVal As Double
' Set the variable 'Col' to be Column A of Sheet 2
Set Col = Sheets("Sheet2").Columns("A")
i = 1
' Loop through each cell of the column 'Col' until
' a blank cell is encountered
Do Until IsEmpty(Col.Cells(i))
   ' Apply arithmetic operations to the value of the current cell
   dVal = Col.Cells(i).Value * 2 + 1
   ' The command below copies the result into Column A
   ' of the current Active Worksheet - no need to specify
   ' the Worksheet name as it is the active Worksheet.
   Cells(i, 1) = dVal
   i = i + 1
Loop
End Sub

conto VBA kalawan rékaman tanggal modifikasi

Hayu urang nyerat makro VBA saderhana anu hurung nalika sél dina sauntuyan spésifik dina lambaran urang diropéa. Anggap anjeun hoyong ngalacak parobahan dina kolom B (B4 dugi ka B11) sareng ngarékam tanggal sareng waktos parobahan dina kolom A.
Hayu urang teraskeun sapertos kieu:

• Dina tab Developer klik pilihan "Visual Basic” pikeun muka redaktur VBA.
• Dina redaktur VBA, klik dua kali redaktur kode anu aya hubunganana sareng Sheet2.
• Pilih Lembar Kerja tina tab katuhu (atawa kénca) tur pilih pilihan Robah.
• Tambahkeun kode VBA:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B1:B10")) Is Nothing Then
        Target.Range("A1:A1").Value = Now
    End If
End Sub

Simpen buku kerja sareng makro diaktipkeun (contona, salaku file .xlsm).

Ayeuna, unggal waktos urang ngamutahirkeun sél dina kolom B (tina baris 1 nepi ka baris 10), sél dina kolom A bakal otomatis mintonkeun tanggal jeung waktu ayeuna.

Ercole Palmeri