Ìwé

Awọn apẹẹrẹ ti Excel Macros ti a kọ pẹlu VBA

Awọn apẹẹrẹ macro Excel ti o rọrun wọnyi ni a kọ nipa lilo VBA

Atọka akoonu

Iye akoko kika: 3 iṣẹju

Apeere VBA nipa lilo Array

Ilana Sub atẹle yii ka awọn iye lati awọn sẹẹli ni iwe A ti iwe iṣẹ ti nṣiṣe lọwọ, titi yoo fi pade sẹẹli ofo kan. Awọn iye ti wa ni fipamọ ni ohun orun. Apeere Makiro Excel ti o rọrun yii ṣe afihan lilo ti:

Awọn ikede iyipada;
Awọn ila ti o ni agbara;
Yiyipo kan Do Until;
Tọkasi awọn sẹẹli ninu iwe iṣẹ iṣẹ Excel lọwọlọwọ;
Awọn iṣẹ VBA Ubound builtin (eyi ti o pada ga atọka ti ohun orun).

' 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

Ilana naa tọju awọn iye ni iwe A ti iwe iṣẹ ti nṣiṣe lọwọ ni ọna kan, ṣe akiyesi pe:

Yiyipo Do Until yọkuro awọn iye ti sẹẹli kọọkan ni iwe A ti iwe iṣẹ ti nṣiṣe lọwọ, kọju awọn sẹẹli ofo
Ipo naa"If UBound(dCellValues) < iRow"Ṣayẹwo pe dCellValues ti o tobi to lati di alaye naa mu, ti kii ba ṣe bẹ, lo ReDim lati mu iwọn titobi pọ si nipasẹ 10
Níkẹyìn, ẹkọdCellValues(iRow) = Cells(iRow, 1).Value"Ṣipamọ sẹẹli lọwọlọwọ ni titobi CellValues

VBA apẹẹrẹ pẹlu mathematiki mosi

Ilana Sub atẹle yii ka awọn iye lati iwe A ti iwe iṣẹ ti a npè ni “Sheet2” ati ṣe awọn iṣẹ ṣiṣe iṣiro lori awọn iye. Awọn iye abajade ti wa ni titẹ ni iwe A ti iwe iṣẹ ṣiṣe lọwọlọwọ.

Makiro yii ṣapejuwe:

Awọn ikede iyipada;
Awọn ohun ti o tayọ (ni pato, lilo ti Koko Ṣeto ati bi o ṣe le wọle si nkan 'Awọn ọwọn' lati nkan 'Sheets');
Yiyipo kan Do Until;
Wọle si awọn iwe iṣẹ ati awọn sakani sẹẹli ninu iwe iṣẹ Excel lọwọlọwọ.

' 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

Apeere VBA pẹlu gbigbasilẹ ọjọ iyipada

Jẹ ki a kọ Makiro VBA ti o rọrun ti o njo nigbati sẹẹli kan ni iwọn kan pato ti dì wa ti ni imudojuiwọn. Ṣebi o fẹ lati tọpa awọn ayipada ninu iwe B (B4 si B11) ati ṣe igbasilẹ ọjọ ati akoko iyipada ni iwe A.
Jẹ ki a tẹsiwaju bi eleyi:

Ninu taabu Developer tẹ lori aṣayan "Visual Basic”lati ṣii olootu VBA.
Ninu olootu VBA, tẹ lẹẹmeji olootu koodu ti o ni ibatan si Sheet2.
Yan Iwe iṣẹ lati apa ọtun (tabi osi) taabu ki o yan aṣayan Yipada.
Ṣafikun koodu 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

Ṣafipamọ iwe iṣẹ pẹlu awọn macros ṣiṣẹ (fun apẹẹrẹ, bi faili .xlsm kan).

Bayi, ni gbogbo igba ti a ba ṣe imudojuiwọn sẹẹli kan ni iwe B (lati ila 1 si laini 10), sẹẹli ti o wa ninu iwe A yoo ṣe afihan ọjọ ati akoko lọwọlọwọ laifọwọyi.

Ercole Palmeri