Microsoft Excel Dasar -3

oleh: Aurino Djamaris & Sridhani Pamungkas

APLIKASI KERJA DENGAN ANGKA

MEMILIH RANGE SEL

    Untuk menjumlahkan atau memformat kelompok sel, kita harus memilih sel – sel apa saja yang berhubungan dalam proses pengolahan data. Satyu atau lebih sel yang terpilih disebut range ( rentang ). Range dalam excel ditandai dengan (:) misalnya, A5:B10 merujuk pada range mulai dari sel A5 hingga sel B10.

MEMILIH SEL

Kita dapat memilih range sel dengan menggunakan mouse. Tunjuk sel pertama yang hendak kita sertakan dalam pilihan, tekan dan tahan tombil mouse, kemudian seret ke sel terakhir dalam pilihan. Lepas tombol mouse, maka sel – sel akan terpilih. Cara lain yang dapat dilakukan adalah dengan munjuk salah satu sel terpilih, tekan tombol Shift pada keyboard, tahan dan arahkan ke beberapa sel lainnya yang dikehendaki.

Sel – sel terpilih ditandai dengan adanya segi empat gelap yang menutupi sel terpilih. Apabila sel – sel terpilih berisi angka, maka fasilitas Autocalculate dari excel akan menampilkan jumlah nilai dalam sel – sel terpilih dalam bar status.

Gambar 1 Memilih Sel

MEMILIH KOLOM DAN BARIS

Anda dapat memilih seluruh sel dalam kolom dengan mengklik header kolom. Untuk memilih seluruh sel dalam baris, klik header baris. Untuk memilih lembar kerja utuh, klik tombol Select All di pojok kiri atas lembar kerja di sebelah kiri header kolom A. Untuk memilih beberapa kolom atau baris yang berada bersebelahan, pertama – tama pilih salah satu, kemudian seret melewati kolom atau baris lainnya. Untuk membatalkan pilihan kelompok sel, klik satu kali untuk memilih sel sembarang lainnya dalam lembar kerja.

MEMILIH DAERAH YANG TIDAK BERSEBELAHAN

Untuk memilih range baris dan kolom yang tidak bersebelahan kita dapat menggunakan fungsi tombol ctrl pada keyboard. Pertama kita harus tentukan salah satu range yang kita pilih, kemudian tekan tombol ctrl, tahan dan pilih range lainnya.

 
 


Gambar 2 Memilih daerah yang tidak bersebelahan

MENGGUNAKAN AUTOSUM

Tombol autosum dapat digunakan apabila kita hendak mencari nilai total atas sejumlah angka dengan cepat. Langkah – langkah yang dapat digunakan adalah :

  1. Aktifkan salah satu sel yang hendak dijadikan tempat penjumlahan.
  2. Tekan icon Autosum yang berada pada menu home pilihan editing.


  3. Pilihlah rang yang akan dicari total penjumlahannya
  4. Klik enter maka hasil akan diketahui dengan cepat.

ARITMATIKA DASAR DALAM EXCEL

Autosum hanya dapat digunakan dalam penjumlahan, tidak ada tombol automultiply atau autosubstract. Tapi kita dapat membuat rumus dalam menyesaikan perhitungan tersebut. Misalnya kita ingin membuat sebuah rumus yang mampu menghasilkan perhitungan gaji kotor karyawan dalam payroll perusahaan. Dalam menentukan gaji kotor, kita harus mengalikan jam kerja dengan upah per jam.

    Gaji kotor = Jam kerja x Upah per jam

Untuk itu kita harus menggunakan fungsi perkalian dalam excel. Sebelum kita melangkah lebih jauh, kita harus mengetahui mengenai operator hitung yang berlaku dalam Microsoft excel :

Operasi            Simbol        Contoh

Penjumlahan         +        Jam kerja biasa + jam kerja lembur

Pengurangan         –        Harga jual – potongan harga

Perkalian         *        Jumlah barang yang dibeli * harga masing – masing

                    Barang

Pembagian         /        Bonus/jumlah karyawan

Pemangkatan         ^        3^2

 
 

Kembali ke contoh soal sebelumnya, dalam menentuka gaji kotor, pertama kali kita harus memasukan data mengenai Jam kerja tiap karyawan dan upah per jam tiap karyawan kedalam lembar kerja, kemudian kita membuat kolom yang berisi gaji kotor dimana kita mulai menerapkan rumus perjanjian pada kolom tersebut.


Gambar 3 Contoh Perkalian

Keterangan : Dalam sel D4 salah satu kolom yang berisi rumus gaji kotor, kita isikan rumus perkalian antara B4 ( jam kerja ) dan C4 ( upah per jam ) . =B4*C4 kemudian enter.tarik kursor D4 kebawah sampai D6 maka rumus akan berlaku sampai D6.

MEMFORMAT ANGKA

 Jenis data angka yang dimasukan dalam excel memiliki format tersendiri yang dapat dibedakan dengan jenis angka lainnya. Pengaturan mengenai format angka dapat anda temukan pada menu bar file number.

