VBA bilan yozilgan Excel makrolariga misollar

Quyidagi oddiy Excel makro misollari VBA yordamida yozilgan 

Taxminiy o'qish vaqti: 3 daqiqada

Array yordamida VBA misoli

Quyidagi quyi protsedura faol ish varag'ining A ustunidagi hujayralardagi qiymatlarni bo'sh katakka duch kelmaguncha o'qiydi. Qiymatlar massivda saqlanadi. Ushbu oddiy Excel makros misoli quyidagilardan foydalanishni ko'rsatadi:

• O'zgaruvchan deklaratsiyalar;
• Dinamik massivlar;
• Tsikl Do Until;
• Joriy Excel ish varag'idagi hujayralarga murojaat qiling;
• VBA funktsiyasi Ubound qurilgan (massivning eng yuqori indeksini qaytaradi).

' 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

Protsedura faol ish varag'ining A ustunidagi qiymatlarni massivda saqlaydi, shuni yodda tuting:

• Tsikl Do Until bo'sh kataklarni e'tiborsiz qoldirib, faol ish varaqining A ustunidagi har bir katakning qiymatlarini chiqaradi
• shart"If UBound(dCellValues) < iRowdCellValues ​​massivi ma'lumotni saqlash uchun etarlicha katta ekanligini tekshiradi, agar bo'lmasa, massiv hajmini 10 ga oshirish uchun ReDim-dan foydalaning.
• Nihoyat, ta'lim​​dCellValues(iRow) = Cells(iRow, 1).Value” Joriy katakchani CellValues ​​massivida saqlaydi

Matematik operatsiyalar bilan VBA misoli

Quyidagi quyi protsedura "Varaq2" deb nomlangan ishchi varaqning A ustunidagi qiymatlarni o'qiydi va qiymatlar ustida arifmetik amallarni bajaradi. Olingan qiymatlar joriy faol ish varag'ining A ustunida chop etiladi.

Ushbu makro tasvirlaydi:

• O'zgaruvchan deklaratsiyalar;
• Excel ob'ektlari (aniqrog'i, Set kalit so'zidan foydalanish va "Shaxslar" ob'ektidan "Ustunlar" ob'ektiga qanday kirish mumkin);
• Tsikl Do Until;
• Joriy Excel ish kitobidagi ish varaqlari va hujayra diapazonlariga kirish.

' 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

O'zgartirish sanasi bilan VBA misoli

Keling, varaqimizning ma'lum bir diapazonidagi hujayra yangilanganda ishga tushadigan oddiy VBA makrosini yozaylik. Aytaylik, siz B ustunidagi (B4 dan B11 gacha) o'zgarishlarni kuzatmoqchisiz va A ustunidagi o'zgarish sanasi va vaqtini yozmoqchisiz.
Keling, shunday davom etaylik:

• Yorliqda Developer variantni bosing "Visual Basic” VBA muharririni ochish uchun.
• VBA muharririda Sheet2 bilan bog'liq kod muharririni ikki marta bosing.
• O'ng (yoki chap) yorlig'idan Ish varag'ini tanlang va O'zgartirish opsiyasini tanlang.
• VBA kodini qo'shing:

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

Ish kitobini makroslar yoqilgan holda saqlang (masalan, .xlsm fayli sifatida).

Endi biz har safar B ustunidagi katakchani yangilaganimizda (1-qatordan 10-qatorgacha), A ustunidagi katak avtomatik ravishda joriy sana va vaqtni koʻrsatadi.

Ercole Palmeri