Dasar Pemrograman 1

Bikin program susah atau tidak ? 
Jawabannya SUSAH...hahahaha....

Saya share sedikit untuk kebutuhan dasar sebagai seorang programmer.
Yang paling penting adalah LOGIKA

Code yang kita tulis di dalam suatu aplikasi sebagian besar adalah logika, misal anda ingin mengetahui apakah angka 10 adalah bilangan genap, logikanya adalah
1. Bagi 10 dengan 2
2. Cek sisa hasil pembagiannya, apakah hasilnya 0
3. Jika 0 maka tuliskan "Bilangan Genap"
4. Jika tidak sama dengan (<>) 0 maka tuliskan "Bilangan Ganjil"

4 langkah ini yang nantinya kita terjemahkan dalam code program, sesuai dengan alur logika tersebut.

Nah ada 2 fungsi yang diperlukan pembuatan program.
Pengulangan
Pengulangan disini diperlukan untuk mengulang perintah yang kita inginkan
Misalnya dengan menggunakan For 
Contohnya : 
For x = 1 to 10,  
  Tuliskan x
Next x

pada code ini artinya akan dilakukan 10 kali pengulangan untuk perintah menuliskan huruf X

Pengujian
Berikutnya adalah pengujian yang digunakan untuk menguji sesuatu
Contohnya seperti pada contoh logika di awal artikel ini, yaitu pengujian bilangan genap.

If X mod 2 = 0 then
  Tuliskan "Bilangan Genap"
else
  Tuliskan "Bilangan Ganjil"
end if

*mod adalah perintah pembagian yang hasilnya adalah Sisa hasil bagi

Nah selama kita bisa menggunakan 2 hal ini dengan tepat, disertai dengan logika yang kita miliki, saya rasa setiap orang mampu untuk membuat program sendiri.

Enable Developer Tab

Jika anda baru pertama kali menggunakan macro, anda harus masuk ke dalam VBA Editor, dimana tombol untuk masuk ke sana ada di Tab Developer.
Tab ini pada awalnya tidak dimunculkan oleh Excel, sehingga perlu kita aktifkan sendiri.
Caranya adalah sebagai berikut :

Manfaat Excel Macro + Database

Excel ternyata mempunyai banyak kegunaan jika kita bisa menggunakan secara maksimal.
Contoh nyata yang sudah banyak dilakukan oleh perusahaan, adalah membuat pekerjaan yang awalnya manual menjadi otomatis, dengan cara ini keuntungan yang didapatkan adalah :
1. Kecepatan Proses
Dengan melakukan otomatisasi maka proses pengerjaan data lebih cepat, dan tentunya lebih akurat, karena proses otomatisasi tersebut dibuat berdasarkan rule rule yang sama dengan proses manual
2. Akurasi
Dengan proses otomatisasi maka akurasi juga diharapkan lebih meningkat, karena sudah tidak ada lagi proses pemindahan data secara manual, yang biasanya juga menjadi salah satu faktor kesalahan.
3. Single Source
Dengan menambahkan database pada excel, maka source data bisa di sentralisasi , sehingga hal ini akan menghilangkan masalah yang berkaitan dengan perbedaan data.

Macro Excel Project - Payroll Module

Macro Excel Project - Payroll Module

Telah selesai Payroll Module untuk perusahaan yang bergerak dibidang jasa, berlokasi di Kota Semarang.
Module Payroll ini berisikan :
1. Data Absensi
2. Transaksi Kasbon
3. Transaksi Lain-Lain
4. Data Karyawan
5. Data Karyawan Per Area
6. Data Area
7. Rekap Gaji
8. Print Slip Gaji
9. Data Estimasi THR

 Sebelum dibuatnya aplikasi ini, kesulitan yang dihadapi oleh client adalah :
1. Melakukan grouping untuk karyawan berdasarkan area, karena seringnya karyawan yang keluar masuk (resign)
2. Melakukan perhitungan gaji dengan cara manual, sehingga cukup banyak memakan waktu
3. Absensi dihitung secara manual
4. Print Slip Gaji dipetakan secara manual, dengan banyaknya karyawan maka proses ini akan memakan waktu yang lama.
5. Kasbon dan Lain-Lain dicatat dengan buku manual , sehingga harus input lagi saat perhitungan gaji