Kategoro format angka terdiri dari :

Kategori        Uraian                            Contoh

General         Format Default                        10008.7

 
 

Number (bilangan)    Seperti general, tetapi kita dapat            1008.7

menetapkan tempat decimal dan

menggunakan pemisah ribuan

 
 

Currency         Nilai didahului dengan symbol mata uang         $1,008.70

Negara sebelum digit pertama. Nilai nol

Ditampilkan

 
 

Accounting         Nilai diratakan pada titik decimal. Apabila        $1,008.70

terdapat symbol dollar maka symbol tersebut

akan direnggangkan agar rata. Nilai nol

<

p style=”margin-left: 72pt”>diperlihatkan sebagai dash ( – )

 
 

Persentage         Nilai diekspresikan sebagai persentase, diikuti        100807%

                    Oleh symbol persentase.

Scientific         Nilai ditampilkan dalam notasi ilmiah             1.01E+03

 

 Ke

Microsoft Excel Dasar -2

oleh: Aurino Djamaris & Sridhani Pamungkas

MEMASUKKAN DATA DALAM BUKU KERJA EXCEL

MENGENAL ELEMEN – ELEMEN DASAR MICROSOFT EXCEL

Microsoft excel adalah sebuah aplikasi yang memiliki bagian – bagian dalam jendelanya. Sebelumnya kita sudah mengetahui kegunaan dari tiga tombol utama yang ada di jendela Microsoft excel. Sekarang kita akan mengetahui lebih lanjut mengenai elemen – elemen yang ada dalam Microsoft excel.

  1. Option Button Merupakan elemen utama Microsoft excel yang berisi pilihan – pilihan save, save as, dan sebagainya. Dalam option button juga terdapat excel option yang digunakan dalam pengaturan lebih lanjut mengenai Ms. World.
  2. Bar Menu Merupakan sebuah elemen yang berada tepat dibawah bar judul. Bar menu berisi sejumlah menu yang digunakan dalam menjalankan alikasi excel. Sebagai contohnya menu ” Home ” berisikan pengaturan font, number , clipboard, cells dan editing.
  3. Toolbar Merupakan deretan tool-tool (gambar-gambar) yang mewakili perintah dan berfungsi untuk mempermudah dan mengefisienkan pengoperasian program.
  4. Help Bila kita ingin bertanya sesuatu, maka ketik pertanyaan anda pada tempat tersebut. Ms. Excel akan memberikan alternatif jawaban terhadap pertanyaan yang dimaksud.
  5. Cell

Cell merupakan elemen yang terdiri dari :

  1. Nomor Baris
  2. Nomor Kolom
  3. Nama Range
  4. Fungsi
  5. Penggulung vertical dan horisontal


Gambar 1 Elemen – elemen Microsoft excel 2007 dan 2010

 
 

 
 

MEMASUKKAN TEKS DAN ANGKA DALAM MICROSOFT EXCEL

    Terdapat dua jenis data yang dapat dimasukkan dalam lembar kerja Microsoft excel, yaitu jenis data angka dan teks. Angka adalah nilai yang perlu anda jumlahkan, kurangi, kalikan, bagi, atau bandingkan. Sedangkan teks adalah data yang dapat dijadikan media keterangan atas angka yang kita masukan. Contoh : Pada gambar di bawah, ” Data Pejualan PT. Maju tahun 2007 sebagai jenis data teks yang menjelaskan keseluruhan ini atas data dalam lembar kerja tersebut, “Januari” sampai ” Desember ” dijadikan jenis data teks yang menjelaskan jenis data angka yang tersedia.


Gambar 2 Jenis data angka dan huruf

MENAMAI LEMBAR KERJA ( SHEET )

Langkah – langkah yang dapat dilakukan dalam menamai lebar kerja atau Sheet adalah :

  1. Arahkan pointer kita ke sheet yang hendak dinamai klik kanan, pilih menu Rename


Gambar 3 Rename worksheets

  1. Ketikan nama yang sesuai dengan pilihan anda.
  2. Tekan enter. Maka nama sheet yang sebelumya akan berubah sesuai dengan keinginan kita.

MENYIMPAN LEMBAR KERJA

Setelah kita bekerja menggunakan aplikasi Microsoft excel dan ingin mengakhiri aplikasi tersebut, maka kita harus menyimpan file excel ke dalam hard drive Komputer kita. Langkah – langkah dalam penyimpanan file excel sama seperti penyimpanan di dalam aplikasi Microsoft Office lainnya.

  1. Pilihlah office button excel save as Atau dengan menekan icon save .
  2. Tentukan ” File Name ” dan “Save as Type ” dari file yang akan kita simpan. Tentukan dimana letak file tersebut akan di simpan. Dan klik save

     
     


Gambar 4 Dialog Box Save As

 
 

  1. Data File excel telah tersimpan.


<

p style=”text-align: justify”>Ke    

Microsoft Excel Dasar -1

oleh: Aurino Djamaris & Sridhani Pamungkas

PENDAHULUAN

