belajar excel makro
description
Transcript of belajar excel makro
Blog XQhttp://excelquotient.wordpress.com
Visual Basic for Visual Basic for Application Microsoft Application Microsoft
Office ExcelOffice Excel
2
How to Learn Any Software EasierHow to Learn Any Software Easier
Jangan fanatik pada software tertentu dan membenci software lain
If I think I can. Yes I can. Cari manfaat mempelajari software tsb dan jadikan motivasi Berkorban untuk membeli buku panduan atau mengunduh
panduan/software dari internet Berdo’a
Blog XQ (http://excelquotient.wordpress.com)
3
AgendaAgenda
VBA Excel Overview Integrated Development Environment Record Macro Procedure & Function Range/Cells Worksheets/Sheets Workbooks/Books Application Charts/Chart Conditional & Looping Syntax Visual Basic Controls
Blog XQ (http://excelquotient.wordpress.com)
4
VBA Excel VBA Excel Overview Overview
Mengapa harus VBA Excel? Pekerjaan sama dan berulang-ulang Data banyak Praktis & cepat
Dari mana saya bisa belajar VBA Excel? Help? Buku Internet seperti dari Blog XQ (http://excelquotient.wordpress.com) atau
mailing list [email protected]. Training/Workshop
Persiapan apa untuk belajar VBA Excel? Programming Logic Mampu menggunakan Excel untuk entri data sederhana Mampu menggunakan formula di Excel Mengetahui shortcuts keyboard untuk perpindahan cell dalam worksheets
Blog XQ (http://excelquotient.wordpress.com)
5
Integrated Development EnvironmentIntegrated Development Environment
Macro Running Permission: Tools>Macro>Security
Record Macro: Tools>Macro>Record New
Macro… Visual Basic Editor:
Tools>Macro>Visual Basic Editor [Alt+F11] Right click on sheet name then
select View Code Running Macro:
Tools>Macro>Macro… [Alt+F8]
Blog XQ (http://excelquotient.wordpress.com)
6
Integrated Development EnvironmentIntegrated Development EnvironmentBlog XQ (http://excelquotient.wordpress.com)
7
Record MacroRecord Macro
Record Macro Jika lupa Jika ingin tahu kode sumber dari apa yang
dikerjakan dengan GUI Excel Caranya:
Bikin New File Excel Tools>Macro>Record New Macro Beri nama macro Buka VB Editor Buka Modul berisi procedure macro tadi Perhatikan terjadi penambahan baris per baris
macro pada VB Editor ketika kita menjalankan perintah pada GUI Excel.
Blog XQ (http://excelquotient.wordpress.com)
8
Provedure & FunctionProvedure & Function
Procedure & Function Semua kode program ditulis dalam procedure atau
function Procedure tidak mengembalikan nilai Function dapat mengembalikan nilai
Declaration Variable yg dideklarasikan dalam procedure atau function
hanya berlaku secara lokal (pada prosedure atau function tsb).
Variable global berlaku secara global di simpan di baris paling atas.
Contoh: Public nama as string
Blog XQ (http://excelquotient.wordpress.com)
9
Provedure & FunctionProvedure & Function
Contoh:Public nama As String
Function kurangkan(angka1 As Integer, angka2 As Integer) Dim hasil As Integer kurangkan = angka2 - angka1End Function
Sub tambahdankurang() Dim nilai1 As Integer Dim nilai2 As Integer Dim nilai3 As Integer Dim nilai4 As Integer
nilai1 = 1982 nilai2 = 9128 nilai3 = nilai1 + nilai2 nilai4 = kurangkan(nilai1, nilai2)
nama = "F-Xtudent" namanyaEnd Sub
Sub namanya() namamu = namaEnd Sub
Blog XQ (http://excelquotient.wordpress.com)
10
Range/CellsRange/Cells
Range/Cells
Storing Value: Range(“A1”).Value=“Statistika” Range(“A2”).Value=12
Assign Range Value: Sel=Activecell.Value
Assign Range Address: SelName=Range(“E4”).Address SelName=Range(“A1:E4”).Address
Activate Range: Range(“A1”).Select Range(“A1:D5”).Select Range(Sel,SelName).Select
Copy Range to: Range(“A1:A3”).Copy Range(“B1”)
Paste Range: SelectionPasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False
Clear Range: Range(“A1:A3”).Clear
Activate Range rth,cth: Activecell.Offset(r,c).Select
Insert Column: Selection.EntireRow.Insert
Assign Absolute Formula: Range(Sel).Formula=“=A2+A3”
Assign Relative Formula: Range(Sel).FormulaR1C1=“=R[1]C+R[2]C”
Font Manipulation:Activecell.Font.
Name; SizeColorIndex=(0-56);Color=RGB(0-255, 0-255, 0-255)Bold; Italic; Underline (Boolean[True/False])
Etc…
Blog XQ (http://excelquotient.wordpress.com)
11
Worksheet/SheetsWorksheet/Sheets
Worksheets/Sheets
Add New Worksheet: Worksheets.Add After:=ActiveSheet
Assign Sheet Name: Worksheets(“Sheet1”).Name=“Grafik”
Delete Sheet: Sheets(“Grafik”).Delete
Activate Sheet: WorkSheets(“Grafik”).Activate
Move Sheet: ActiveSheet.Move Before:=Sheets(“Grafik”)
Etc…
Blog XQ (http://excelquotient.wordpress.com)
12
Workbooks/BooksWorkbooks/Books
Workbooks/Books
Add New Workbook: Set Newbook=Workbooks.Add Newbook.Name=“myData”
Open Workbook: Workbooks.Open “C:\Data.xls”
Activate Workbook: Worknooks(“Data”).Activate
Save Workbook: ActiveWorkbook.Save ActiveWorkbook.SaveCopyAs Filename:=“C:\Data2.xls”
Close Workbook: ActiveWorkbook.Close
Get Location of Workbook: Lokasi=ActiveWorkbook.Path
Accessing Range on Specific Workbook and Worksheet Nilai=Workbooks(“Data”).Worksheets(“Grafik”).Range(“A1:D5”).Value
Etc…
Blog XQ (http://excelquotient.wordpress.com)
13
ApplicationApplication
Application
Turn off Screen Updating: Application.ScreenUpdating=False
Turn off Display Alert: Application.DisplayAlerts=False
Suspending Execution: Application.Wait(Now+TimeValue(“00:00:01”))
Statust Bar: Application.Statusbar=“Running”
Etc…
Blog XQ (http://excelquotient.wordpress.com)
14
Charts/ChartCharts/Chart
Charts/Chart BarChart
Delete Chart: Activesheet.Charts(“Chart1”).Delete
The other types of chart could be learned use Record Macro command
…Charts.AddActiveChart.ChartType = xlColumnClusteredActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B5"), PlotBy:=_ xlColumnsActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"Application.CommandBars("Chart").Visible = False…
0
100
200
300
400
500
600
Excess Demand Seimbang Tekanan Jual
Frekuensi Kondisi Pasar
Blog XQ (http://excelquotient.wordpress.com)
15
If …<condition>… then …<program>…Else If …<condition>… then …<program>…Else…<program>…EndIf
Select Case …<variable>…Case …<value>……<program>…Case …<value>……<program>…End Select
• Do while …<condition>……<program>…
Loop
• Do until …<condition>……<program>…
Loop
• For …<condition>……<program>…
Next
Conditional & Looping SyntaxConditional & Looping SyntaxBlog XQ (http://excelquotient.wordpress.com)
16
If ActiveCell.Value <= 5 Then Status = "baby" ElseIf ActiveCell.Value <= 10 Then Status = "child" ElseIf ActiveCell.Value <= 20 Then Status = "teen" Else Status = "old" End If
Select Case ActiveCell.Value Case 1 letter = "satu" Case 2 letter = "dua" Case 3 letter = "tiga" End Select
Conditional & Looping SyntaxConditional & Looping Syntax
m = 0 Do Until ActiveCell.Value = "" ActiveCell.Value = m ActiveCell.Offset(1, 0).Select m = m + 1 Loop
For m = 1 To 10 ActiveCell.Value = m ActiveCell.Offset(1, 0).Select m = m + 1 Next m
Blog XQ (http://excelquotient.wordpress.com)
17
Visual Basic ControlsVisual Basic Controls
Memunculkan Toolbar Visual Basic Klik kanan pada menu apa saja lalu pilih Visual Basic Atau klik menu Tools>Costumize Pada tab Toolbar, centang Visual Basic lalu Close
Blog XQ (http://excelquotient.wordpress.com)
18
Visual Basic ControlsVisual Basic Controls
Sheet Main
VB Controls
Blog XQ (http://excelquotient.wordpress.com)
19
Visual Basic ControlsVisual Basic Controls
Sheet Data
VB Controls
Blog XQ (http://excelquotient.wordpress.com)
20
Visual Basic ControlsVisual Basic Controls
Sheet Support
Blog XQ (http://excelquotient.wordpress.com)
21
Visual Basic ControlsVisual Basic ControlsBlog XQ (http://excelquotient.wordpress.com)
22
Visual Basic ControlsVisual Basic Controls
=IF(Support!B2=TRUE,"Laki-laki",IF(Support!B2=FALSE,"Perempuan",""))
Blog XQ (http://excelquotient.wordpress.com)
23
Visual Basic ControlsVisual Basic ControlsBlog XQ (http://excelquotient.wordpress.com)
24
Visual Basic ControlsVisual Basic Controls
Insert > Name > Define… >
Blog XQ (http://excelquotient.wordpress.com)
25
Visual Basic ControlsVisual Basic ControlsBlog XQ (http://excelquotient.wordpress.com)
26
Visual Basic ControlsVisual Basic ControlsBlog XQ (http://excelquotient.wordpress.com)
27
Visual Basic ControlsVisual Basic Controls
Program Code Sheets (Main)
Private Sub record_Click() simpanEnd Sub
Private Sub vData_Click() Sheets("Data").ActivateEnd Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("D8").SelectEnd Sub
Sheets (Data)Private Sub main_Click() Sheets("Main").ActivateEnd Sub
ThisWorkbookPrivate Sub Workbook_Open()
Application.ScreenUpdating = False Application.DisplayAlerts = False Sheets("Main").Activate
Range("D8").Value = "" Sheets("Support").Range("B2").Value = "N.A." Range("D10").Formula = "=IF(Support!B2=TRUE,""Laki-laki"",IF(Support!B2=FALSE,""Perempuan"",""""))" Range("D12").Value = "" Range("D14").Value = "" Range("D8").Select Application.ScreenUpdating = True Application.DisplayAlerts = TrueEnd Sub
Blog XQ (http://excelquotient.wordpress.com)
28
Visual Basic ControlsVisual Basic Controls
Sub simpan() Application.ScreenUpdating = False Application.DisplayAlerts = False
'DECLARATION Dim cID As String Dim cJK As String Dim cPPD As String Dim cTK As String Dim m As Integer 'ASSIGN VARIABLE cID = Range("D8").Value cJK = Range("D10").Value cPPD = Range("D12").Value cTK = Range("D14").Value 'VALIDATION EMPTY CELL If cID = "" Or cJK = "" Or cPPD = "" Or cTK = "" Then GoTo emptyform End If 'VALIDATION & PASTE VARIABLES ''Activate Cell A1 at Sheet Data Sheets("Data").Activate Range("B3").Select ''Search empty cell Do Until ActiveCell.Value = "" ''Assign record number m = m + 1 ActiveCell.Offset(0, -1).Value = m ''Validation duplicate redord If ActiveCell.Value = cID Then GoTo duplicate End If ''Check is Empty ActiveCell.Offset(1, 0).Select Loop
‘’Fill the record ActiveCell.Offset(0, -1).Value = m + 1 ActiveCell.Value = cID ActiveCell.Offset(0, 1).Value = cJK ActiveCell.Offset(0, 2).Value = cPPD ActiveCell.Offset(0, 3).Value = cTK 'RESET THE CONDITION Sheets("Main").Activate Range("D8").Value = "" Sheets("Support").Range("B2").Value = "N.A." Range("D10").Formula = "=IF(Support!B2=TRUE,""Laki-
laki"",IF(Support!B2=FALSE,""Perempuan"",""""))" Range("D12").Value = "" Range("D14").Value = "" Range("D8").Select 'JUMP TO THE END OF PROGRAM GoTo lastline 'GOTO LINE AS ERROR HANDLINGemptyform: MsgBox "Fill the form correctly, please!" GoTo lastlineduplicate: MsgBox "Can't perform duplicate ID Responden." Sheets("Main").Activatelastline: Application.ScreenUpdating = True Application.DisplayAlerts = TrueEnd Sub
Module
Blog XQ (http://excelquotient.wordpress.com)
29
Visual Basic ControlsVisual Basic Controls
Finnishing Touch Tools > Options… Lock cell Data > Validation…
Blog XQ (http://excelquotient.wordpress.com)
30
Terima KasihThank You
Arigatou
Jazaakumullah
Hatur Nuhun
Blog XQhttp://excelquotient.wordpress.com