Macro excel dalam bentuk kamus ini dibuat untuk memudahkan para macro-er (maaf rada maksa istilahnya :), pengganti programmer yang suka bikin macro). Berikut kumpulan sintaks macro :
Insya Allah akan diupdate berkala. Jika rekan-rekan ingin menambahkan silakan ditulis di bagian komentar.
Address – Mengetahui Posisi Cell di Excel
ADDRESS(baris, kolom, absolut type) ADDRESS(1,1) returns $A$1 ADDRESS(1,1,4) returns A1
Auto Open
Pilih di Macro ThisWorkbook, kemudian masukkan macro yang akan dijalankan misalnya msgbox,
tanggal hari ini now(), dll
Private Sub Workbook_Open()
Worksheets(“DR_HARI”).Select
DefaultTanggal = Range(“TGL_01”).Value
DefaultNow = Now()
‘MsgBox DefaultNow
DefaultPilihTGL = Format(DefaultNow, “dd”) – 1
DefaultPilihBLN = Format(DefaultNow, “mmm”)
DefaultPilihTHN = Format(DefaultNow, “yyyy”)
Range(“TGL_” & DefaultPilihTGL).Select
Worksheets(“DR_FORM”).Range(“BDR_DTGL”).Value = DefaultPilihTGL
Worksheets(“DR_FORM”).Range(“BDR_DBULAN”).Value = DefaultPilihBLN
Worksheets(“DR_FORM”).Range(“BDR_DTAHUN”).Value = DefaultPilihTHN
Worksheets(“DR_FORM”).Select
PosisiBaris = Range(“AG11”).End(xlDown).Row
Range(“AG” & PosisiBaris).Select
Worksheets(“DR_HARI”).Select
End Sub
Cells – Value
example #1
TextCaptureA = Range(“A” & DataRecordSource).Value
example #2
Range(“A1”).Select
example #3
ActiveCell.FormulaR1C1 = “key1”
example #4
Range(“A2”).Select
ActiveCell.FormulaR1C1 = “=RC[2]&RC[7]”
example #5
FileSource = Range(“_FormSource”).Value
FileTarget = Range(“_FormTarget”).Value
excel worksheet
=CELL("col", A1) Result: 1 =CELL("address", A2) Result: "$A$2" =CELL("format", A2) Result: "P2"
Cells – Copy Paste Area
Range(“E30:AQ32”).Select
Selection.Copy
Range(“E150:AQ152”).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Cells – Pilih Cells/Range
Range(“B9”).Select
Cells – Posisi/Potition
PosisiBaris = ActiveCell.row
PosisiKolom = ActiveCell.col
=CELL("address",INDEX(B5:D11,MATCH(G6,B5:B11,0),2))
Column – AutoFit
Columns(“A:A”).EntireColumn.AutoFit
Chart – Filter Selection
‘Memilih Filter Chart yang aktif ke satu nama
ActiveSheet.ChartObjects(“Chart 3”).Activate
ActiveSheet.PivotTables(-1).PivotFields(“Nama”).CurrentPage = “(All)”
With ActiveChart.PivotLayout.PivotTable.PivotFields(“Nama”)
.PivotItems(SheetNama).Visible = True
End With
‘Range(“AS44”).Select
Range(“AT37”).Select
Range(“AT37”).Value = SheetNama
Column – Insert 1 Column
Columns(“A:A”).Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Column – Unhide
Columns(“F:AH”).Select
Selection.EntireColumn.Hidden = False
Range(“C19”).Select
Column – Convert Column to Letter
SUBSTITUTE(ADDRESS(1,col_number,4),"1","")
Computer Name
Range(“AC” & PosisiPas).Value = Environ(“computername”)
Copy
MaxBaris = Range(“A2”).End(xlDown).Row
Range(“A2:H” & MaxBaris).Select
Selection.Copy
Copy – Paste Value
Range(“A105”).Select
Selection.Copy
Range(“B106”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Countif – From Defined Name
ABSEN_GetFP_Data = Range(“ABSEN_GetFP_Data”).Value
REPORT_tglmulai = Range(“REPORT_tglmulai”).Value
REPORT_tglselesai = Range(“REPORT_tglselesai”).Value
Range(“E11”).Formula = “=MATCH(REPORT_tglmulai,'” & ABSEN_GetFP_Data & “‘!D:D,0)”
Range(“E12”).Formula = “=MATCH(REPORT_tglselesai,'” & ABSEN_GetFP_Data & “‘!D:D,0)”
BARISTGL_start = Range(“E11”).Value
BARISTGL_end = Range(“E12”).Value
‘ActiveCell.FormulaR1C1 = “=COUNTIF(DATAAE!C[-2],””2017-01-01″”)”
Range(“F11”).Formula = “=COUNTIF(” & ABSEN_GetFP_Data & “!D:D,””” & REPORT_tglmulai & “””)”
Range(“F12”).Formula = “=COUNTIF(” & ABSEN_GetFP_Data & “!D:D,””” & REPORT_tglselesai & “””)”
Find
#1
Cells.Find(What:=NamaOrigi, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
#2
With Worksheets(1).Range(“a1:a500”)
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then firstAddress = c.Address
Do c.Value = 5 Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
File & Folder – Macro Open Folder
Sub OpenFolder()
Dim MyFolder As String
MyFolder = “C:\PATH\”
ActiveWorkbook.FollowHyperlink MyFolder
End Sub
ChDir “D:\DATA.Office\DATA.Macro”
File & Folder – Open File
On Error Resume Next
Dim excelFile As String
excelFile = “test1.xlsm”
‘open excel file “myfile.xls” in drive D:\
Workbooks.Open “c:\users\test\” & excelFile
File & Folder – Path File Information
‘Baca Folder dengan file yang aktif
BacaPathFile = ThisWorkbook.FullName
‘Baca Folder Aktif Sheet
BacaPathDir1 = ThisWorkbook.Path
Filter – Unselect Data
Selection.AutoFilter
ActiveWindow.SmallScroll Down:=-3
Font – Bold and Colour (red)
Range(“I” & sStart2 & “:I” & rStart).Select
Selection.Font.Bold = True
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
Format – General
Selection.NumberFormat = “General”
Format – Tanggal
(1) Selection.NumberFormat = “yyyy.mm.dd”
(2) If different with month and date, please using this script
ActiveCell.NumberFormat = "@"
ActiveCell.Value = Format(Now(), "dd-mmm-yy")
(3) Tanggal Selisih
TanggalSelisih = DateDiff("d", Range("Form_TanggalMulai").Value, Range("Form_TanggalSampai").Value)
TanggalSelisih = TanggalSelisih + 1
MsgBox TanggalSelisih
Hapus Content
Sheets(“FORM”).Range(“B15:I100”).Select
Selection.ClearContents
If Then Else – Exit
If .. Then
Exit Sub
End If
Index – Match
Sheets(“DATA”).Range(“S” & FORM_rows).Formula = “=Index(‘KARY’!A:C,MATCH(A” & FORM_rows & “, ‘KARY’!A:A,0),3)”
Sheets(ABSEN_GetFP_Data).Range(“J” & BARIS_PROSES).Formula = “=Index(‘KALENDER’!A:F,MATCH(TEXT(D” & BARIS_PROSES & “,””yyyy-mm-dd””)&””LIBUR”” , ‘KALENDER’!F:F,0),5)”
Selection.Copy
ActiveSheet.Paste
Application.CutCopyMode = False
Index – Match Problem
=TEXT(G451,”0″)
Indirect
=indirect($A$1&”!”&cell(“
SUMIF(INDIRECT(“‘” & C5 & “‘!E”&E5&”:J” & E6;TRUE);LEFT(D6;4)&X16;INDIRECT(“‘” & C5 & “‘!J”&E5&”:J” & E6;TRUE))
=MATCH(AI3;INDIRECT(“‘[“&AJ$1&”]”&AJ$2&”‘!”&AK$2;TRUE);0)
InputBox
Range(“B5”).Value = InputBox(“Bulan Yang Akan Diproses”, “SETTING BULAN”, “2018-08-“)
Message Box
MsgBox (Filename & ” has been created in” & Source_Folder)
Message Box – Baris Baru
MsgBox “Absen Karyawan: ” & vbCrLf & Range(“Form_Nama”).Value & “BERHASIL di SUBMIT”
Paste
Range(“A2”).Select
ActiveSheet.Paste
Pivot – Pilih Filter
Application.ScreenUpdating = False
ActiveSheet.Range(“D68”) = SheetNama
Application.ScreenUpdating = True
Process – Show Process
‘#show process
Application.ScreenUpdating = False
Range – Delete
Range(“A2:Y37”).Delete
Range Name
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.AddressLocal = “$AH$512” Then
NamaPilihan = “DR_” & Range(“AI508”).Value
Range(“AI512”).Value = “Pilih Kategori Pekerjaan”
‘xlBetween, Formula1:=”=” & NamaPilihan & “”
Range(“AI512″).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=”=” & NamaPilihan
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = “”
.ErrorTitle = “”
.InputMessage = “”
.ErrorMessage = “”
.ShowInput = True
.ShowError = True
End With
End If
End Sub
Rounddown (Pembulatan)
ROUNDDOWN(B3/3;0)
Rows – Pilih Baris
Rows(“2:2”).Select
Selection.Delete Shift:=xlUp
Rows – Pilih Baris, Kemudian Hapus
Rows(“2:2”).Select
Selection.Delete Shift:=xlUp
Rows – Baris Terakhir (Max)
Range(“B1”).Select
Range(Selection, Selection.End(xlDown)).Select
Range(“B100”).Select
Range(Selection, Selection.End(xlUp)).Select
maximum_lines = Worksheets(“TEMP”).Range(“A4”).End(xlDown).Row
Ref: Baris Terakhir Macro
Save – Close File (Without) Save
ActiveWindow.Close
ActiveWorkbook.Close savechanges:=False
Sub Close_No_Save()
Application.DisplayAlerts = False
ThisWorkbook.Close
Application.DisplayAlerts = True
End Sub
Close with save
Sub Close_With_Save()
ThisWorkbook.Close True
End Sub
Save
Sub Save_Me()
ThisWorkbook.Save
End Sub
Select Case
If Len(Kehadiran_Initial) <= 2 Then
Select Case Kehadiran_Initial
Case “S”
Kehadiran_Code = “Sakit”
Case “I”
Kehadiran_Code = “Izin”
Case “IK”
Kehadiran_Code = “Kontrak Habis”
Case “SB”
Kehadiran_Code = “Stand By – Reguler”
End Select
Else
End If
Selection – Range
Range(Selection, Selection.SpecialCells(xlLastCell)).Select
Sheets – Pilih Sheet
Sheets(“NamaSheet”).Select
Workbooks(“NAMA_SHEET”).Activate
Worksheets(“NAMA_SHEET”).Activate
Worksheets(“NAMA_SHEET”).Select
Sheets – Copy Sheet
Sheets(“NamaSheet”).Copy
Sheets(“Azis”).Select
Sheets(“Azis”).Copy Before:=Sheets(21)
Sheets – Name & Count
pName = ActiveWorkbook.Path ' the path of the currently active file
wbName = ActiveWorkbook.Name ' the file name of the currently active file
shtName = ActiveSheet.Name ' the name of the currently selected worksheet
countName = ActiveSheet.Count ' Count the sheet
Status Bar
For FP_rows = 6 To FP_rowsMax Percent = FP_rows / FP_rowsMax * 100 Status = "Generate Process.. " & Percent & "% " 'For i = 0 To Percent Status = Status & "|" 'Next Application.StatusBar = Status
Windows – Pilih File
Windows(“Nama_File.XLS”).Activate
Semoga kamus macro excel ini bermanfaat bagi siapapun.
Error Case
http://www.dlldownloader.com/msdbrptr-dll/
Incoming search terms:
- kamus macro vba excel