Microsoft Excel adalah salah satu aplikasi dari Microsoft office yang yang dapat membantu pekerjaan manusia khususnya yang berkaitan dengan data statistik. Tugas dari Ms. Excel adalah sebagai media pengolahan data, penghitungan, prakiraan, analisis dan dapat pula sebagai alat untuk mempresentasikan data.

Program spreadsheet (atau biasa juga disebut lembar kerja), termasuk Microsoft Excel, awalnya dibuat untuk menganalisis dan memanipulasi informasi data keuangan. Spreadsheet pertama kali digunakan sebagai pengganti kalkulator dalam melakukan hitungan keuangan sederhana. Dengan menggunakan Ms. Excel kita dapat menganalisis data terutama data numerik secara mendalam dan melalui langkah–langkah yang efektif.

MEMULAI MICROSOFT EXCEL

    Untuk membuka aplikasi Ms. Excel langkah – langkah yang dapat Anda lakukan adalah :

  1. Pindahkan petunjuk mouse ke tombol kemudian klik mouse kiri satu kali untuk membuka menu start.
  2. Arahkan mouse anda ke pada jendela menu start.
  3. Pilih menu microsoft office Microsoft Excel
  4. Klik microsoft excel, maka aplikasi microsoft excel akan terbuka.

Cara lain yang dapat dilakukan dalam proses pembukaan aplikasi Microsoft excel adalah dengan memasang icon Ms. Excel pada desktop atau dengan menggunakan “Pin” sehingga aplikasi Ms. Excel akan muncul pada menu start.

Cara yang dilakukan agar Ms. Excel tetap berada pada menu start :

  1. Lakukan langkah – langkah di atas sampai langkah ke empat.
  2. Klik kanan lalu pilih ” Pin to Start Menu ”
  3. Ketika anda klik start menu maka pilihan Microsoft excel telah tersedia.

Gambar 1 Pin excel to start menu

 

Gambar 2 Jendela aplikasi Ms. Excel 2007

 
 

 

Gambar 3 Jendela aplikasi Ms. Exce 2010l

 
 

BEKERJA DENGAN JENDELA MICROSOFT EXCEL

    Diujung kanan atas dari jendela excel terdapat tiga tombol yang dapat digunakan untuk meminimalkan, mengubah ukuran , dan menutup jendela excel.

Tombol minimize yang berada di sebelah paling kiri digunakan untuk mereduksi jendela excel menjadi icon taskbar yang berarti aplikasi “ditunda”. Aplikasi yang telah diminimalkan tetap berjalan, namun tidak aktif.

Tombol restore memungkinkan kita untuk menjaga excel tetap aktif tanpa memenuhi layar utuh.

Tombol maximize digunakan untuk mengembalikan jendela setelah di restore down kembali memenuhi layar secara keseluruhan.

Tombol close digunakan untuk mengakhiri aplikasi microsoft excel. Setelah kita mengklik tombol ini akan ada pilihan untuk menyimpan data file excel atau tidak.

<

p style=”text-align: justify”>Lanjut ke    

Fakta Ms Excel 2007-2010 Singkat

by Aurino Djamaris

Fakta Ms Excel 2007-2010 Singkat

Excel as object

Object utama

Worksheet and workbook specifications and limits

Excel user interface

Entry data

Beberapa kemampuan excel

Analysis tools

Alamat Sel (Cell Address)

Excel as object

Dalam graphical user interface, aplikasi dan bagian-bagiannya dikenal sebagai object

Object excel

  • Excel
  • Workbook
  • Worksheet
  • Range dalam worksheet
  • Listbox
  • Chart sheet
  • Chart
  • Dsb.

Go Top-TOC

Object utama

Workbooks merupakan “folder” yang berisi:

  • Worksheets
  • Chart sheets
  • Macro sheets (.xlm à obsolete)
  • Dialog sheets (obsolete)

Workbooks merupakan “folder” yang berisi:

Jumlah maksimum Lembar kerja dapat dimasukkan di excel 2003 atau sebelumnya adalah 255 lembar, maka:

Go Top-TOC

Worksheet and workbook specifications and limits

FEATURE MAXIMUM LIMIT
Open workbooks Limited by available memory and system resources
Worksheet size 1,048,576 rows by 16,384 columns
Column width 255 characters
Row height 409 points
Page breaks 1,026 horizontal and vertical
Total number of characters that a cell can contain 32,767 characters
Characters in a header or footer 255
Sheets in a workbook Limited by available memory (default is 3 sheets)
Colors in a workbook 16 million colors (32 bit with full access to 24 bit color spectrum)
Named views in a workbook Limited by available memory
Unique cell formats/cell styles 64,000
Fill styles 256
Line weight and styles 256
Unique font types 1,024 global fonts available for use; 512 per workbook
Number formats in a workbook Between 200 and 250, depending on the language version of Excel that you have installed
Names in a workbook Limited by available memory
Windows in a workbook Limited by available memory
Panes in a window 4
Linked sheets Limited by available memory
Scenarios Limited by available memory; a summary report shows only the first 251 scenarios
Changing cells in a scenario 32
Adjustable cells in Solver 200
Custom functions Limited by available memory
Zoom range 10 percent to 400 percent
Reports Limited by available memory
Sort references 64 in a single sort; unlimited when using sequential sorts
Undo levels 100
Fields in a data form 32
Workbook parameters 255 parameters per workbook
Filter drop-down lists 10,000

