Macro excel untuk menulis rumus atau formula

Bagaimana caranya memasukkan rumus excel ke dalam macro excel vba?

Memang digunakan cara khusus untuk memasukkan rumus excel ke dalam macro excel vba, karena penulisan rumus dalam excel secara langsung berbeda dengan penulisan rumus atau fungsi menggunakan macro.

contoh rumus rumit berikut bagaiman penulisan dalam macronya?
=IF(ISERROR(FIND("POST",B1,1)+FIND("DLKT4",B1,1))=FALSE,"MASUK","KELUAR")
http://mymacrosformula.blogspot.com/
Caranya adalah :
1. Tulis rumus atau fungsi di cell yg sudah ditentukan seperti biasa tanpa macro.
2. Setelah rumus selesai dibuat.

macro excel lebih fleksibel

seringkali kalau melihat macro excel bekerja bisa bikin pusing.

untuk mengatasinya gunakan kode:
- diawal macro
    Application.ScreenUpdating = False
dan diakhir macro gunakan kode :
    Application.ScreenUpdating = True

untuk kode makro yang langsung menunjuk alamat sel, akan terlalu riskan, dan tidak fleksibel
karena saat letak cel berubah maka macro tidak akan jalan malah salah copy.
seperti contoh dibawah :
    Range("S10").Select



agar macro lebih fleksible saat blok atau pilih beberapa cell langsung aja di bikin referensi dengan define name, contoh range diberi nama refername:
ActiveWorkbook.Names.Add Name:="refername", RefersTo:=Selection

nanti saat proses bisa langsung menunjuk ke referensi nama sel
contoh proses filter dibawah:

    ActiveSheet.Range("refername").AutoFilter Field:=8, Criteria1:="=*arti*" _
        , Operator:=xlOr, Criteria2:="=*ada*"

atau proses copy contoh:
    Application.Goto Reference:="almckintfc"
    Selection.Copy


saat pertama kali buka file untuk mendukung fleksibilitas.
maka blok atau drag sell tidak usah nyebut alamat cell.

Perubahan code macro setelah pindah ke windows 7 ke atas

Setelah saya ganti PC atau ganti system di komputer ternyata ada beberapa macro yang tidak jalan.
Macro yang tidak jalan itu adalah macro yang berhubungan dengan tentang alamat file atau folder.

semua macro chdir tidak bisa jalan di wondows 7 ke atas.

Agar bisa jalan kode macro chdir mesti ditambah sebelumnya dengan CHDRIVE


Kemungkinan dari tidak jalannya macro ini disebabkan oleh pergantian system folder atau alamat file default yg terjadi antara xp ke seven.

Pada win xp default simpan document ada di mydocument di c:\user, dan my document bisa dipindah ke folder lain

Pada win 7 ke atas alamat simpan document defaultnya ke library. dan library documentnya tidak bisa dipindah.

Demikian sharing saya kali ini
nanti khan postingan berikutnya

Dilema menggunakan pemercepat pekerjaan

Memang tidak mudah menggunakan macro apalagi harus belajar lebih dalam dan lebih banyak lagi serta perlu waktu yang tidak sedikit, padahal disamping itu pekerjaan harian sudah menumpuk, pekerjaan rumah sudah semakin banyak. kapan waktunya dan kapan mulainya menggunakan macro untuk mempercepat pekerjaan. Ada 2 pertanyaan dan keputusan yang harus dijawab dan diambil untuk langkah ke depannya : http://mymacrosformula.blogspot.com
1. Apakah masih ingin mempercepat pekerjaan dan mulai menggunakan macro (ironinya mempelajari macro membutuhkan waktu , apakah masih ada waktu untuk itu?)
2. Apakah terus mempertahankan cara cara lama tidak usah memakai macro atau apapun untuk mempercepat pekerjaan. (kalau keputusan ini yang diambil lupakan saja macro, tapi ingat untuk kedepannya nanti pekerjaan akan bertambah banyak dan menumpuk. apakah masih bisa menanganinya?). http://mymacrosformula.blogspot.com

Tehnik dalam menggunakan Macro untuk yang tidak seragam atau tidak standar.

Ada kalanya kita menemukan masalah dimana macro tidak bisa memecahkan masalah otomatisasi kita, mungkin ada penyelesaiannya tapi kita aja yang tidak atau belum menemukannya. saat menghadapi masalah seperti ini kita bisa menyelesaikannya dengan menggunakan tehnik tehnik tertentu. diantaranya bisa dengan menggunakan file dummy, atau kolom dummy atau kolom atau range sementara untuk perhitungan tertentu yang nanti bisa disambung sambung dan hasil akhirnya bisa menemukan pemecahan masalah. 

