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


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


http://excelquotient.wordpress.com/

mailto:[email protected]

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]


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.


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


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


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…


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…


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…


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…


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


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


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


18


Sheet Main

VB Controls


19


Sheet Data

VB Controls


20


Sheet Support


21

Visual Basic ControlsVisual Basic ControlsBlog XQ (http://excelquotient.wordpress.com)

22


=IF(Support!B2=TRUE,"Laki-laki",IF(Support!B2=FALSE,"Perempuan",""))


23


24


Insert > Name > Define… >


25


26


27


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


28


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


29


Finnishing Touch Tools > Options… Lock cell Data > Validation…


30

Terima KasihThank You

Arigatou

Jazaakumullah

Hatur Nuhun

Blog XQhttp://excelquotient.wordpress.com

belajar excel makro

Documents