Sumber: http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx

Worksheets merupakan lembar kerja

  • MsExcel 2007- Satu (1) worksheet terdiri dari 256 kolom dan 65,536 baris (16,777,216 sel!!) à setara dengan 36,000 halaman cetak!!!
  • MsExcel 2010- Satu (1) worksheet terdiri dari 16,384 kolom dan 1,048,576 baris (17,178,820,608 sel!!) à setara dengan 37,000,000 (tiga puluh tujuh juta) halaman cetak!!!

Go Top-TOC

Excel user interface

Antarmuka excel bisa dilakukan dengan beberapa cara:

  • Menu
  • Dialog box
  • Toolbars
  • Drag-and-drop
  • Keyboard shortcuts

Go Top-TOC

Entry data

  • Nilai (angka, tanggal, waktu)
  • Text
  • Rumus
  • Nilai logika (boolean – true-false)
  • Beberapa tips
    • Mengisi data pada range: isikan data pada active cell, tekan CTRL+ENTER
    • Mengcopy ke bawah: drag pojok selection di bagian titik
    • Mengcopy ke bawah dengan increment (bertambah terus): drag pojok selection sambil tekan CTRL
    • Untuk penulisan teks yang panjang, ganti baris dengan ALT+ENTER
    • Untuk menulis pecahan tulis 0 <spasi> pecahan
    • Tanggal hari ini: CTRL+;
    • Tanggal hari ini dengan jamnya maka gunakan fungsi =NOW()
    • Jam saat ini: CTRL+SHIFT+;

Go Top-TOC

Beberapa kemampuan excel

  • Formating
  • Formulas
  • Names
  • Functions
  • Shapes
  • Charts
  • Macros
  • Database access & filter

Go Top-TOC

Analysis tools

  • Outline
  • Automatic subtotals
  • Scenario management
  • Pivot table
  • Auditing
  • Add-ins
    • Analysis toolpak
    • Solver
    • dan lain sebagainya

Go Top-TOC

Alamat Sel (Cell Address)

  • Relative A1
  • Absolute $A$1
  • Row absolute A$1
  • Column absolute $A1
  • Referencing other sheets/workbooks =[data.xls]Sheet2[A2]+1
  • Referensi dengan nama : INSERT|NAME| CREATE
  • Kita dapat mengubah alamat sel dengan angka melalui R1C1 reference style

    contoh jika kita menggunakan fungsi ADDRESS(77,300) hasilnya adalah $KN$77

    Go Top-TOC

VBA – Visual Basic for Application Part 1

by Aurino Djamaris

Contents

VBA – Visual Basic for Application

Dasar-dasar pemrograman VBA 

Alasan Memakai VBA 

Untuk menulis/edit program VBA digunakan IDE 

Prinsip pemrograman berbasis object dalam VBA 

Program dalam VBA disebut prosedur (dan function)

Cara membuat program VBA 

Membuat dan menjalankan macro 

Menulis Program Dalam IDE/VBA Editor 

Fungsi Dalam Excel (User Function)

Contoh1 : menghitung sisi miring segitiga 

Contoh 2 : Membuat fungsi Akar persamaan

Prosedur Dalam Excel VBA (Sub Procedure)

Contoh 1: Procedure Perhitungan Bunga 

Memanfaatkan intellisense 

Informasi parameter

Menyimpan/export modul

Mengimpor/ambil modul

Mengatur editor

Melindungi program 

Dasar-dasar pemrograman VBA

Apa yang dibicarakan?

  • Mengenal VBA
  • IDE
  • Pemrograman berbasis object
  • Cara membuat program VBA
  • Makro
  • Editor program
  • Menjalankan program
  • Membuat fungsi sendiri
  • Membuat prosedur
  • Mengatur editor
  • Melindungi program

Go Top-TOC

Alasan Memakai VBA

  • Merupakan bahasa pemrograman bagi semua aplikasi Microsoft Office (Exel, Word, Access, Powerpoint) dan beberapa yang lain (Outlook, Visio, AutoCAD, MapInfo, dsb.)
  • Dapat berfungsi sebagai macro atau bahasa pemrograman
  • Merupakan bahasa program beorientasi object, semua object office dapat dengan mudah dimanipulasi dengan VBA
  • Belajar satu bahasa dapat diaplikasikan dalam banyak aplikasi Microsoft
  • Mendukung aplikasi internet dan intranet Mendukung ActiveX controls
  • Code editor dengan bantuan yang lengkap dan debugger uForm building tools
  • Object browser
  • dan lain sebagainya yang memudahkan pembuatan program

Go Top-TOC

Untuk menulis/edit program VBA digunakan IDE

Go Top-TOC