contoh :

• Seringkali untuk rumus logika IF kalau tidak salah hanya dibatasi sampai dengan 7 kriteria untuk slebihnya bisa menggunakan kolom dummy yaitu misal kriteria s/d 40 kriteria kolom dummy 1 untuk rumus if sampai kriteria ke tujuh, lalu jika hasil kom dummy 1 masih belum ketemu di buat kolom dummy 2 sampai kriteria ke 14, dst sampai buat 6 kolom dummy.
• kolom dummy terakhir disini 6, dibikin resume atau hasil rekap semua kolom dummy. dengan rumus =kolom dummy1&kolom dummy2&kolom dummy3&kolom dummy4&kolom dummy5 atau = kolom dummy1+kolom dummy2+kolom dummy3+kolom dummy4+kolom dummy5

Simpan file sekaligus mengganti dan menghapus file lama

Bagaimana menyimpan file baru dengan macro dan mereplace file lama yang namanya sama.

contoh file baru disimpan dengan nama book2.xlsx, padahal mungkin file lama sudah ada difolder tersebut dengan nama yang sama.

Dim Wk As Workbook

If Dir("C:\book2.xlsx") <> "" Then
Kill "C:\book2.xlsx"
Else 'http://mymacrosformula.blogspot.com/

Navigasi Cell dalam macro excel VBA

Mungkin sudah banyak yang tau ini, ini mengingatkan lagi kalau lupa tinggal baca lagi secara ringkas :

Menuju ke alamat cell spesial paling awal/atas dan ujung bawah/terakhir sheet
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Menuju ke alamat cell paling ujung bawah/terakhir sheet
shortcut (ctrl+end)
macronya :

macro untuk meletakkan/ memilih cell dan memblok suatu range

Macro untuk meletakkan/ memilih cell dan memblok suatu range yang terpisah pisah. Beberapa keistimewaan macro yang jarang terlihat dan langka untuk loncat ke cell tertentu dan memblok atau menambah area blok dari rang yang sudah ada.http://mymacrosformula.blogspot.com/
http://mymacrosformula.blogspot.com/
Bagaimana melompat atau menuju ke cell turun 5 baris dan kekiri 3 kolom, dari tempat cell berada sekarang ?
Jawab :

Message box dengan timer pada AHK

Kegunaan message box pada AHK sebagai perantara loncatan dari windows ke windows berikutnya. Tapi msgbox biasa tidak bisa lanjut kecuali di tekan enter atau spasi. Eh ternyata ada msgbox dengan waktu atau timer. Jadi messagebox dengan waktu atau timer ini atau pendeknya kusebut saja msgbox timer ini berfungsi sebagai informasi di tengah jalannya program macro desktop. Info ditengah proses ini bukan informasi biasa tapi info dengan pilihan dan pernyataan, bahwa sudah selesai proses pertama mau dan akan dilanjutkan ke proses kedua lalu ada pertanyaan apakah semua bahan sudah siap kalau sudah siap klik OK atau biarkan msgbox 2 s/d 4 detik maka proses kedua akan lanjut otomatis. Dan ada alternative kalau belum siap dilanjutkan

Menggunakan dim dan filename untuk referensi cell

Dim digunakan pada macro agar kode nama atau istilah tertentu bisa dimasukkan dan dikenali dalam macro. Pertama Dim kita tentukan dulu menjadi jenis apa, bisa string/huruf atau integer/bilangan atau lainnya. Kedua kita definisikan atau kita beri nilai mengacu kemana?

Untuk referensi cell dalam macro bisa menggunakan define name dan bisa juga

Bedakan macro cell find dan selection.find

Bedakan makro antara cari seluruh cell dengan mencari dari suatu range.

bedakan antara Cells.find dengan Selection.Find
http://mymacrosformula.blogspot.com/


