Range : Mengisi Formula

Prasyarat:

---

Salah satu fungsi dari penggunaan VBA adalah untuk mengisi formula pada lembar kerja Excel secara otomatis.

Pada materi kali ini, kita akan membahas bagaimana cara mengisi dan menggunakan Formula Excel pada Worksheet dengan menggunakan VBA.

Sama seperti didalam Worksheets Excel, didalam VBA kita juga bisa mengisi Cell dengan menggunakan 2 notasi Formula, yaitu Notasi A1 Formula dan Notasi R1C1 Formula.

Tetapi selain 2 notasi tersebut masih ada 3 fungsi lagi jika didalam VBA. Didalam VBA terdapat 4 fungsi formula yang bisa digunakan, yaitu :

Sebelum kita bahas semuanya, mari kita focus terlebih dahulu membahas 2 notasi utama, yaitu Notasi A1 (.Formula) dan Notasi R1C1 (.FormulaR1C1).

1. Notasi A1 Formula

Cara pertama yang umum digunakan adalah dengan menggunakan notasi A1, notasi yang umum dikenali dan banyak digunakan dalam penulisan formula.

Sintak untuk mengisi formula pada sebuah Sheet excel menggunakan VBA umumnya menggunakan script seperti berikut ini:

Rangenya.Formula = IsiDariFormula

Contoh:

Sub FillFormulas()
   Range("A1").Formula = "=SUM(B1:B10)"
End Sub

Pada script diatas diperintahkan untuk mengisi Range A1 dengan isi formula =SUM(B1:B10). Kamu bisa mengganti formula ini dengan formula apapun yang diinginkan sama seperti di Worksheet Excel.

Agar lebih memahami mari kita ke contoh kasus.

Jawaban :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Notasi A1
Range("B11").Formula = "=SUM(B3:B10)"

End Sub

Pada script diatas, Range B11 diisi dengan rumus =SUM(B3:B10).

2. Notasi R1C1 Formula

Untuk rumus yang menggunakan Notasi R1C1 tidak jauh berbeda dengan notasi A1, hanya berbeda pada notasi referensi cell rumusnya saja.

Berikut ini adalah contoh bagaimana mengisi formula dengan format Notasi R1C1.

Sub FillFormulas()
   Range("B5").FormulaR1C1 = "=SUM(R[1]C[1]:R[10]C[1])" 
End Sub

R adalah nilai yang mewakili Row dan C adalah Nilai yang mewakili Column, R[1] artinya baris bergeser 1 baris kebawah dari posisi B5. sedangkan jika R[-1] maka baris bergeser 1 baris keatas dari B5.

Jawaban :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Notasi R1C1
Range("C11").FormulaR1C1 = "=SUM(R[-8]C[0]:R[-1]C[0])"

End Sub

Pada script diatas, Range C11 diisi dengan Formula SUM untuk Rangenya dimulai Dari C11. R[-8] artinya baris bergeser dari C11 sebanyak 8 baris keatas yang artinya sama dengan C3.

Semua contoh-contoh diatas, tentu sangat mudah karena rumus yang dibuat tidak memiliki parameter dan juga inputan yang berbeda misalnya sebuah Text atau String dengan menggunakan tanda petik.

Formula Dengan Tanda Petik

Untuk formula yang berisi tanda petik didalamnya misalnya seperti =IF(B1="vba","ini vba","bukan") maka perlu ditambahkan Escape karakter untuk tanda petik, sehingga tanda petik pada rumus menjadi double.

Contoh rumus, bisa dibuat seperti ini .

Sub FillFormulas()
   Range("A1").Formula = "=IF(B1=""vba"",""ini vba"",""bukan"")"
End Sub

Maka hasilnya akan terlihat seperti dibawah ini:

Alternatif selain menggunakan Escape karakter, bisa juga menggunkan ASCII Code dari tanda petik yaitu Nilai 34. Maka rumus yang dibuat akan diganti setiap tanda petik dengan Chr(34).

Contoh penulisannya seperti dibawah ini:

Sub FillFormulas()
   Range("A1").Formula = "=IF(B1=" & Chr(34) & "vba" & Chr(34) & "," & Chr(34) & "ini vba" & Chr(34) & "," & Chr(34) & "bukan" & Chr(34) & ")"
End Sub

Mungkin cara dengan menggunakan ASCII code rumus akan lebih panjang dan lebih mudah menggunakan Escape Char.

Jawaban :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("D3:D12").Formula = "=IF(C3>75,""Lulus"",""Tidak Lulus"")"
End Sub

Catatan : untuk Excel dengan Regional Indonesia dimana pemisah rumusnya adalah titik koma ( ; ) , maka pada VBA tetap menggunakan koma sebagai pemisah argumen.

