Piv txwv ntawm Excel Macros sau nrog VBA

Cov piv txwv yooj yooj yim Excel macro hauv qab no tau sau siv VBA 

Lub sijhawm nyeem ntawv kwv yees: 3 minuti

VBA piv txwv siv Array

Cov txheej txheem Sub hauv qab no nyeem cov txiaj ntsig ntawm cov hlwb hauv kab A ntawm daim ntawv ua haujlwm nquag, kom txog rau thaum nws ntsib lub xov tooj dawb. Cov nqi khaws cia hauv ib qho array. Qhov yooj yim Excel macro piv txwv qhia txog kev siv:

• Cov lus tshaj tawm sib txawv;
• Dynamic arrays;
• Ib lub voj voog Do Until;
• Xa mus rau cov hlwb hauv daim ntawv ua haujlwm Excel tam sim no;
• VBA muaj nuj nqi Ubound builtin (uas rov qab qhov ntsuas siab tshaj plaws ntawm ib qho array).

' 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

Cov txheej txheem khaws cov txiaj ntsig hauv kab A ntawm daim ntawv ua haujlwm nquag hauv ib qho array, nco ntsoov tias:

• Lub voj voog Do Until rho tawm cov txiaj ntsig ntawm txhua lub xov tooj hauv kab A ntawm daim ntawv ua haujlwm nquag, tsis quav ntsej cov hlwb
• Qhov xwm txheej "If UBound(dCellValues) < iRow” kuaj xyuas tias dCellValues ​​array loj txaus los tuav cov ntaub ntawv, yog tias tsis yog, siv ReDim kom nce qhov loj ntawm array los ntawm 10
• Thaum kawg, kev kawm​​dCellValues(iRow) = Cells(iRow, 1).Value” Khaws cov xovtooj tam sim no hauv CellValues ​​array

VBA piv txwv nrog kev ua lej

Cov txheej txheem Sub hauv qab no nyeem cov txiaj ntsig ntawm kab A ntawm daim ntawv ua haujlwm hu ua "Sheet2" thiab ua cov lej lej ntawm cov nqi. Cov txiaj ntsig tau raug luam tawm hauv kab A ntawm daim ntawv ua haujlwm tam sim no.

Qhov no macro qhia txog:

• Cov lus tshaj tawm sib txawv;
• Cov khoom Excel (tshwj xeeb, siv cov lus tseem ceeb thiab nkag mus rau 'Kem' cov khoom los ntawm 'Cov Ntawv' cov khoom);
• Ib lub voj voog Do Until;
• Nkag mus rau cov ntawv ua haujlwm thiab cov xovtooj ntawm tes hauv phau ntawv ua haujlwm Excel tam sim no.

' 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

VBA piv txwv nrog hloov kho hnub kaw

Cia peb sau ib qho yooj yim VBA macro uas tua hluav taws thaum lub xov tooj ntawm tes hauv ib qho tshwj xeeb ntawm peb daim ntawv hloov tshiab. Piv txwv tias koj xav taug qab cov kev hloov pauv hauv kab B (B4 txog B11) thiab sau hnub thiab sijhawm ntawm kev hloov pauv hauv kab A.
Cia peb ua li no:

• Nyob hauv tab Developer nyem rau ntawm qhov kev xaiv "Visual Basic"los qhib VBA editor.
• Hauv VBA editor, muab ob npaug rau-nias tus lej editor ntsig txog Sheet2.
• Xaiv Daim Ntawv Ua Haujlwm los ntawm sab xis (lossis sab laug) tab thiab xaiv qhov hloov pauv.
• Ntxiv VBA code:

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

Txuag phau ntawv ua haujlwm nrog macros enabled (piv txwv li, ua cov ntaub ntawv .xlsm).

Tam sim no, txhua zaus peb hloov kho lub xov tooj ntawm kab ntawv B (los ntawm kab 1 txog kab 10), lub xov tooj ntawm tes hauv kab A yuav cia li tso saib hnub thiab sijhawm tam sim no.

Ercole Palmeri