Setelah menggunakan aplikasi :
1. Grouping karyawan otomatis
2. Perhitungan Absensi cepat, menggunakan macro
3. Absensi diinput, dan perhitungan dilakukan oleh aplikasi
4. Print Slip tidak perlu memetakan secara manual lagi
5. Kasbon dan Lain-Lain dicatat didalam aplikasi, sehingga bisa langsung mengurangi gaji karyawan

Dengan menggunakan aplikasi ini harapan saya adalah waktu anda untuk bekerja bisa menjadi lebih efisien, sehingga tidak disibukkan dengan pekerjaan yang sifatnya administrative.

Pertanyaan
Berapa biaya pembuatan aplikasi ?
Biaya pembuatan aplikasi tidak bisa dipastikan, karena tingkat kesulitan suatu aplikasi berbeda dengan aplikasi lainnya. Hanya saja biaya bisa di negosiasikan dengan penyesuaian fitur dari aplikasi.

Untuk jenis usaha apa yang cocok menggunakan aplikasi berbasis Excel ?
Jenis usaha apapun cocok, hanya saja dengan menggunakan database access, maka aplikasi ini lebih cocok untuk usaha dengan skala menengah kebawah, dan tidak memerlukan database yang terpusat (1 komputer saja)

Kelebihannya apa ?
Aplikasi ini sifatnya portable, bisa di simpan di flashdisk, sehingga mudah digunakan dikomputer manapun, yang penting sudah terinstal Microsoft Excel dan Access dengan versi sesuai pada saat pembuatan.

Mengapa perlu membuat aplikasi ?
Pembuatan aplikasi bertujuan untuk membantu manusia dalam pekerjaan, aplikasi/software sebenarnya adalah cara berpikir manusia yang di terjemahkan ke dalam bentuk sistem otomatis.
Jika cara berpikir / alur proses sudah fix/tetap, maka alur ini bisa diterjemahkan menjadi suatu software, yang akan membantu manusia dalam memproses data dalam jumlah yang besar. Sehingga tidak diperlukan lagi pekerjaan yang berulang dilakukan oleh manusia. Dan waktu anda bisa digunakan untuk hal hal lain yang lebih penting daripada mengerjakan sesuatu yang berulang.

Untuk menjaga kerahasiaan data client, maka contact number tidak saya tampilkan di Gambar. Bagi rekan rekan yang berminat untuk membuat aplikasi menggunakan Microsoft Excel, silahkan kontak saya di 089610077999(Ferry)

Create Your Own Shortcut Key

Awal mulanya muncul perasaan kurang efisien pada saat bekerja dengan excel yang terdiri dari beberapa worksheet, mengapa demikian ? karena pada saat akan pindah sheet, kita harus klik sheet yang dimaksud dengan menggunakan mouse, cara kedua adalah dengan menggunakan shortcut Ctrl + Page Up/Down.

Melihat posisi tombol Page Up/Down yang ada di pojok kanan atas keyboard (Laptop Dell) rasanya susah juga untuk menekan tombol dengan benar tanpa melihat (mungkin saya kurang ahli dalam ketik mengetik hehehe....).

Akhirnya sambil iseng iseng mulailah bikin macro untuk membuat shorcut key sendiri dengan memanfaatkan Personal.xlsb yang diload setiap aplikasi excel dibuka

Codenya adalah sebagai berikut :

'Pilih Sheet sebelah kanan
Sub SelectRightSheet()
  If ActiveSheet.Index < Worksheets.Count Then
    ActiveWorkbook.Sheets(ActiveSheet.Index + 1).Activate
  End If
End Sub

'Pilih Sheet sebelah kiri
Sub SelectLeftSheet()
  If ActiveSheet.Index > 1 Then
    ActiveWorkbook.Sheets(ActiveSheet.Index - 1).Activate
  End If
End Sub

Kedua procedure ini saya masukkan dalam module.

Kemudian tambahkan juga setup code nya :

Public Sub Workbook_Open()
  Application.OnKey "%{LEFT}", "PERSONAL.XLSB!SelectLeftSheet"
  Application.OnKey "%{RIGHT}", "PERSONAL.XLSB!SelectRightSheet"
End Sub  

Code ini saya masukkan dalam workbook dari personal.xlsb juga.  
Setelah selesai , maka saya bisa memanfaatkan tombol Alt + Left/Right Arrow yang tentunya lebih mudah dijangkau oleh jari dan tanpa melihatpun bisa dengan mudah di tekan.   Setelah itu beberapa shortcut juga saya tambahkan untuk mempermudah saya dalam menggunakan excel,  