dibawah ini adalah di seluruh cell dalam satu sheets
http://mymacrosformula.blogspot.com/
Cells.Find(What:="asd", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
http://mymacrosformula.blogspot.com/

dibawah ini adalah di dalam suatu range atau suatu blok

http://mymacrosformula.blogspot.com/
Range("D5:G13").Select

Selection.Find(What:="ser", After:=ActiveCell, LookIn:=xlFormulas, _LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _MatchCase:=False, SearchFormat:=False).Activate

Mensiasati File yang terlalu besar Bag.2

Salah satu cara mengecilkan file adalah dengan membuat file kedua salinan yang merupakan hanya sebagai laporan.

misal ada file 1 induk perhitungan (didalam file banyak sekali rumus vlookupnya dan sumifnya sehingga file berkembang menjadi 30 mega)
lalu tehnik yang saya lakukan adalah http://mymacrosformula.blogspot.com/

Penjelasan dan contoh soal:

1. difile 1 induk perhitungan dibikin alamat cell tertentu misal (cell m40) dengan rumus mis : http://mymacrosformula.blogspot.com/

="cek balance "&MID(A3;6;20)&" per "&TEXT(NOW();"dd-mm-yyyy pukul HH'Mm")&".xls"

hasil dari rumus itu akan menjadi "cek Balance September 2012 per 08-10-2012 pukul 11'16.xls"
http://mymacrosformula.blogspot.com/
alamat ini nanti akan menjadi nama file salinan yang ukurannya lebih kecil dengan menghilangkan rumus referensi dari file induk menjadi value.


2. Bikin Macro untuk mengcopy beberapa sheet yang diperlukan dari file induk ke file baru dengan nama di cell yang sudah ditentukan diatas.dengan macro :

Sheets(Array("Cek balance", "Cek face perdist p", _

"Cek face perdist r", "Cek face perdist n", "PPAIDperdist", "RPAID perdist", _
"NPAID perdist")).Select

Sheets("Cek Balance").Activate
Sheets(Array("Cek Balance", "Cek face perdist p", _

"Cek face perdist r", "Cek face perdist n", "PPAIDperdist", "RPAID perdist", _
"NPAID perdist")).Copy

'menyimpan dengan nama dari alamat cell
ChDir "D:\1 interf pst"

ActiveWorkbook.SaveAs Filename:= _
(Range("m40"))
Range("C8").Select

3. Menyimpan file dalam bentuk format excel 2003 lalu menutup atau memisahkan semua link dari file 1 induk perhitungan. sehingga menjadi berdiri sendiri. dan otomatis semua rumus dan fungsi referensi seperti vlookup, hlookup, maupun sum if menjadi value.

ActiveWorkbook.SaveAs Filename:= _

(Range("m40")) _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.BreakLink Name:= _
"D:\1 interf pst\bulan lalu\1 file induk perhitungan.xls", _
Type:=xlExcelLinks

4. Sampai proses ketiga ini jalankan dulu macronya untuk mengetahui link mana saja yang tidak terputus. Catat di cell mana saja link tidak terputus. lalu dibuat copy value untuk semua cell tersebut. (catatan : macro edit link diatas tidak berhasil memutuskan semua link)
kemudian menyimpan file baru salinan yang hampir tanpa rumus itu.


berikut adalah copy paste value untuk cel cel yang linknya tidak terputus :
Sheets("Cek balance").Select

Range("I6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Cek balance").Select
Range("M40").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Range("k43:l43").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Cek face perdist p").Select
Range("A8").Select
Sheets("Cek face perdist p").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save

Bagaimana Membuat otomatisasi dengan macro excel VBA

Otomatisasi memang diperlukan untuk mempermudah dan mempercepat pekerjaan. Beberapa keuntungan nya diantaranya :

• Memperpendek waktu pemrosesan perhitungan (http://mymacrosformula.blogspot.com)
• Menyederhanakan masalah dengan adanya dashboard atau shortcut interface
• Memperpendek waktu berpikir dan mengurangi energi berpikir yang tidak perlu. ( misal : setelah ini klik ini plus ini copy ini dan paste ini akan memakan waktu lama dan melelahkan. Dengan otomatisasi itu semua tidak terjadi.)(http://mymacrosformula.blogspot.com)
• Tidak perlu mengingat rumus atau prosesnya mulai dari awal lagi.

Otomatisasi diperlukan untuk pekerjaan yang sifatnya berkala misal rutin harian, mingguan, bulanan bahkan tahunan. Dan bisa juga dikerjakan untuk suatu pekerjaan yang sangat banyak dan lebih banyak lagi sehingga akan memerlukan proses berbulan bulan atau sampai tahunan. Dengan otomatisasi pekerjaan berbulan bulan

Perbedaan excel 2003 dan dengan excel 2007

Apa saja perbedaaan Excel 2003 dan excel 2007?

saya akan menjawab dengan yang saya ketahui saja, diantaranya:
http://mymacrosformula.blogspot.com
1. Tampilan menu pada 2003 dan pada excel 2007 jauh berbeda.
2. cara mengeluarkan shortcut patokan pada tampilan menu juga berbeda.
     contoh :
     - insert baris excel 2003 shortcut nya ALt+I+R         (pilihan menu insert dan row)
     - insert baris excel 2007 shortcut nya ALt +H+I+R   (pilihan menunya Home, Insert, Row)

3. Tapi shortcut excel 2003 masih bisa jalan pada excel 2007
    contoh :
    - insert baris excel 2003 shortcut nya ALt+I+R (pilihan menu insert dan row)

    - coba tekan ALt+I+R di excel 2007 hasilnya tetap bisa jalan yaitu menambah baris. http://mymacrosformula.blogspot.com

Message box menentukan yes or no atau jalankan atau tidak.

Dalam suatu perhitungan atau penggunaan macro ada kalanya kita dihadapkan dengan dua kondisi yang memerlukan keputusan kita yes or no (jalankan atau tidak ). Sehingga walau proses macro otomatis dijalankan kita perlu menentukan apakah proses dilanjutkan atau tidak.

Bagaimana caranya membuat macro untuk message box dengan yes or no (jalankan atau tidak) :
Contoh macro :

Dim Answer As String

Dim MyNote As String



'Place your text here
MyNote = "Cek dulu apakah sudah benar atau belum, kalau sudah tekan yes ?"

'Display MessageBox
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "perhitungan belum benar, ulang lagi dari awal")



If Answer = vbYes Then

Application.Run _


"Lanjutkan_ke_tahap_ke_dua"



Else

'Code for No button Press

MsgBox "perhitungan belum benar, ulang lagi dari awal!"

End If

Kegunaan tombol untuk menjalankan macro excel dan macro desktop

Bagaimana caranya menjalankan macro pada excel menggunakan tombol.

Macro excel dapat dijalankan dengan tombol yang di klik, jadi disini tombol tersebut berfungsi sebagai triger / pemicu mulai dijalankannya macro.

Macro pada excel dapat dibuat dengan menggunakan menu insert shape/image lalu klik kanan pada shape/image dan pilih assign macro dan pilih macro yang sudah disimpan dan akan dijalankan. dengan langkah sebagai berikut : http://mymacrosformula.blogspot.com/

1. Pilih menu Insert dan pilih shapes lalu pilih bentuk shape / image yang diinginkan

http://mymacrosformula.blogspot.com/
2. Klik kanan pada shape atau image atau gambar lalu pilih assing macro. Dan tinggal pilih macro yang akan dijalankan dengan klik pada gambar

http://mymacrosformula.blogspot.com/



Pada pekerjaan rutin macro ini sangat berguna sebagai interface atau index.

Mendownload dan menyimpan hasil cms ke local drive

Setiap hari aku memasukkan hasil penerimaan uang ke dalam kertas kerja perhitungan. kalau memasukkan manual akan makan banyak waktu terutama waktu berpikir. misal berpikir untuk setelah klik ini lalu kik ini lalu klik itu. walau kelihatan sepele tapi waktu berpikir tersebut cukup makan waktu. Jadi aku bikin program kecil agar bisa seragam masuk ke kertas kerja. Program ini dibuat dari gabungan macro excel VBA dengan macro desktop Autohotkey (AHK).
Karena aku adalah pemula dan tidak punya latar programmer. Jadi aku membuatnya tidak seperti programmer. Kalau programmer akan membuat algoritma (jalan logikanya program) dulu lalu bikin programnya. Kalau aku kebalikannya Bikin dulu programnya lalu bikin jalan berpikir/algoritmanya programnya.

Alur umum dulu :

1. Buka cms isi password dan masuk cms (dengan AHK)

Mensiasati file excel yang terlalu besar

Saya lebih suka Excel 2003 walau jumlah barisnya 65.000 baris. Dalam pemakaian sehari hari saya pernah menggunakan file sampai file Excel tersebut berkembang ukurannya menjadi 10 MB per satu file excelnya.Wah beratnya bukan main kalau saya buka dirumah karena prosessing komputer di rumah kecil Tapi dikantor sudah pakai Intel Core Duo jadi tidak begitu masalah. Ukuran file tersebut membesar karena banyaknya link, rumus rumus reference seperti vlookup, hlookup, sumif, dst.... Dengan makin banyaknya data dan perhitungan yang akan dimasukkan lagi file tersebut mungkin akan menjadi 20 MB.
http://mymacrosformula.blogspot.com/
Sehingga saya mencari jalan keluarnya yaitu dengan Copy paste value, saya pecah file tersebut menjadi 3 s/d 4 file. Dan perpindahan perhitungan antar file menjadi value dengan macro dan copy dan paste di file satunya dengan macro atau AHK.
http://mymacrosformula.blogspot.com/
dan cara lainnya adalah dengan membagi file menjadi file khusus untuk perhitungan dan file khusus untuk laporan atau tampilan saja tanpa rumus. memisahkan filenya bisa dengan macro seperti yang saya bahas pada posting selnajutnya di :

http://mymacrosformula.blogspot.com/2012/10/mensiasati-file-yang-terlalu-besar-bag2.html

http://mymacrosformula.blogspot.com/