Prinsip pemrograman berbasis object dalam VBA

  • Object : Workbook, worksheet, chart, cell, dsb.
  • Properties: Setiap object memiliki properties: color, font, italic, dsb.
  • Method & procedure: Cara/perintah untuk melakukan sesuatu: pindah worksheet, pindah baris, dsb.

Go Top-TOC

Program dalam VBA disebut prosedur (dan function)

Tiga jenis prosedur

  • Command macros à mengotomasi prosedur-prosedur, fungsi-fungsi yang ada (built-in) dalam aplikasi, memformat, menyimpan, dsb.
  • User-defined function (function procedure) à program yang berisi rumus dan perintah yang kita susun sendiri
  • Property procedure menunjukkan atau mengubah properti dari suau object (class module)

Go Top-TOC

Cara membuat program VBA

  • Dengan merekam macro (macro recording)
  • Dengan menulis program dalam IDE/VBA editor

Go Top-TOC

Membuat dan menjalankan macro

  • Tools | macro | record macro
  • Lakukan prosedur-prosedur dan perubahan properties pada worksheet
  • Stop macro recording
  • Edit macro untuk memberbaiki dan mengubah hasil rekaman
  • Menjalankan:
    • Dengan shortcut, atau
    • TOOLS| MACRO| RUN| pilih NAMA MACRO

Go Top-TOC

Menulis Program Dalam IDE/VBA Editor

  • Tools|macro|visual basic editor atau
  • Click tombol VBE
  • Pindah dari VBE ke aplikasi | alt+F11
  • Tuliskan program (function atau procedure)

    Sub

    {Baris program …………… }

    End sub

Go Top-TOC

Fungsi Dalam Excel (User Function)

  • Fungsi adalah rumus yang mengolah argumen dan mengembalikan hasil
  • Struktur fungsi

    Function NamaFungsi(argumen1, argumen2, …)

    [Pernyataan/statement VBA]

    NamaFungsi  = hasil yang dikembalikan

    End Function

  • Bagian-bagian:
    • Statement Function
    • Nama fungsi
    • Argumen fungsi (parameter)
    • Statement VBA yang melakukan hitungan
    • Hasil yang dikembalikan à nama fungsi = Statement
    • End Function

Go Top-TOC

Contoh1 : menghitung sisi miring segitiga

  • Sisimiring = sqr(x2 + y2)

    Function sisimiring(x, y)  ‘ nama fungi

    sisimiring = sqr(x ^ 2 + y ^ 2)  ‘ nilai hasil

    End Function

  • Pemakaian: isikan pada sel dengan nama formula, misal =sisimiring(3, 4) akan menghasilkan 5

Go Top-TOC

Contoh 2 : Membuat fungsi Akar persamaan

Function akar(A, B, C)

    If A <> 0 Then

            diskrim = B ^ 2 – 4 * A * C

            If diskrim > 0 Then

                    akar = “dua akar riel”

            ElseIf diskrim = 0 Then

                    akar = -B / (2 * A)

            Else

                    akar = “akar imaginer”

            End If

    Else

            akar = “bukan persamaan kuadrat”

    End If

End Function

Go Top-TOC

Prosedur Dalam Excel VBA (Sub Procedure)

  • Prosedur adalah unit program dalam VBA yang merupakan blok kode dalam satu modul
  • Struktur sebuah prosedur

    Sub NamaProsedur (argumen1, argumen2,…)

    [pernyataan/statement VBA]

    End Sub

Go Top-TOC

Contoh 1: Procedure Perhitungan Bunga

Sub InputDataPinjaman()

    Range(“bunga”).Value = 0.08

    Range(“lama”).Value = 10

    Range(“pinjaman”).Value = 100000000

    Range(“bayar”).Value =”=PMT(bunga/12,lama*12,pinjaman)”

End Sub

  • Penggunaan:

TOOLS|MACRO| pilih nama sub
InputDataPinjaman | RUN
Go Top-TOC

Memanfaatkan intellisense

  • Setiap object dalam excel memiliki properties dan methods,
  • Untuk memanipulasi obyek dalam program VBA, kita bisa memanfaatkan intellisense
  • Caranya:

    Tuliskan nama obyek dilanjutkan dengan titik (.) | akan keluar properties dari obyek tersebut

  • Contoh: application.

    Kalau kita tekan <ESC> intellisense tak akan muncul lagi untuk obyek yang sama, untuk memunculkan lagi| tekan CTRL+J

     

Go Top-TOC

Informasi parameter

  • Setiap fungsi memiliki satu/beberapa parameter, parameter terebut dapat dimunculkan dengan menuliskan nama fungsi dan tanda kurung buka

activecell.Formula=pmt( akan muncul argumen untuk fungsi pmt)
Go Top-TOC

Menyimpan/export modul

  • Dari project explorer, pilih modul yang akan disimpan
  • Pilih File|Export File
  • Pilih lokasi tempat menyimpan file
  • Beri nama file.BAS
  • Tekan Save

Go Top-TOC

Mengimpor/ambil modul

  • Dari project explorer
  • Pilih File|Import File
  • Pilih file dari lokasi tempat menyimpan file.BAS
  • Tekan Open