vba.co.id

Formula Local

Fungsi ini, pada intinya sama dengan 2 notasi diatas, baik itu FormulaLocal atau FormulaR1C1Local. Local disini mengacu pada bahasa local (local language) yang digunakan dalam Microsoft Office.

Beberapa versi bahasa Microsoft Office misalnya seperti Jerman atau Spanyol, tidak menggunakan rumus standar yang digunakan, misalnya untuk rumus IF, pada versi Jerman diubah menjadi =WENN() dan pada Spanyol berubah menjadi =SI().

Sehingga ketika didalam VBA membuat sebuah script seperti ini

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("D3:D12").Formula = "=WENN(A1=1;""Benar"";""Salah"")
End Sub

Maka script diatas akan menghasilkan Error.

karena itu jika akan menggunakan Formula dalam Bahasa Local maka harus menggunakan FormulaLocal

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("D3:D12").FormulaLocal = "=WENN(A1=1;""Benar"";""Salah"")
End Sub

Formula Local berbeda dengan UDF, Formula Local adalah Formula Bawaan Excel yang menggunakan Bahasa Regional

vba.co.id

Menggunakan Formula di VBA

Pada pembahasan sebelumnya sudah membahas bagaimana mengisi formula ke worksheet excel, sehingga pada worksheet akan terlihat sebuah formula yang dibuat oleh VBA.

Bagaimana jika kita ingin menggunakan sebuah Formula dan hasil nilainya langsung yang dimasukan kedalan Worksheet?

Ada beberapa cara yang bisa dilakukan untuk menggunakan Formula di VBA.

Worksheet Functions

Untuk menjalankan Formula menggunakan VBA yang pertama, bisa menggunakan object dari WorksheetFunctions.

WorksheetFunction adalah sebuah objek yang memungkinkan akses ke berbagai fungsi bawaan dari aplikasi Microsoft Excel seperti SUM, AVERAGE, COUNT, MAX, MIN, dan banyak lagi.

Dengan menggunakan objek WorksheetFunction, kita dapat memanggil fungsi-fungsi Excel ini secara langsung dari kode VBA. Ini memberikan kemampuan yang kuat untuk melakukan manipulasi data dan perhitungan di dalam makro atau prosedur VBA.

Hirarki Object dari WorksheetFunctions bisa dilihat dibawah ini.

Dengan worksheetFunction rumus tidak lagi dituliskan di Cells tetapi langsung digunakan di VBA.

Contoh : Misalnya jika ingin melakukan SUM pada Rang B3 sampai B10

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Worksheet Function
Range("B15").Value = WorksheetFunction.Sum(Range("B3:B10"))

End Sub

Pada script diatas, Range(“B3:B10”) dimasukan kedalam WorksheetFuntion dengan Fungsi SUM dan hasilnya disimpan di B15.

Jawaban :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("H15").Value = WorksheetFunction.SumIf(Range("G3:G12"), "Nama 1", Range("H3:H12"))

End Sub

Selain dengan menggunakan WorksheetFunction, beberapa pengguna VBA juga seringkali menggunakan Evaluate baik itu dari Application.Evaluate atau menggunakan Short Evaluate.

Namun cara ini adalah cara kurang baik untuk dilakukan.

Application Evaluate

Dengan menggunakan Aplication Evaluate, rumus ditulis sebagaimana mengggunakan rumus di Worksheets, misalnya jika rumusnya adalah “=SUM(B3:B10)” , maka script bisa dibuat seperti ini.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Evaluate
Range("B11").Value = Application.Evaluate("=SUM(B3:B10)")

End Sub

ketika script dijalankan, maka pada range B11 akan berisi hasil nilai dari SUM. Bukan lagi berbentuk rumus tetapi Nilai langsung.

Short Evaluate

Cara ini adalah versi pendek dari Application Evaluate yaitu menggunakan operator [ dan ]. Text yang berada didalam [ ] sudah dianggap sebagai TEXT sehingga setiap Text yang dimasukan tidak perlu lagi ditambahkan tanda petik ( ” ” ).

Misalnya rumus SUM yang tadi, akan diubah menjadi Short Evaluate maka scriptnya menjadi :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Short Evaluate
Range("B11").Value = [=SUM(B3:B10)]

End Sub

Contoh lain, jika rumusnya memiliki tanda petik, maka bisa dibuat seperti ini.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("H15").Formula = [=SUMIF(G3:G12,"Nama 1",H3:H12)]

End Sub

Unduh Excel File Contoh

Jika kamu ingin mendapatkan salinan file Excel yang saya gunakan pada materi ini, silakan klik tombol unduh di bawah ini.