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

Round, RoundUp, RoundDown, Floor, Ceilling

Excel menyediakan beberapa fungsi untuk pembulatan , antara lain :
1. Round
2. RoundUp
3. RoundDown
4. Floor
5. Ceiling

Ada yang bingung mau pakai fungsi pembulatan yang mana ?
Mari kita bahas satu persatu beserta contoh hasil dari formulanya

Syntax nya adalah sbb :
Round "=ROUND(angka, digit)"
RoundUp "=ROUNDUP(angka, digit)"
RoundDown "=ROUNDDOWN(angka, digit)"
Floor "=FLOOR(angka, significance)"
Ceiling "=CEILING(angka, significance)"

Gunanya adalah sbb :

Round
Digunakan untuk pembulatan matematis
Jika "<= 0.5" akan dibulatkan keatas, "<5 akan="" dibulatkan="" kebawah="" span="">
pada sytax digunakan untuk mengatur berapa digit setelah koma yang akan dibulatkan.

Contoh :
"=ROUND(0.49,0)" hasilnya adalah 0
"=ROUND(0.5,0)" hasilnya adalah 1
"=ROUND(4.5,1)" hasilnya adalah 5
"=ROUND(5.4,1)" hasilnya adalah 5
"=ROUND(5,-1)" hasilnya adalah 10
"=ROUND(4.9,-1)" hasilnya adalah 0

RoundUp
Digunakan untuk pembulatan keatas
Pembulatan ini tidak melihat berapa nilai dibelakang koma, 0.1 pun akan dibulatkan menjadi 1
Contoh :

"=ROUNDUP(0.49,0)" hasilnya adalah 1
"=ROUNDUP(0.5,0)" hasilnya adalah 1
"=ROUNDUP(4.5,1)" hasilnya adalah 5
"=ROUNDUP(5.4,1)" hasilnya adalah 6
"=ROUNDUP(5,-1)" hasilnya adalah 10
"=ROUNDUP(4.9,-1)" hasilnya adalah 10

RoundDown
Digunakan untuk pembulatan kebawah Pembulatan ini tidak melihat berapa nilai dibelakang koma, 0.9 pun akan dibulatkan menjadi 0
Contoh :
"=ROUNDDOWN(0.49,0)" hasilnya adalah 0
"=ROUNDDOWN(0.5,0)" hasilnya adalah 0
"=ROUNDDOWN(4.5,1)" hasilnya adalah 4
"=ROUNDDOWN(5.4,1)" hasilnya adalah 5
"=ROUNDDOWN(5,-1)" hasilnya adalah 0
"=ROUNDDOWN(4.9,-1)" hasilnya adalah 0

Floor
Digunakan untuk pembulatan kebawah.
Bedanya dengan RoundDown adalah pembulatan menggunakan Floor adalah pembulatan kebawah dengan kelipatan angka tertentu, misal dibulatkan setiap kelipatan 2, maka angka 5 akan menjadi 4

Contoh :
"=FLOOR(112,1)" hasilnya adalah 112
"=FLOOR(112,3)" hasilnya adalah 111
"=FLOOR(112,5)" hasilnya adalah 110
"=FLOOR(112,7)" hasilnya adalah 112
"=FLOOR(112,9)" hasilnya adalah 108

Ceiling
Digunakan untuk pembulatan keatas.
Bedanya dengan RoundUp adalah pembulatan menggunakan Ceiling adalah pembulatan keatas dengan kelipatan angka tertentu, misal dibulatkan setiap kelipatan 2, maka angka 5 akan menjadi 6
Contoh :
"=CEILING(112,1)" hasilnya adalah 112
"=CEILING(112,3)" hasilnya adalah 114
"=CEILING(112,5)" hasilnya adalah 115
"=CEILING(112,7)" hasilnya adalah 112
"=CEILING(112,9)" hasilnya adalah 117

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="