Go Top-TOC

Mengatur editor

Cara kerja dan tampilan editor dapat diatur dengan menu Tools|Options

Go Top-TOC

Melindungi program

Program dapat dilindungi dengan password Tools|ProjectName Properties dialog box, pilih Protection TuIiskan password untuk melindungi program OK

Go Top-TOC

VBA – Visual Basic for Application

VBA – Visual Basic for Application

Contents

VBA – Visual Basic for Application    1

Dasar-dasar pemrograman VBA    1

Alasan Memakai VBA    1

Untuk menulis/edit program VBA digunakan IDE    2

Prinsip pemrograman berbasis object dalam VBA    2

Program dalam VBA disebut prosedur (dan function)    2

Cara membuat program VBA    3

Membuat dan menjalankan macro    3

Menulis Program Dalam IDE/VBA Editor    3

Fungsi Dalam Excel (User Function)    3

Contoh1 : menghitung sisi miring segitiga    4

Contoh 2 : Membuat fungsi Akar persamaan    4

Prosedur Dalam Excel VBA (Sub Procedure)    4

Contoh 1: Procedure Perhitungan Bunga    5

Memanfaatkan intellisense    5

Informasi parameter    5

Menyimpan/export modul    5

Mengimpor/ambil modul    5

Mengatur editor    6

Melindungi program    6

Dasar-dasar pemrograman VBA

Apa yang dibicarakan?

  • Mengenal VBA
  • IDE
  • Pemrograman berbasis object
  • Cara membuat program VBA
  • Makro
  • Editor program
  • Menjalankan program
  • Membuat fungsi sendiri
  • Membuat prosedur
  • Mengatur editor
  • Melindungi program

Alasan Memakai VBA

  • Merupakan bahasa pemrograman bagi semua aplikasi Microsoft Office (Exel, Word, Access, Powerpoint) dan beberapa yang lain (Outlook, Visio, AutoCAD, MapInfo, dsb.)
  • Dapat berfungsi sebagai macro atau bahasa pemrograman
  • Merupakan bahasa program beorientasi object, semua object office dapat dengan mudah dimanipulasi dengan VBA
  • Belajar satu bahasa dapat diaplikasikan dalam banyak aplikasi Microsoft
  • Mendukung aplikasi internet dan intranet Mendukung ActiveX controls
  • Code editor dengan bantuan yang lengkap dan debugger uForm building tools
  • Object browser
  • dan lain sebagainya yang memudahkan pembuatan program

Untuk menulis/edit program VBA digunakan IDE

Prinsip pemrograman berbasis object dalam VBA

  • Object : Workbook, worksheet, chart, cell, dsb.
  • Properties: Setiap object memiliki properties: color, font, italic, dsb.
  • Method & procedure: Cara/perintah untuk melakukan sesuatu: pindah worksheet, pindah baris, dsb.

Program dalam VBA disebut prosedur (dan function)

Tiga jenis prosedur

  • Command macros à mengotomasi prosedur-prosedur, fungsi-fungsi yang ada (built-in) dalam aplikasi, memformat, menyimpan, dsb.
  • User-defined function (function procedure) à program yang berisi rumus dan perintah yang kita susun sendiri
  • Property procedure menunjukkan atau mengubah properti dari suau object (class module)

Cara membuat program VBA

  • Dengan merekam macro (macro recording)
  • Dengan menulis program dalam IDE/VBA editor

Membuat dan menjalankan macro

  • Tools | macro | record macro
  • Lakukan prosedur-prosedur dan perubahan properties pada worksheet
  • Stop macro recording
  • Edit macro untuk memberbaiki dan mengubah hasil rekaman
  • Menjalankan:
    • Dengan shortcut, atau
    • TOOLS| MACRO| RUN| pilih NAMA MACRO

Menulis Program Dalam IDE/VBA Editor

  • Tools|macro|visual basic editor atau
  • Click tombol VBE
  • Pindah dari VBE ke aplikasi | alt+F11
  • Tuliskan program (function atau procedure)

    Sub

    {Baris program …………… }

    End sub

Fungsi Dalam Excel (User Function)

  • Fungsi adalah rumus yang mengolah argumen dan mengembalikan hasil
  • Struktur fungsi

    Function NamaFungsi(argumen1, argumen2, …)

    [Pernyataan/statement VBA]

    NamaFungsi  = hasil yang dikembalikan

    End Function

  • Bagian-bagian:
    • Statement Function
    • Nama fungsi
    • Argumen fungsi (parameter)
    • Statement VBA yang melakukan hitungan
    • Hasil yang dikembalikan à nama fungsi = Statement
    • End Function

Contoh1 : menghitung sisi miring segitiga

  • Sisimiring = sqr(x2 + y2)

    Function sisimiring(x, y)  ‘ nama fungi

    sisimiring = sqr(x ^ 2 + y ^ 2)  ‘ nilai hasil

    End Function

  • Pemakaian: isikan pada sel dengan nama formula, misal =sisimiring(3, 4) akan menghasilkan 5

