belajar excel makro

Blog XQhttp://excelquotient.wordpress.com

Visual Basic for Visual Basic for Application Microsoft Application Microsoft

Office ExcelOffice Excel

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)

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

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 xl-mania@yahoogroups.com. 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

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]

Integrated Development EnvironmentIntegrated Development EnvironmentBlog XQ (http://excelquotient.wordpress.com)

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.

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

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

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…

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…

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…

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…

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…

Excess Demand Seimbang Tekanan Jual

Frekuensi Kondisi Pasar

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>…

• Do until …<condition>……<program>…

• For …<condition>……<program>…

Conditional & Looping SyntaxConditional & Looping SyntaxBlog XQ (http://excelquotient.wordpress.com)

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

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

Sheet Main

VB Controls

Sheet Data

VB Controls

Sheet Support

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

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

Insert > Name > Define… >

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

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

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

Terima KasihThank You

Arigatou

Jazaakumullah

Hatur Nuhun

Blog XQhttp://excelquotient.wordpress.com

belajar excel makro

Documents

Transcript of belajar excel makro

Teori Makro Ekonomi

makro 5,6,&7

Excel 2 2011 Skema Jawapan Makro Stpm 2011 Sabah Trial

Mari Belajar Excel

Documents.tips Mikro Makro

Makro Teaching

MAKRO iKTiSAT 1

Makro Dan Mikro Nutrien

Pengajaran makro

Rph makro pengajaran

Membuat Grafik Fungsi di Excel · PDF fileSeri Tutorial Belajar Komputer Oleh Yani Pieter Pitoy MEMBUAT GRAFIK FUNGSI DI EXCEL 1 Tutorial MEMBUAT GRAFIK FUNGSI Yani

soalan STPM 2010 Makro

Makro Pengajaran

Struktur Makro Saluran Pernapasan

Modul Belajar Excel 2007 Tingkat Lanjut1(1)

Slide Makro

persekitaran makro

belajar excel 2000

Belajar mudah microsoft office excel 2007

Tutorial Cara Cepat Belajar Microsoft Excel