'Untuk paste format painter
Sub SelectFormatPainter()
  Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
  SkipBlanks:=False, Transpose:=False
End Sub

'Untuk membesarkan Font
Sub FontSizeUp()
  Selection.Font.Size = Selection.Font.Size + 1
End Sub

'Untuk mengecilkan Font
Sub FontSizeDown()
  Selection.Font.Size = Selection.Font.Size - 1
End Sub

'Untuk refresh pivot
Sub RefreshPivot()
  On Error GoTo last:
    ActiveCell.PivotTable.PivotCache.Refresh
  last:
End Sub

Sehingga code pada workbook menjadi :  

Public Sub Workbook_Open()   'Alt + Left Arrow
  Application.OnKey "%{LEFT}", "PERSONAL.XLSB!SelectLeftSheet"
  'Alt + Right Arrow
  Application.OnKey "%{RIGHT}", "PERSONAL.XLSB!SelectRightSheet"
  'Alt + `
  Application.OnKey "%{`}", "PERSONAL.XLSB!SelectFormatPainter"
  'Alt + [
  Application.OnKey "%{[}", "PERSONAL.XLSB!FontSizeUp"
  'Alt + ]
  Application.OnKey "%{]}", "PERSONAL.XLSB!FontSizeDown"
  'Alt + \
  Application.OnKey "%{\}", "PERSONAL.XLSB!RefreshPivot"
End Sub

Tentunya ada kekurangan mendasar dari cara ini, yaitu komputer/laptop lain yang tidak mempunyai code tersebut tidak bisa menggunakan, karena personel.xlsb hanya menempel pada komputer/laptop dimana code di masukkan (local code).

Menghitung Unhide Row

Jika kita ingin mengetahui berapa jumlah baris data setelah data tersebut di filter,
Cara pertama adalah menggunakan blocking pada cell
Klik pada data paling atas, kemudian drag hingga yang paling bawah.
Maka di status bar akan muncul count = n

Cara kedua menggunakan formula Subtotal
Formula ini cukup flexible, karena dengan 1 formula bisa menghitung beberapa jenis fungsi.
Syntax : SUBTOTAL(calculation_type, rangedata)

Berikut adalah List dari Calculation Type
1 -> AVERAGE

2 -> COUNT
3 -> COUNTA
4 -> MAX
5 -> MIN
6 -> PRODUCT
7 -> STDEV
8 -> STDEVP
9 -> SUM
10 -> VAR
11 -> VARP

Contoh penggunaan : subtotal(2, A1:A10) -> menghitung jumlah row dari A1 hingga A10 List calculation type diatas adalah untuk menghitung semua baris dalam range yang ditentukan, termasuk baris yang dalam posisi hide. Bagaimana untuk menghitung baris yang unhide saja ? anda bisa menggunakan Calculation Type berikut ini   101 -> AVERAGE
102 -> COUNT
103 -> COUNTA
104 -> MAX
105 -> MIN
106 -> PRODUCT
107 -> STDEV
108 -> STDEVP
109 -> SUM
110 -> VAR
111 -> VARP

Contoh penggunaan : subtotal(102, A1:A10) -> menghitung jumlah row dari A1 hingga A10 tanpa baris/row yang hide.


Bagaimana menghitung baris/row unhide melalui macro ?
Berikut ini adalah perintah untuk menghitung baris unhide

Range("DataTable").SpecialCells(xlCellTypeVisible).Rows.Count

Koneksi ke MS Access 2003 vs 2007/2010

Filetype dari file database MS Access (dan Office lainnya) mengalami perubahan , yaitu
2003 : MDB
2007/2010 : ACCDB

Selain itu koneksi database dari macro excel juga menggunakan engine yang berbeda, sehingga kode untuk mengakses database tersebut juga berubah

Berikut ini adalah perubahannya
2003 "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\Student.mdb;User Id=admin;Password="
2007/2010 "Provider=Provider=Microsoft.ACE.OLEDB.12.0; Data Source=E:\Student.accdb;User Id=admin;Password="

Default Sheet on First Load

Untuk menentukan sheet mana yang akan dibuka diawal pada saat file excel dibuka caranya cukup mudal, anda bisa menggunakan macro sederhana seperti dibawah ini :

Private Sub Workbook_Open()
  Sheets(1).Activate
End Sub

angka 1 adalah sebagai index atau nomor urut sheet yang akan dibuka, nomor ini bisa diganti dengan nama sheet tertentu, karena dikuatirkan sheet akan diubah urutannya.