Contoh 2 : Membuat fungsi Akar persamaan

Function akar(A, B, C)

    If A <> 0 Then

            diskrim = B ^ 2 – 4 * A * C

            If diskrim > 0 Then

                    akar = “dua akar riel”

            ElseIf diskrim = 0 Then

                    akar = -B / (2 * A)

            Else

                    akar = “akar imaginer”

            End If

    Else

            akar = “bukan persamaan kuadrat”

    End If

End Function

Prosedur Dalam Excel VBA (Sub Procedure)

  • Prosedur adalah unit program dalam VBA yang merupakan blok kode dalam satu modul
  • Struktur sebuah prosedur

    Sub NamaProsedur (argumen1, argumen2,…)

    [pernyataan/statement VBA]

    End Sub

Contoh 1: Procedure Perhitungan Bunga

Sub InputDataPinjaman()

    Range(“bunga”).Value = 0.08

    Range(“lama”).Value = 10

    Range(“pinjaman”).Value = 100000000

    Range(“bayar”).Value =”=PMT(bunga/12,lama*12,pinjaman)”

End Sub

  • Penggunaan:

TOOLS|MACRO| pilih nama sub
InputDataPinjaman | RUN

Memanfaatkan intellisense

  • Setiap object dalam excel memiliki properties dan methods,
  • Untuk memanipulasi obyek dalam program VBA, kita bisa memanfaatkan intellisense
  • Caranya:

    Tuliskan nama obyek dilanjutkan dengan titik (.) | akan keluar properties dari obyek tersebut

  • Contoh: application.

    Kalau kita tekan <ESC> intellisense tak akan muncul lagi untuk obyek yang sama, untuk memunculkan lagi| tekan CTRL+J

Informasi parameter

  • Setiap fungsi memiliki satu/beberapa parameter, parameter terebut dapat dimunculkan dengan menuliskan nama fungsi dan tanda kurung buka

activecell.Formula=pmt( akan muncul argumen untuk fungsi pmt)

Menyimpan/export modul

  • Dari project explorer, pilih modul yang akan disimpan
  • Pilih File|Export File
  • Pilih lokasi tempat menyimpan file
  • Beri nama file.BAS
  • Tekan Save

Mengimpor/ambil modul

  • Dari project explorer
  • Pilih File|Import File
  • Pilih file dari lokasi tempat menyimpan file.BAS
  • Tekan Open

Mengatur editor

Cara kerja dan tampilan editor dapat diatur dengan menu Tools|Options

Melindungi program

Program dapat dilindungi dengan password Tools|ProjectName Properties dialog box, pilih Protection TuIiskan password untuk melindungi program OK

Pivot Tables in Excel 2007

Pivot Tables in Excel 2007

  • A Pivot Table is way to present information in a report format. The idea is that you can click drop down lists and change the data that is being displayed. For example, choose just one student from a drop down list and view only his or her scores. Pivot tables are a lot easier to grasp when you see them in action. Here’s the one we’re going to create :

  • Look at Row 4. This shows that the student is Elisa. If we click Elisa’s drop down arrow, we’ll see this:

  • Now we have another student to select (we’ll only use two students, for this tutorial). We could untick Lisa, and tick Mary instead. Then her scores would display.
  • The Subject and Month cells also have drop down lists. So we could view only January’s scores, and just for Art and English, for example.
    So this is a Pivot Table – a report that we can manipulate by selecting items from drop down lists. Let’s make a start.
  • The first thing you need for a Pivot Table is some data to go in it.
  • Highlight the data that will be going in to your Pivot Table (cells A1 to D37). On the Excel 2007 menu bar, click Insert. From the Insert menu, locate the Tables Panel:
  • On the Tables panel click Pivot Tables. The Create Pivot Tables dialogue box appears:

  • In the dialogue box above, the data that we highlighted is in the Table/Range textbox. You can select different cells by clicking the icon to the right of the Table/Range textbox. You can also specify an external data source, such as a text file, for the data in your Pivot Table.
  • We’ve selected a New Worksheet as the place where the Pivot Table will be placed. Click OK.
  • When you click OK, Excel 2007 present you with a rather complex layout. The area on the right should look something like this one below:

  • Now take a look at the Pivot Table Field List image again, the one above the completed pivot table. It has tick boxes for Month, Subject, Student, and Score. These are column headings from the original spreadsheet data. We’ve put the Month in cell A7 on our Pivot Table, Subject is in cell B6, Student is in cell B4, and Score is the Average scores in cells C8 to G10. You’ll see how it works, though.
  • The idea is that you tick a box in the Pivot Table Field List, and then drag it to the four areas below. Excel 2007 will take care of the rest.
  • So, tick all four boxes in the field list:

  • Excel will create a basic (and messy) Pivot Table for you. But we’re going to put our 4 fields into the 4 areas below. Here’s the 4 areas we can drag to:

  • For the Report Filter, we want the name of a Student. For the Column Labels, we want the Subject, and for the Row Labels, we’ll just have the Month. The Values will be the Average scores.
  • If you look at the Field areas after you have ticked all four boxes, however, you may see something like this:
  • So click on Student in the Row Labels box. Hold down your left mouse button, and then drag it in to the Report Filter box. If you don’t fancy dragging and dropping, simply click the Student item with your left button. From the menu that appears, select Move to Report Filter:

  • Your Field areas will then look like this:
  • Pivot: Move Row Label to Column Labels
    • Move Subject from Row Labels to the Column Labels area:

  • Your Field areas will then look like this:

  • The Pivot Table on your spreadsheet will look a lot different, too. It should be looking like this:

  • The numbers have all been added up. But we want averages, instead. To change the formula, click on Sum of Score under the Values field area:

 

  • Change the Formula from Sum to Average, and then click OK. Your Average formula won’t be formatted to any decimal places. So highlight you data. On the Home menu in Excel 2007, locate the Number panel. Format your Averages so that it has no decimal places. Your Pivot Table will then look like this:

  • Look at cells A3, B3 and A4 above. These all have the not very descriptive names of Average of Score, Column Labels, and Row Labels. You can click inside of these cells and type your own headings, in exactly the same way as you would to enter text in a normal cell.
  • In the new version of the Pivot Table below, we have renamed these cells. We’ve also centred the data.

  • Click anywhere on your Pivot Table to highlight it. Now look at the menu bar at the top of Excel 2007. You’ll notice a Design menu. Click on this to see the various design options.
  • The Pivot Table Style Options panel is interesting.

  • Select Banded Rows and see what happens. Now click Banded Columns.
  • Next to this panel, there are lots of Pivot Table Styles to chose from. Select one that catches your eye. Here’s our finished Pivot Table again, only with a different Style:

