Menu

Kamus Macro Excel

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

different month and date

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

Ref : Match

=TEXT(G451,”0″)

Indirect

=indirect($A$1&”!”&cell(“address”,A1))

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

Leave a Reply

howto-123.com 2020 . Powered by WordPress