Private Sub Workbook_Open()
  Sheets("Sheet3").Activate
End Sub

Get Column Letter From Number

Berikut ini adalah macro untuk konversi dari number ke column character.
(dibuat hingga 3 digit kolom, bagi pengguna Microsoft Excel 2010)

Function m_GetCell(iColumn As Integer) As String
  Dim i, vColumn1, vColumn2, vColumn3 As Integer
  Dim sColumn As String
 
  If iColumn <= 26 Then
    vColumn1 = iColumn + 64
  End If
  If iColumn > 26 And iColumn <= 26 * 26 Then
    vColumn1 = (iColumn - (iColumn Mod 26)) \ 26 + 64
    vColumn2 = iColumn Mod 26 + 64
  End If
  If iColumn > 26 * 26 And iColumn <= 26 * 26 * 26 Then
    vColumn1 = (((iColumn - (iColumn Mod 26)) \ 26) - ((iColumn - (iColumn Mod 26)) \ 26) Mod 26) \ 26 + 64
    vColumn2 = (iColumn - (iColumn Mod 26)) \ 26 Mod 26 + 64
    vColumn3 = iColumn Mod 26 + 64
  End If
  m_GetCell = Chr(vColumn1) & Chr(vColumn2) & Chr(vColumn3)
End Function

Sub test()
    MsgBox m_GetCell(16383)
End Sub

Semoga membantu,

Thanks

Disable Cut, Copy, Paste

Jika anda ingin data pada worksheet anda tidak bisa dicut, copy dan paste, caranya sangat mudah...
Tambahkan macro berikut pada worksheet anda :

Procedure yang pertama berguna untuk membatalkan perintah klik kanan pada mouse

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
End Sub

Procedure yang kedua berguna untuk membatalkan perintah cut atau copy

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CutCopyMode = False
End Sub

Mudah kan ?
Semoga bermanfaat...

Set Error Formula Result To Zero Value

Untuk mengubah nilai error (#N/A, #DIV/0!, dll) pada suatu formula menjadi 0 (nol) , biasa kita menambahkan fungsi IF pada formula.
Contoh :
Diketahui : A1=10, B1=0
Formula : =A1/B1
Result : #DIV/0!

Untuk mengubah nilai error menjadi 0, maka Formulanya kita ubah menjadi :
=IF(B1=0,0,A1/B1)
Maka hasilnya adalah 0

Bayangkan jika formula anda cukup panjang, maka dengan ditambahkannya fungsi IF seperti pada contoh, panjang formula anda akan menjadi 2 kali lipat, dan ini tentunya akan mempersulit anda dalam pengecekan formula jika ada kesalahan.

Contoh Formula :
=IF(A49="",0,SUM(B60:C60)/VLOOKUP(Setting!$C$3,Setting!$B$25:$C$26,2,FALSE)*VLOOKUP($B$3,Setting!$B$6:$L$8,COLUMN()-3,FALSE))

Jika menggunakan IF akan menjadi :
=IF(VLOOKUP(Setting!$C$3,Setting!$B$25:$C$26,2,FALSE)*VLOOKUP($B$3,Setting!$B$6:$L$8,COLUMN()-3,FALSE))=0,0,IF(A49="",0,SUM(B60:C60)/VLOOKUP(Setting!$C$3,Setting!$B$25:$C$26,2,FALSE)*VLOOKUP($B$3,Setting!$B$6:$L$8,COLUMN()-3,FALSE)))

Bagaimana cara untuk mempermudah ?

Cara untuk mempermudah adalah dengan memanfaatkan macro

Buat satu module , kemudian isi dengan code berikut :

Function SetToZero(vString As Variant) As Double
On Error Resume Next

If IsEmpty(vString) Or Len(vString) = 0 Then
SetToZero = 0
GoTo akhir:
Else
SetToZero = vString
GoTo akhir:
End If

If vString = CVErr(xlErrDiv0) Or _
vString = CVErr(xlErrNA) Or _
vString = CVErr(xlErrName) Or _
vString = CVErr(xlErrNull) Or _
vString = CVErr(xlErrNum) Or _
vString = CVErr(xlErrRef) Or _
vString = CVErr(xlErrValue) Then
SetToZero = 0
GoTo akhir:
Else
SetToZero = vString
GoTo akhir:
End If
akhir:
End Function

Kemudian ubah formula anda menjadi :
=SetToZero(A1/B1)

Kemudian rasakan hasilnya....