<

p style=”text-align: center”>

Data Entry Forms

  • If your spreadsheet is too big to manage, and you constantly have to scroll back and forward just to enter data, then a Data Form could make your life easier. To see what a Data Form is, we’ll construct a simple spreadsheet
    • Enter January in Cell A1 of a new spreadsheet

    • AutoFill the rest of the months to December

    • Now, highlight the columns A1 to L1 (click on the letter A and drag to letter L)
    • On the Home menu from Excel 2007, locate the Cells panel
    • On the Cells panel, click the Format item
    • From the Format menu, click Width
    • Enter a value of say 20 for the Column Width, and click OK
    • Some of your months should disappear from the spreadsheet
  • The problem is, if you have to enter data under each month, you’d have to scroll across to complete the row. And then scroll back again to start a new row. Instead of doing this, we’ll create a Data Form. You then enter data in the form to complete a row on your spreadsheet. No more scrolling back and forth! Type any number you like in cell A2, under January. Then type a number in cell B2 for February. Now highlight the columns A to L again. This is so that Excel 2007 will know which are the column headings and which is the data.
  • Click the Form item you have just added to the Quick Access toolbar:

    2007 menu or 2010 menu

  • You should then see this:

<

p style=”margin-left: 18pt”>Just continue your data entry by filling the data and add new data in new line using button.

If you have existing records, you will see a form for each record. If your spreadsheet is new, you’ll see a blank form with your labels.

While the Excel data form may not make data entry fun, it does reduce the time it takes me to enter the data.

Introducing Data Analysis Using Excel 2007 and 2010 Part-1

Introducing Data Analysis Using Excel 2007 and 2010

The student familiar with Data Analysis using Ms Excel 2007 or Ms Excel 2010, Organizing Worksheet Data, Creating Formula, Using Function (Statistics & Financial ), Working With Pivot Tables and Charting Data

A report produced by the students should be in the form of working procedures and results in both softcopy and hardcopy

1. The Quick Access Toolbar in Excel 2007

  • If you look at the very top of the Excel 2007 screen, you’ll see what’s called the Quick Access Toolbar. The default will look similar to this:


  • The floppy disk icon is for quick saving, then you have an Undo, and Redo options, followed by a printer. You can add your own items to the Quick Access Toolbar. In the next part, you’ll see what a Data Form is. However, Data Forms are not on the Excel Ribbon, but have now been tucked away. So we’ll add the Data Forms icon to the Quick Access Toolbar.
  • To find Data Forms, click on the File button in the top left of Excel. From the File menu, click on Excel options at the bottom:


  • When you click the Excel Options button, you’ll see this dialogue box popping up:

  • Click the Customization button on the left. The idea is that you can place any items you like on the Quick Access toolbar at the top of Excel 2007. You pick one from the list, and then click the Add button in the middle.
  • To add the Data Form option to the Quick Access Toolbar, click the drop down list where it says Choose Commands From. You should see this (we’ve chopped a few options off, in the image below):

From the Commands Not in  the Ribbon list, select Form. Now click the Add button in the Middle. The list box on the right will then look something like this one:

  • Explore the other items you can add to the Quick Access Toolbar. You might find your favourite in there somewhere!
  • When you click OK on the Excel Options dialogue box, you’ll be returned to Excel 2007. Look at the Quick Access toolbar, and you should see your new item:

  • The Data Form item is highlighted, in the image above. We’ll be using this icon in the next part.