You've probably noticed

We love Excel

Author

When some user experience severe excel problem, it is not uncommon for their job activity to disturbed.
My great passion is bringing solution to some user who have been trought those experience, so they can save their times fixing excel issues.

  • myexcelive@gmail.com
  • www.excelive.com
  • READ MORE
Me

What will you get here

We have been work hard to bringing something useful for you in order to help your excel issues

Macro VBA 70%
Formula Excel 45%
Tips and Tricks 15%
Miscellaneous 25%

Expertise

We share the tutorials that we ourselves have tested in the workplace and slightly refine it to make it easy for you

Our Article

Every article on this site is presented explicitly so that you can understand the purpose of each tutorial.

Efficiency

We provides examples of just for illustration only, and we do not guarantee that they can be used in all situations.

Installation

every each codes on this site are provided "as is" and are easy to install

Youtube

in order to facilitate you in learning excel, you can visit our channel here

Design

all the design of each tutorial has passed the test for excel 2007 up

Tampilkan postingan dengan label Macro VBA. Tampilkan semua postingan
Tampilkan postingan dengan label Macro VBA. Tampilkan semua postingan
  • Cara Copy Paste Special Value di Excel Secara Cepat Menggunakan Macro VBA

    copy paste special macro vba Pernah dengar istilah paste special ?!
    Ya, fitur ini adalah salah satu fitur andalan dari Microsoft Excel yang memungkinkan pengguna Excel untuk menempelkan hasil data yang disalin sesuai dengan opsi atau hasil yang diinginkan. Salah satu contohnya adalah ketika kita ingin melakukan pengambilan angka (hasil akhir) dengan menggunakan perintah copy-paste dari hasil sebuah formula sederhana.

    Perhatikan contoh data berikut ini
    formula hitung sederhana

    Dengan menggunakan tombol pintas CTRL + V untuk menempelkan hasil dari sebuah formula,maka hanya akan menempelkan referensi sel dan bukan hasil akhir, yang mana Hal ini tentu akan berpengaruh jika nantinya terdapat perubahan sel atau referensi.
    Tutorial terkait lain
    Skrip Macro VBA untuk menyalin sebuah range
    Solusi membuat ranking berdasarkan kelompok atau kelas tertentu
    Membuat nomor transaksi diawali dengan angka 000

    Inilah gunanya kita menggunakan fitur tempel spesial untuk menghindari perubahan hasil akhir dari data yang disalin.
    Opsi tempel spesial atau Copy Paste special bisa dipanggil (setelah anda melakukan perintah menyalin data) dengan menekan tombol tekan Ctrl + Alt + V, atau Alt + E + S untuk membuka dialog Tempel Spesial

    fitur tempel khusus excel
    Selanjutnya, silahkan tentukan pilihan perintah untuk menempelkan hasil salinan, dan akhiri dengan menekan tombol OK.

    Alternatif lain untuk melakukan perintah copy paste special dari contoh data di atas dengan lebih cepat tentu saja dengan menggunakan Macro VBA. Triknya adalah sebagai berikut :
    1. Aktifkan tab Developer jika belum, selanjutnya pilih Insert --> CommandButton (ActiveX Control) dan letakkan di lembar kerja Microsoft Excel anda

    2. Klik ganda objek VBA yang barusan anda buat untuk langsung menuju ke jendela Microsoft Visual Basic Editor
    3. Tempelkan kode berikut ini agar Tombol bisa berfungsi sebagaimana mestinya
      On Error GoTo x
      Dim a As Range, b As Range
      
      Application.DisplayAlerts = False
      Set a = Selection
      Set b = Application.InputBox("rangenya", Type:=8)
      a.Copy
      b.PasteSpecial xlPasteValues
      Application.CutCopyMode = False
      x:
      Exit Sub
      Secara singkat penjelasan baris kode di atas adalah sebagai berikut :
      • Membuat perangkap error untuk menghentikan jalannya kode jika terjadi kesalahan
      • Mendeklarasikan variabel a dan b sebagai sebuah range
      • Menonaktifkan pesan peringatan Microsoft Excel
      • Mengatur variabel a untuk memilih beberapa sel
      • Menjadikan variabel b sebagai jendela InputBox untuk menempelkan hasil salinan
      • Setelah serangkaian syarat tersebut dipenuhi, selanjutnya adalah menyalin isi dari variabel a kemudian menempelkan ke sebuah sel atau range sesuai dengan nilai atau alamat yang ditentukan oleh variabel b, dengan menggunakan opsi xlpasteValues
    4. Tutup jendela Microsoft Visual Basic Editor atau tekan tombol pintas Alt+Q untuk kemebali ke jendela Aplikasi Excel
    5. Sebelum menjalankan kode yang sudah dibuat. Pastikan anda menonaktifkan tombol Design Mode yang terdapat di tab menu Development
      design mode development
    Hasil akhir dari serangkaian tutorial membuat tombol Copy Paste Special menggunakan Macro VBA, bisa anda lihat dalam tampilan berikut ini
    copas spesial excel


    Selamat...Anda sudah berhasil melakukan perintah copy paste special dengan memilih opsi nilai murni dengan menggunakan bantuan Macro VBA Excel.
    Jangan lupa, silahkan kunjungi channel youtube kami untuk melihat video tutorial terbaru. Adios

  • Cara Menyembunyikan atau Menampilkan Lembar Kerja Jika Sel Berisi Nilai Tertentu

    hidden sheet dengan macro vba Lembar kerja Ms.Excel atau biasa disebut juga dengan Worksheet merupakan sebuah wadah di mana kita bisa menyimpan berbagai macam data di dalamnya. Mulai dari data berupa teks, angka, hingga formula yang dapat membantu kita dalam mempercepat pekerjaan.

    Dari setiap lembar kerja (baca:sheet) yang kita miliki dalam dokumen Microsoft Excel, mungkin Anda pernah mengalami situasi, kondisi atau keadaan dimana kita harus menyembunyikan beberapa hal yang kita rasa mengganggu pada lembar kerja kita, dan atau mungkin kita perlu menyembunyikan data-data rahasia yang terdapat dalam satu atau beberapa lembar kerja tersebut, sehingga anda tidak ingin lembar kerja itu terlihat di bilah tab di bagian bawah jendela Excel.

    Bahkan, Anda dapat menyembunyikan seluruh buku kerja, meskipun kami tidak yakin mengapa Anda ingin melakukan itu. Namun, kami akan menunjukkan cara menyembunyikan lembar kerja dan buku kerja dan menampilkannya lagi.
    Baca tutorial terkait lain
    Cara mengisi TextBox dan menulisnya ke dalam lembar kerja Excel
    Panduan mencetak dokumen dengan disertai jumlah banyaknya cetakan
    Daftar kode-kode untuk merubah interior sel dalam lembar kerja Excel

    Apa yang akan saya tunjukkan dalam tutorial ini adalah, bagaimana cara menyembunyikan dan atau menampilkan kembali buku kerja yang kita miliki jika sebuah sel memuat nilai tertentu. Dan berikut panduannya :
    1. Pastikan Aplikasi Microsoft Excel sudah anda aktifkan
    2. Buat atau tambah beberapa lembar kerja dalam dokumen Excel Anda
    3. Sebagai contoh, masukkan nilai atau angka 1 (satu) di lembar kerja ke-2 hingga lembar kerja terakhir yang anda miliki.
      Hal ini nantinya kita gunakan untuk sebagai kunci untuk menyembunyikan dan atau menampilkan kembali lembar kerja yang hilang dari bilah tab di bagian bawah jendela Excel
    4. Tekan tombol pintas Alt+F11 untuk menuju ke jendela Aplikasi Microsoft Visual Basic Editor
    5. Selanjutnya, buat sebuah module baru
      Tambah module baru di excel
      Lanjutkan dengan copy-paste kode berikut ini ke dalam module yang sudah dibuat :
      Sub ABC
      Dim wb As Worksheet
      For Each wb In ActiveWorkbook.Worksheets
          If wb.[a1] = 1 Then
              wb.Visible = xlSheetHidden
          End If
      Next ws
      End Sub
      Penjelasan singkatnya Sebelum menjalankan kode, terlebih dahulu saya mendeklarasikan variabel wb sebagai lembar kerja. Selanjutnya saya menggunakan statement For Each untuk melakukan perintah pengulangan yang mana kriteria pengulangannya adalah "Jika sel A1 di setiap lembar kerja berisi angka 1 - Maka lembar kerja akan disembunyikan"
      Tentunya anda bisa menampilkan kembali lembar kerja yang hilang dengan mengganti perintah xlSheetHidden menjadi xlSheetVisible
    6. Langkah terakhir adalah menguji serangkaian kode ini dengan cara menekan tombol yang terdapat di menubar atau dengan menekan tombol F5 yang terdapat dikeyboard anda

    Hasil akhir dari serangkaian tutorial ini bisa anda lihat dalam tampilan berikut
    menyembunyikan lembar kerja excel


    Selamat...anda sudah berhasil menampilkan dan atau menyembunyikan beberapa lembar kerja dokumen Microsoft Excel Anda berdasarkan nilai yang terdapat disebuah sel menggunakan Macro VBA Excel.
    Jangan lupa, silahkan kunjungi channel youtube kami untuk melihat video tutorial terbaru dari kami. Gracias


  • Solusi Untuk Memformat Efek Teks Dari Hasil Formula Excel

    bold effect within formula excelMembuat sebuah teks atau angka menjadi tebal (bold), miring (italic) dan bergaris bawah (underline) pada microsoft excel berfungsi untuk memberikan penekanan pada kata atau kalimat tersebut atau bisa juga untuk memperjelas kata dan kalimat pada tulisan kita. Pada umumnya cara membuat efek seperti ini bisa dilakukan dengan 2 (dua) cara yaitu:
    1. Menggunakan menu home dan memilih sub menu bagian font
    2. Atau anda juga bisa menggunakan tombol shortcut pada keyboard

    Mengaplikasikan efek ini juga terbilang sangat mudah, dimana kita tinggal memilih sel yang ingin diberi efek atau memilih sebagian kata atau kalimat yang terdapat dalam sel tersebut kemudian melakukan salah satu dari dua cara di atas.

    Tapi sayangnya, ketika kita menggunakan Microsoft Excel dan ingin memberikan efek pada sebagian teks atau angka yang terdapat dalam atau hasil dari sebuah Formula Excel, maka hal ini tidak dapat dilakukan. Contoh; kita ingin memberikan efek tebal pada hasil formula =A1&B2 dengan cara memilih atau blok B2 pada rangkaian formula tersebut, maka Secara otomatis menu efek yang terdapat dalam sub menu Font akan dinonaktifkan. Untuk lebih jelasnya, silahkan lihat perbandingan cara mengaplikasikan teks efek pada tampilan berikut.
    teks effect dalam formula
    Jika anda perhatikan, pada tampilan sebelah kanan adalah sebuah sel yang memuat formula sehingga ketika kita hendak memberikan efek pada sebagian teks maka system akan langsung menonaktifkan pilihan efeknya.

    Kesimpulannya adalah, kita tidak bisa memformat sebagian teks atau angka dengan efek tebal, miring, atau garis bawah pada sel yang memiliki rumus atau formula didalamnya dengan menggunakan cara biasa. Artinya, anda membutuhkan bantuan Macro VBA jika tetap ingin memberikan efek-efek tersebut, dan berikut langkah-langkahnya.

    1. Buka jendela Microsoft Visual Basic Editor dengan cara menekan tombol pintas Alt+F11 pada keyboard Anda
    2. Silahkan buat sebuah module baru melalui menu Insert
    3. Lanjutkan dengan menulis baris kode berikut di dalamnya
      Sub efek()
      With [E1]
         .Formula = "=A1&B1&C1"
         .Value = .Value
         .Characters(Len([A1]) + 1, Len([B1])).Font.Bold = True
      End With
      End Sub
      Penjelasan singkatnya Kode VBA di atas berfungsi untuk merubah hasil formula yang terdapat di sel E1 yang mana isi sel ini didapatkan dari gabungan antara sel A1, B1, dan C1. Dan agar perubahan efek sebagian teks ini berhasil, maka disana saya 'memaksa' agar formula yang tertulis di sel E1 berubah menjadi nilai biasa.
      Baris selanjutnya adalah baris penting yang bertujuan untuk memberikan efek tebal, dengan ketentuan sebagai berikut :
      • Karakter yang ingin saya berikan efek tebal adalah karakter yang terdapat di sel B1, maka dari itu saya menuliskan formula sederhana untuk membaca panjang karakter A1 + 1
      • Kemudian, untuk jumlah karakter yang ingin diberikan efek tebal menggunakan rumus LEN([B1])
      • Kode VBA untuk membuat karakter tebal adalah Font.Bold = True
    Hasil akhir dari serangkaian tutorial ini adalah sebagai berikut:
    memberikan efek font sebagian teks


    Selamat mencoba dan selamat bereksperimen.

    Semoga tutorial singkat tentang cara merubah efek sebagian huruf pada formula excel ini dapat memberikan manfaat untuk anda. Terimakasih sudah berkunjung di excelive.com dan silahkan bookmark situs ini untuk memudahkan anda ketika akan mengunjunginya lain waktu. Adios


  • Membuat Kalkulator Konversi Ukuran File Sederhana Menggunakan UserForm

    kalkulator konversi ukuran file Secara umum, mungkin kalkulator yang akan kita buat pada kesempatan ini terbilang sudah cukup banyak beredar di dunia maya dan sifatnya online sehingga mengharuskan pengguna harus terhubung ke internet. Hal ini tentu saja menjadi salah satu kekurangannya, karena tidak semua pengguna selalu online setiap saat.

    Nah, pada tutorial ini kita akan mencoba untuk membuat kalkulator konversi ukuran sebuah file secara sederhana.
    Tutorial terkait lain yang bisa anda pelajari ada disini
    Mengisi beberapa daftar isi ListBox dari nilai sama sekaligus
    Cara membuat kalkulator hitung sederhana
    Kumpulan cara mengisi ListBox dan ComboBox
    Sebelumnya kita sepakati dulu jenis-jenis ukuran yang sudah umum dan sama-sama kita kenal, dan kita pasti sudah tidak asing dengan beberapa satuan ukuran file di komputer seperti KiloByte (KB), MegaByte (MB), GigaByte (GB) atau pun TeraByte (TB). Satuan satuan ukuran tersebut saat ini menjadi hal yang biasa atau lumrah dan sangat familiar di telinga kita, hal ini karena satuan tersebut sudah menjadi satuan ukuran yang sering kita jumpai baik itu sebuah ukuran gambar, video, hingga sebuah ruang penyimpanan seperti flash disk, microSD hingga Hard disk drive.

    Itulah 4 (empat) buah satuan ukuran file komputer yang akan kita konversi menggunakan Macro VBA Excel. Dan selanjutnya, silahkan ikuti langkah-langkah berikut untuk membuatnya.
    1. Buka Aplikasi Microsoft Office Excel, dan langsung aktifkan jendela Microsoft Visual Basic Editor (VBE) dengan menggunakan tombol pintas Alt+F11
    2. Setelah berada di jendela VBE, kini saatnya mendesain form kalkulator dengan cara klik menu Insert dan pilih UserForm
      Insert userform
    3. Selanjutnya silahkan tambahkan beberapa kontrol seperti gambar berikut ke dalam UserForm yang sudah berhasil dibuat dilangkah sebelumnya
      desain kalkulator konversi ukuran file
    4. Sampai langkah ini anda sudah berhasil membuat desain kalkulator konversi satuan ukuran file komputer, silahkan anda percantik tampilan sesuai dengan selera
    5. Langkah selanjutnya adalah langkah inti agar kontrol-kontrol yang terdapat dalam UserForm dapat berjalan sebagaimana mestinya. Mulailah dengan klik kanan nama UserForm yang terdapat di jendela VBAProject dan pilih menu ViewCode.
      Viewcode UserForm
      Selanjutnya silahkan copy kode berikut dan paste ke dalam jendela kode



      Private Sub UserForm_Activate()
      Dim i As Integer
      For i = 1 To 2
              Me.Controls("ComboBox" & i).List = Array("TB", "GB", "MB", "KB")
      Next i
      End Sub
      
      Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
      If (InStr("0123456789.", Chr(KeyAscii)) = 0) And _
      (KeyAscii <> 8) Then KeyAscii = 0
      End Sub
      
      Private Sub ComboBox1_Change()
      Select Case ComboBox1
          Case "TB"
              [a1] = 0
          Case "GB"
              [a1] = 3
          Case "MB"
              [a1] = 6
          Case "KB"
              [a1] = 9
      End Select
      End Sub
      
      Private Sub ComboBox2_Change()
      Select Case ComboBox2
          Case "TB"
              [b1] = 0
          Case "GB"
              [b1] = 3
          Case "MB"
              [b1] = 6
          Case "KB"
              [b1] = 9
      End Select
      End Sub
      
      Private Sub CommandButton1_Click()
      On Error Resume Next
      [C1] = [a1] - [b1]
      MsgBox "Hasil konversi" & vbCr & _
              Format(TextBox1, "#,##0.####") & _
              " " & ComboBox1 & " = " & _
              Format(TextBox1 / 10 ^ [C1], "#,##0.###########") & _
              " " & ComboBox2
      End Sub
    6. Selesai.... silahkan uji UserForm ini dengan cara menekan tombol yang terdapat di menubar. Dan hasil akhir akan tampak seperti tampilan berikut :
      kalkulator konversi ukuran file

    Selamat....Anda sudah berhasil membuat kalkulator konversi ukuran sebuah file sederhana menggunakan kode Macro VBA Microsoft Excel. Semoga tutorial ini dapat memberikan manfaat. Jangan lupa kunjungi kami lain waktu untuk mengetahui tips dan trik Microsoft Excel lainnya. Gracias

  • Cara Membuat Komentar Sel Berdasarkan Isinya Secara Otomatis

    insert comment in excel Ada cara mudah untuk mengetahui status sel, apakah ia berisi catatan tambahan atau yang lebih umum disebut dengan komentar atau tidak, karena Excel sudah memberikan perbedaan dari sisi tampilannya yakni sel yang berisi komentar memiliki tanda segitiga warna merah yang berada di pojok kanan atasnya. Hal ini tentu bisa sangat mempermudah pengguna untuk mengetahui bahwa sel tersebut pasti berisi informasi tambahan yang dimiliki oleh sel tersebut.

    Informasi tambahan ini bisa dibilang cukup efektif dalam memberikan informasi kepada pengguna tentang isi dari sel tersebut tanpa harus menuliskannya di sel-sel lainnya. seperti, informasi tentang cara menginput nilai, bagaimana dan dari mana isi sel itu didapatkan, formula yang terdapat di dalamnya, dari mana data di input, hingga informasi lainnya yang sifatnya hanya untuk sekedar mengingatkan pengguna lainnya. Dengan demikian, orang lain dapat memahami maksud isi sel tersebut dengan baik.

    Menuliskan komentar untuk sebuah sel-pun sangatlah mudah dilakukan dan hampir setiap pengguna bisa membuatnya, yakni tinggal klik kanan sel yang ingin diberi informasi tambahan, kemudian di menu Pop-Up yang muncul kita tinggal pilih perintah Insert Comment dan langsung bisa mengetikkan informasi yang diinginkan di kotak komentar.
    cara memberi komentar di sel

    Cara ini adalah cara yang biasa dilakukan secara manual. Artinya pengguna harus melakukan beberapa rangkaian aksi agar bisa membuat komentar terhadap sel yang dimaksud, sehingga hal ini tentu saja membutuhkan kejelian untuk memilih sel mana yang harus diberi catatan tambahan, terlebih jika anda mempunyai kriteria isi sel yang memiliki nilai tertentu saja yang harus dibuatkan komentar, ambil contoh seperti ini
    Ketika pengguna mengetik sebuah nilai yang berisi teks seperti, keren, oke, dan excel, maka sel-sel tersebut akan otomotis menampilkan catatan tambahan atau komentar berupa judul komentar dan isi dari sel tersebut, sementara jika nilai dalam sel tersebut selain dari kriteria yang disebutkan, maka status komentar akan dihapus
    Menyisipkan komentar secara otomatis seperti skenario di atas hanya bisa dilakukan menggunakan bantuan Macro VBA dengan menggunakan even Worksheet.Change, Hal ini karena disana terdapat sebuah aksi pengguna mengetik nilai kemudian komentar akan muncul jika sesuai dengan kriteria yang disebutkan.

    Bagaimana membuatnya?!...Jika anda sudah siap, silahkan ikuti panduannya berikut:
    1. Buka jendela Microsoft Visual Basic Editor
    2. Tentukan lembar kerja mana yang akan anda otomatisasi penulisan komentarnya. Ambil contoh, lembar kerja yang akan diotomatisasi adalah lembar kerja atau Sheet1, maka yang perlu anda lakukan adalah klik ganda objek Sheet1 yang ada di jendela VBAProject, kemudian klik even Worksheet dan prosedurnya pilih Change.
      sheet1 worksheet change
    3. Selanjutnya, copy-paste kode berikut ke dalam even yang sudah anda buat dilangkah sebelumnya
      On Error Resume Next
      If Target.Cells Like "*oke*" Then
      Target.ClearComments
      Target.AddComment "Satu :" & vbNewLine & Target.Value
          ElseIf Target.Cells Like "*keren*" Then
          Target.ClearComments
          Target.AddComment "Dua :" & vbCrLf & Target.Value
              ElseIf Target.Cells Like "*excel*" Then
              Target.ClearComments
              Target.AddComment "Tiga :" & vbCrLf & Target.Value
          Else
          Target.ClearComments
      End If
      Penjelasannya kurang lebih seperti berikut :Perintah diawali dengan statement Error, artinya jika terjadi kesalahan akan diabaikan. Selanjutnya adalah serangkaian logika IF bertingkat yang kondisinya sebagai berikut :
      • Setiap sel yang berisi teks (baca:kriteria) seperti, keren, oke, dan excel yang dikombinasikan dengan menggunakan wildcard, maka akan berisi komentar secara otomatis
      • jika ternyata sel-sel yang ditulis terdapat teks seperti di atas maka komentar sebelumnya maka akan dihapus (ClearComments) dan akan diganti dengan komentar baru
      • Komentar yang beru berisi kata-kata yang terdapat di baris AddComment dan dibaris berikutnya ditulis sesuai dengan isi sel yang memuat kriteria di atas
      • Statement untuk menambahkan baris baru adalah vbCrLf
      • Kriteri yang ditulis sifat adalah Case-Sensitive
    Tutorial sejenis ini bisa anda lihat dipembahasan tentang mewarnai sel dan font berdasarkan isi sel .

    Untuk menyempurnakan pembelajaran kali ini, seperti biasa saya akan memperlihatkan rangkaian kode yang telah dibuat dan bagaimana perubahan yang terjadi di lembar kerja
    memilih listbox dengan enter

    Selamat... anda sudah berhasil membuat komentar muncul secara otomatis ketika mengetikkan teks tertentu di lembar kerja, semoga memberikan manfaat untuk anda.
    Jangan lupa bookmark situs ini agar memudahkan anda dalam mengunjunginya lain waktu. Thanks for visit us

    Tutorial ini memuat Macro VBA, jadi pastikan hasil akhir dari tutorial ini disimpan menggunakan format Excel Macro-Enable Workbook
    .xlsm
    atau Excel Binary Workbook
    .xlsb


  • Cara Menjumlahkan Range Jika Teks Berisi Kata Tertentu

    calculate with SUMIF Masih seputar tutorial tentang Formula Excel, kali ini kita akan coba mendemonstrasikan bagaimana cara menjumlahkan sebuah rentang hanya untuk teks berisi kata tertentu saja. Dan karena kita bicara tentang operator penjumlahan, maka secara otomatis harus tersedia data berupa angka-angka yang nantinya akan diolah.

    Mungkin yang terlintas dalam benak anda ketika ingin menghitung sekumpulan data berdasarkan kriteria, maka nama formula yang langsung dingat adalah Formula SUMIF. Ya, karena ini merupakan gabungan dari Formula SUM yang berfungsi untuk menjumlah data-data berupa angka, serta formula IF yang bisa digunakan untuk menyelesaikan soal-soal logika. Sehingga gabungan formula ini bisa menjadi cukup powerfull dalam menyelesaikan perhitungan yang membutuhkan sebuah kondisi tertentu tanpa harus menggunakan formula IF standard.

    Sebagai contoh, saya mempunyai data-data seperti gambar berikut:
    Data karyawan gudang
    Secara sederhananya, ketika ingin menjumlahkan keseluruhan nominal dari data karyawan di atas adalah cukup menggunakan formula SUM.
    Namun bagaimana jika anda ingin menghitung berapa total nominal yang harus dikeluarkan untuk para Manager, atau para Kepala bagian?!. Sudah jelas jawabannya bukan menggunakan formula SUM, karena disana kita bisa melihat terdapat kriteria yang jadi patokan dalam menghitung jumlah nominal.

    Dan seperti yang sudah anda bayangkan sebelumnya, formula yang tepat untuk menyelesaikan hitungan di atas adalah dengan menggunakan formula SUMIF. Dan penulisan formula lengkapnya adalah seperti berikut:
    Formula Excel SUMIF
    =SUMIF(A2:A9;"*"&D1&"*";B2:B9)Sebagai contoh, anda bisa menuliskan formula ini di sel D2

    Bagaimana cara kerja formula ini ?
    • Bagian yang berwarna ungu adalah sebuah rentang yang berisi nama-nama departemen
    • Kunci dari formula di atas terletak pada bagian kedua (warna merah), karena pada bagian ini menyimpan informasi kriteria yang akan dilakukan penghitungan.
      Dan untuk contoh ini, saya menggunakan lokasi sel D1 sebagai tempat untuk menuliskan nama-nama divisi yang ingin saya jumlahkan. Sementara dalam formula tersebut, sel D1 diapit oleh tanda bintang. Ini artinya bahwa setiap kata yang saya tuliskan akan mencocokkan atau mencari frasa parsial daripada keseluruhan string atau teks yang ada di sel D1.
      Model penulisan ini lebih umum di kenal dengan nama WILDCARD

      Tutorial lain yang membahas tentang Wildcard silahkan baca disini:
      Mencari data dengan cepat
      Hasil Error HLOOKUP
      Menjumlah data antar sel
      Melakukan pencarian berulang-ulang
    • Bagian terakhir atau bagian yang berwarna magenta adalah sekumpulan data-data yang berisi angka yang akan dijumlahkan jika kriteria yang berada di bagian kedua sesuai

    Agar lebih mudah memahaminya, silahkan lihat hasil akhir berikut :
    memilih listbox dengan enter

    Selamat, anda sudah berhasil menghitung data berdasarkan kata yang terdapat dalam sebuah range. Semoga tutorial yang cukup singkat ini bisa menambah referensi penggunaan rumus SUMIF.
    Jangan lupa bookmark situs ini untuk memudahkan mengunjungi kami lain waktu. Ciao Adios

  • Kode Untuk Merubah Warna Interior dan Font yang Terdapat Di sebuah sel

    mewarnai sel di excel Setiap sel yang terdapat dalam lembar kerja Microsoft Excel, ibarat sebuah lembaran kecil yang siap untuk kita isi dengan berbagai macam jenis data, bisa berupa teks, angka, simbol-simbol, atau mungkin data berupa kalender atau jam. Artinya bahwa sel yang semula kosong bisa kita isikan dengan berbagai jenis data-data yang siap kita olah menjadi sebuah informasi yang dapat disesuaikan dengan kebutuhan.

    Secara ukuran, baik sel yang berisi data maupun yang kosong, keduanya sama-sama memiliki ukuran yang sama, begitu juga dengan warna interiornya. Hingga versi terbaru yang saat ini diluncurkan atau sampai posting ini diterbitkan, Microsoft Excel tidak memberikan fitur khusus untuk memberikan tanda atau warna terhadap sel yang memiliki isi atau tidak sebagai pembeda antara kedua. Dan untungnya, masalah kecil ini bisa dengan mudah kita buat sendiri, baik dengan menggunakan Conditional Formatting maupun menggunakan Macro VBA.

    Dan untuk tutorial kali ini, saya menggunakan script Macro VBA agar model warna interior sel lebih bervariatif dengan mengikuti kondisi yang harus dipenuhi sebagai berikut
    Jika sebuah sel berisi angka 0 maka sel tersebut akan berwarna merah, jika diisi hanya berupa karakter spasi saja maka sel akan berwarna kuning, selain dari itu maka warna interior sel akan kembali ke warna asal - termasuk ketika user menghapus isi data dalam sel tersebut menggunakan tombol Delete

    Ok, setidaknya terdapat 4 (empat) buah kondisi yang kita miliki untuk dijadikan sebagai kriteria pewarnaan interior sebuah sel beserta warna dasar font-nya. Dan untuk menjalankan setiap kondisi yang ada ini, saya lebih suka menggunakan Statement IF (meskipun bisa saja menggunakan Select Case). Dan karena model pewarnaannya terjadi setiap kali ada perubahan, maka prosedur yang saya lakukan adalah sebagai berikut :
    1. Aktifkan terlebih dahulu Microsoft Visual Basic dengan menekan tombol pintas Alt+F11
    2. Lanjutkan dengan memilih nama-nama dari Object VBA yang terdapat di pojok kiri atas aplikasi Microsoft Visual Basic Editor.
      Jika perintah hanya ingin terjadi di salah satu lembar kerja saja, maka pilih (klik ganda) nama lembar kerjanya. Namun jika ingin agar perintah dapat dieksekusi di semua lembar kerja maka pilih (klik ganda) objek ThisWorkbook
    3. Sebagai contoh, objek lembar kerja yang akan saya beri perintah pewarnaan interior adalah Sheet1. Maka langkah selanjutnya silahkan alihkan perhatian ke jendela utama aplikasi Microsoft Visual Basic Editor, kemudian pilih objek Worksheet dan Change dari masing-masing menu dari kotak dropDown yang tersedia
      Worksheet chage procedure
    4. Setelah berhasil menentukan jenis prosedur yang akan dieksekusi, selanjutnya adalah tulis kode berikut di dalamnya
      On Error Resume Next
      If Target.Cells = "" Then
      Target.Interior.Color = xlNone
      Target.Font.Color = 1
          ElseIf Len(Trim(Target.Cells)) = 0 Then
          Target.Interior.Color = RGB(250, 250, 100)
              ElseIf Target.Cells = 0 Then
              Target.Interior.Color = RGB(250, 100, 100)
              Target.Font.Color = RGB(255, 255, 255)
          Else
          Target.Interior.Color = xlNone
          Target.Font.Color = 1
      End If
      
      Penjelasan singkat baris kode di atas adalah sebagai berikut
      • Membuat sebuah Statement yang bertujuan untuk tetap melanjutkan perintah jika terdapat error dalam baris kode yang telah dibuat
      • Baris kode inti dari tutorial ini terletak di statement Logika IF yang berbunyi, jika perubahan terhadap sel target berupa :
        • "" artinya kosong, maka warna interior bernilai xlNone atau default
        • Jika Panjang isi sel setelah dihapus karakter spasi yang terdapat diawal teks adalah 0 maka warna interior berubah menjadi warna kuning (kombinasi warna adalah RGB)
        • Jika sel berisi nilai 0 maka warna interior berubah menjadi warna merah sementara warna font menjadi putih
        • Jika kondisi selain dari pada kriteria di atas, maka warna interior kembali ke bentuk asal dan warna font berubah menjadi warna hitam
    5. Rangkaian dari kode-kode di atas jika dieksekusi, maka perubahan akan langsung terlihat dalam lembar kerja dimana kode ini diletakkan.
      warna interior dan font

    Selesai....
    Semoga tutorial singkat dalam mewarnai interior sel setiap kali ada perubahan yang dilakukan, dapat memberi manfaat kepada anda.
    Jangan lupa bookmark situs ini untuk memudahkan anda dalam mengunjungi kami lain waktu. Au revoir

    Tutorial ini memuat Macro VBA, jadi pastikan hasil akhir dari tutorial ini disimpan menggunakan format Excel Macro-Enable Workbook
    .xlsm
    atau Excel Binary Workbook
    .xlsb
  • Cara Mudah Mencari Lokasi Baris atau Kolom Dari Beberapa kumpulan Nilai

    search rows and column Sudah jelas bahwa ketika kita bekerja menggunakan Excel, proses memasukkan data-data ke dalam lembar kerja adalah sebuah prosedur awal yang harus kita kerjakan sebagai syarat mutlak agar data tersebut bisa diolah untuk mendapatkan informasi yang kita inginkan.

    Sementara dalam proses menginput data ke dalam lembar kerja, antara pengguna satu dengan lainnya pasti ada perbedaan untuk memilih lokasi awal dalam menempatkan data-data mereka. Sebagian memilih baris dan kolom ke-1 (Sel A1) untuk memulai pekerjaannya, sebagian lagi memilih sel B2, dan seterusnya, tergantung dari kebutuhan tiap-tiap individu.

    Misalnya, saya memilih kolom B yang dimulai dari baris kedua (sel B2) untuk menuliskan nomor urut, selanjutnya di kolom C saya isikan data-data berupa nama barang, sementara di kolom D saya gunakan untuk menulis harga dari tiap-tiap barang. Sehingga jika dibuat akan tampak seperti tampilan berikut:
    daftar harga barang

    Inilah sekumpulan data sederhana yang selanjutnya dapat kita olah untuk mencari informasi yang kita inginkan; seperti menjumlah total harga barang, menghitung banyaknya item yang dimiliki, melihat nilai harga tertinggi atau terendah dari data-data tersebut, dan lain sebagainya.

    Jika berurusan dengan mengolah data seperti yang saya sebut di atas, saya yakin anda pasti sudah paham dan bisa melakukannya. Akan tetapi, bagaimana jika anda ingin mengetahui posisi baris atau kolom di mana nilai tersebut dituliskan ?!. Ambil contoh, kata "Mouse" yang berada di baris ke-5 dan berada di kolom C, sementara nilai "70.000" berada di sel D6, dan seterusnya. Bagaimana mendapatkan alamat sel, atau baris, atau kolomnya ?!

    Cara mudah yang saya tawarkan kepada anda adalah dengan menggunakan Macro VBA, dan berikut ini panduan lengkapnya.
    1. Aktifkan atau pilih tab Developer jika belum, kemudian pilih ikon View Code. Atau cara cepatnya bisa anda lakukan dengan menggunakan kombinasi tombol keyboard Alt+F11 untuk membuka jendela Microsoft Visual Basic Editor (VBE)
    2. Setelah anda berhasil membuka aplikasi VBE, selanjutnya adalah klik 2x nama lembar kerja di mana anda meletakkan data-data yang sudah anda buat. Untuk tutorial ini saya memilih Sheet1 pada bagian bagian Microsoft Excel Objects, karena data-data yang saya miliki berada di lembar kerja 1.
      objek vba sheet1
    3. Di jendela sebelah kanan (jendela code), pilih Object Worksheet dan prosedur Change dari kotak dropdown
      memilih objek dan prosedur vba
      Maka secara otomatis anda akan dibuatkan sebuah judul prosedur lengkap dengan elemen penutup seperti gambar berikut

      Tujuan dari prosedur ini adalah untuk menjalankan perintah-perintah (langkah ke-4 dan 5) jika terjadi perubahan pada sel yang dipilih
    4. Langkah selanjutnya, Masukkan baris kode berikut ke dalam prosedur yang sudah dibuat sebelumnya dan letakkan sebelum baris kode End Sub
      If Not Intersect(Target, Range("G2")) Is Nothing Then
      Call CARI
      End If
      Perintah ini hanya dieksekusi jika terjadi perubahan terhadap isi sel G2 saja, dan kemudian menjalankan perintah CARI. Untuk alamat sel, Silahkan anda ganti sesuai dengan kebutuhan anda.
    5. Langkah selanjutnya adalah menuliskan secara manual isi perintah kode CARI seperti berikut dan diletakkan sebelum atau sesudah kode di atas
      Sub CARI()
      On Error GoTo ZZ
      Set A = Range("C2:D8").Find([G2], LookAt:=xlValue)
      If [G2] = vbNullString Then
          [G3] = "--"
          Else
          A.Row
      End If
      Exit Sub
      ZZ:
      [G3] = "Data tidak ada"
      End Sub
      Secara singkat, maksud dari Kode di atas adalah :Menentukan statement jika terjadi error maka akan menjalankan perintah ZZ, kemudian menentukan nilai A yang tujuannya untuk mencari berdasarkan nilai-nilai yang terdapat di range C2:D8 dengan kata kunci pencarian yang diletakkan di sel G2.
      Jika kata kunci yang dimasukkan ditemukan, maka isi sel G1 akan menunjukkan posisi baris sesuai dengan letak kata kunci tersebut.
      Jika kata yang dicari tidak ditemukan, maka perintah ini akan menjalankan baris kode ZZ: yang isinya akan menulis sebuah teks "Data tidak ada" di sel G3. Sementara jika sel G2 kosong, maka isi sel G3 berupa simbol --

      Tampilan lengkap serangkaian kode akan terlihat seperti gambar berikut :
      Hasil akhir penulisan kode

    6. Langkah terakhir adalah tutup jendela Microsoft Visual Basic Editor dengan menekan tombol X yang berada di pojok kanan atas, atau bisa juga menggunakan menu File --> Close and Return to Microsoft Excel
    7. Sekarang saatnya menguji kode yang telah dibuat dengan cara menuliskan kata kunci di sel G2 dan lihat hasilnya di sel G3
      Hasil akhir mencari lokasi baris

    Selamat....Anda sudah berhasil mencari lokasi alamat sel dari beberapa kumpulan nilai yang terdapat di lembar kerja Microsoft Excel dengan menggunakan statement ROW Macro VBA.
    Terimakasih atas kunjungan anda. Gracias

    Tutorial ini memuat Macro VBA, jadi pastikan hasil akhir dari tutorial ini disimpan menggunakan format Excel Macro-Enable Workbook
    .xlsm
    atau Excel Binary Workbook
    .xlsb

  • Cara Cepat Membuat Matrik Rencana Kegiatan Menggunakan Macro VBA

    jadwal kegiatan Matrik adalah sebuah informasi yang disampaikan serta ditampilkan dalam bentuk kolom dan baris yang tak ubahnya seperti lembar kerja dalam aplikasi Microsoft Excel, maka dari itu hampir mayoritas para pembuat rencana kegiatan bentuk matrik ini mengandalkan bantuan aplikasi Excel dalam merancang usulan rencana program kegiatan dengan tujuan agar segala apa yang dirancang bisa diselesaikan sesuai dengan jadwal yang ditentukan.

    Beberapa alasan dari mereka yang menggunakan matrik kegiatan sebagai acuan pelaksanaan program antara lain adalah kemudahan dalam melakukan kontrol kegiatan serta membantu dalam pengambilan keputusan.

    Terlepas dari berbagai macam alasan yang ada, kali ini excelive.com akan membantu anda dalam merancang dan membuat matrik kegiatan secara cepat dilengkapi dengan warna-warna yang menunjukkan tanggal pelaksaan kegiatan dengan menggunakan aplikasi Microsoft Excel.

    Sebelum memulai pembelajaran kali ini, ada baiknya anda lihat hasil akhir dari apa yang akan kita kerjakan nanti:
    Matrik rencana kegiatan
    Tampilan di atas saya buat dengan cara menyusun terlebih dahulu struktur matrik dalam lembar kerja Microsoft Excel dalam rentang satu bulan, beberapa diantara point penting dalam merancang matrik di lembar adalah :
    • Kolom A : berisi sebuah nomor urut kegiatan
    • Kolom B : berisi deskripsi singkat dari kegiatan yang akan dilakukan
    • Kolom C : berisi tanggal awal kapan kegiatan tersebut akan dilaksanakan, dan
    • Kolom D : berisi tanggal akhir kegiatan
    Sementara untuk kolom-kolom setelah kolom yang saya sebut di atas, saya isi dengan tanggal untuk memudahkan dalam membaca matrik

    Ok, saya berasumsi bahwa anda sudah merancang matrik kegiatan paling tidak seperti gambar di atas. Dan langkah selanjutnya adalah :
    1. Tekan kombinasi tombol Alt+F11 untuk mengaktifkan jendela Microsoft Visual Basic Editor
    2. Selanjutnya klik menu Insert dan pilih perintah Module untuk memebuat sebuah module baru
    3. Isi module yang pertama berfungsi untuk menghapus semua isi dalam range tertentu dengan baris kode seperti berikut
      Sub Hapus()
      brs = Range("A4").End(xlDown).Row
      kol = Range("A4").End(xlToRight).Column
      Set cl = Cells(1, kol)
      hasil = Replace(cl.Address(False, False), "1", "") & brs
      
      rg = "E5:" & hasil
      'kode di bawah ini digunakan untuk menghapus interior, border dan isi dalam range yang terdapat di kode rg
      Range(rg).Interior.ColorIndex = xlNone
      Range(rg).Borders.LineStyle = xlNone
      Range(rg).ClearContents
      End Sub
      Beberapa baris kode penting dari script Macro VBA di atas antara lain:
      • Hapus : mama sub module
      • brs : digunakan untuk mengetahui posisi baris terakhir yang berisi data
      • kol : sama seperti kode brs, namun berfungsi untuk mengetahui kolom terakhir
      • Set cl : digunakan untuk menentukan isi sel sesuai dengan kode yang terdapat di baris kol
      • hasil : bertujuan untuk mengkonversi (merubah) kode cl menjadi nama kolom
      • rg : berfungsi untuk menuliskan alamat range
    4. Berikutnya adalah membuat module kedua yang merupakan kode inti dari tutorial ini, yakni untuk menggambar kotak-kotak sesuai dengan tanggal awal dan akhir seperti berikut
      Sub matrik()
      Dim isi, hsl, kol1, kol2, Tg1, Tg2 As Variant
      Dim n As Long
      a = Range("A4").End(xlDown).Row
      
      Application.ScreenUpdating = False
      Call Hapus
      
      For n = 5 To a
      Tg1 = Cells(n, 3)
      Tg2 = Cells(n, 4)
      
      If Tg1 > 0 And Tg2 > 0 And IsNumeric(Tg1) And IsNumeric(Tg2) Then
          Set kol1 = Cells(1, Tg1 + 4)
          Set kol2 = Cells(1, Tg2 + 4)
          hsl = Replace(kol1.Address(False, False), "1", "") & n & ":" & _
                Replace(kol2.Address(False, False), "1", "") & n
          isi = Replace(kol1.Address(False, False), "1", "") & "4:" & _
                Replace(kol2.Address(False, False), "1", "") & "4"
          
          With Range(hsl)
              .Interior.Color = RGB(140, 180, 255)
              .Borders.ColorIndex = 56
              .Borders.LineStyle = xlContinuous
              .Borders.Weight = xlHairline
              'Baris kode pilihan untuk mengisi kotak dengan tanggal
              .Value = Range(isi).Value
              .Font.Size = 8
              .Font.Color = RGB(0, 0, 255)
          End With
      End If
      Next
      Application.ScreenUpdating = True
      End Sub
      Cara kerja secara umum dari kode inti ini adalah :Menentukan terlebih dahulu sebuah parameter dengan nama a untuk mendeteksi baris terakhir yang berisi sebuah nilai, kemudian mengawali baris perintah dengan menjalankan module hapus, dilanjutkan dengan baris kode untuk mulai membaca baris ke-5 hingga baris terakhir yang ditemukan oleh parameter a.

      Baris kode berikutnya adalah berfungsi untuk menguji beberapa sel dengan menggunakan statement logika dimana jika kolom Tgl1 dan Tgl2 berisi nilai yang lebih besar dari nol serta kolom-kolom tersebut berupa angka maka perintah akan dilanjutkan dengan mengeksekusi beberapa perintah berikut, antara lain:
      1. Menentukan jumlah sel yang terpilih berdasarkan hasil dari nilai Tgl1 dan Tgl2 masing-masing berjumlah 1 sel
      2. Kolom awal untuk mewarnai hasil temuan dimulai dari kolom E
      3. Mengkonversi bilangan kolom menjadi bentuk alphabet, dan
      4. Menggabungkan hasil konversi tersebut menjadi sebuah range
      Setelah ketentuan tersebut dibuat, baru menjalankan aksi utama dari tutorial ini diantaranya :
      1. Mewarnai alamat range dengan warna menggunakan mode RGB, dan
      2. Memberikan garis tepi berukuran xlHairline dengan warna menggunakan mode ColorIndex
      kemudian dilanjutkan dengan beberapa baris kode yang sifatnya pilihan, antara lain:
      1. Mengisi masing-masing sel yang ditemukan oleh kode hsl dengan baris kode yang ditentukan oleh nilai isi
      2. Memberikan ukuran serta warna huruf yang selanjutnya bisa diatur sesuai dengan selera
    Jika terjemahan kode yang saya rangkai di atas kurang begitu jelas atau bahkan terkesan membingungkan, mungkin dengan mengintip hasil akhir dari tutorial ini bisa membantu menambah pemahaman anda. Dan jika ingin mengoleksi tutorial ini, silahkan download file project-nya secara gratis untuk anda.

    Sebagai penutup tutorial dalam membuat rencana program kegiatan menggunakan bentuk dan model matrik, ijinkan saya untuk mengucapkan terimakasih untuk anda karena telah berkunjung ke situs excelive.com dan membaca ulasan ini dari paragraf awal hingga paragraf yang saat ini sedang anda baca.

    Mudah-mudahan xcelive.com bisa menjadi salah satu pilihan dan partner terbaik anda dalam menemani aktivitas pekerjaan mengolah data berbasis Ms.Excel. Danke

    Tutorial ini memuat Macro VBA, jadi pastikan hasil akhir dari tutorial ini disimpan menggunakan format Excel Macro-Enable Workbook
    .xlsm
    atau Excel Binary Workbook
    .xlsb


  • Gunakan Cara Ini Untuk Menampilkan Informasi Tanggal Terakhir Ketika Melakukan Proses Save

    Simpan informasi tanggal excelive.com - Selamat datang kembali di salah satu referensi alternatif tempat belajar Microsoft Excel. Salah satu situs yang membahas tentang berbagai macam tips dan trik Microsoft Excel untuk membantu anda dalam menyelesaikan pengolahan data secara cepat dan tepat.

    Berbicara tentang sesuatu yang membahas tentang alternatif memang tidak luput dari keyakinan atau selera masing-masing individu, artinya sesuatu tersebut bukan sebuah kewajiban yang harus dilakukan karena sifatnya hanyalah sebuah pilihan, seperti halnya tutorial yang akan saya share kali ini yakni tentang sebuah fitur yang bisa digunakan untuk mengingatkan kepada pengguna akan status terakhir kapan menyimpan dokumen Microsoft Excel, serta mengingatkan kepada pengguna bahwa dokumen telah mengalami perubahan.

    Ok, karena tutorial ini berhubungan dengan proses menyimpan serta perubahan yang dilakukan di lembar kerja - maka ada 2 (dua) prosedur yang menjadi sorotan, yakni prosedur BeforeSave dan SheetChange yang mana kedua prosedur tersebut bisa ditemukan di Project VBA, tepatnya di aplikasi Microsoft Visual Basic yang sudah terintegrasi dengan Microsoft Excel, dan anda bisa membuka aplikasi ini cukup dengan menekan tombol kombinasi Alt+F11 di keyboard.

    Setelah jendela Microsoft Visual Basic berhasil dibuka, langkah selanjutnya adalah
    1. Memilih salah satu Objek Microsoft Excel yang terletak di jendela sebelah kiri. Untuk contoh ini, saya memilih (klik 2x) ThisWorkbook sebagai lahan eksperimen. :)
    2. Jendela kode yang masih kosong inilah yang akan kita isi dengan beberapa kode di dalam prosedur yang sudah saya sebutkan di atas. Dan untuk lebih cepatnya, silahkan copy-paste kode berikut ke dalam jendela kode yang masih kosong
      Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
      Sheets(1).Range("A1") = Format(Date, "dd-mm-yy") & " | " & Format(Time, "HH:MM.ss")
      Application.StatusBar = [a1]
      End Sub
      
      Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
      Application.StatusBar = [a1] & " *)"
      End Sub
      
      Private Sub Workbook_WindowActivate(ByVal Wn As Window)
      Application.StatusBar = [a1]
      End Sub
      
      Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
      Application.StatusBar = ""
      End Sub
      
      Penjelasan singkat dari masing-masing prosedur di atas antara lain:
      • BeforeSave
        Perintah yang tertulis di dalamnya akan dijalankan saat prosedur menyimpan dokumen dilakukan oleh pengguna, dengan ketentuan mengisi sel A1 dengan informasi tanggal dan waktu yang saat ini sedang aktif.
        Kemudian, merubah tampilan status bar dengan informasi sesuai dengan apa yang tertulis di sel A1.
      • SheetChange
        Prosedur ini akan dijalankan ketika pengguna melakukan perubahan terhadap setiap lembar kerja yang terdapat dalam dokumen yang saat ini aktif, di mana setiap perubahan yang dilakukan akan menampilkan informasi berupa tanggal dan waktu namun diberi sebuah akhiran dengan simbol *)
      • WindowActivate
        Bertujuan agar setiap kali pengguna mengaktifkan dokumen ini, informasi yang tertulis di status bar akan terisi sesuai dengan nilai yang terdapat di sel A1
      • WindowDeactivate
        Berfungsi untuk mengembalikan informasi status bar ke settingan default disaat pengguna menutup (menonaktifkan) dokumen ini.

    Selain hasil perubahan dan informasi terkait waktu terakhir proses penyimpanan ditampilkan di StatusBar, teknik ini juga bisa diterapkan ke dalam sebuah userform dengan memberikan sedikit tambahan baris kode di dalamnya. Dan untuk hasil akhir, bisa anda lihat di tampilan berikut :
    Status informasi penyimpanan terakhir

    Untuk kode yang digunakan, silahkan kunjungi halaman download untuk mendapatkan file project dari tutorial ini.

    Terimakasih sudah berkunjung di excelive.com dan membaca ulasan terkait Status perubahan dan tanggal terakhir menyimpan dokumen Microsoft Excel. Semoga tulisan ini memberikan manfaat untuk anda. Thanks for visiting

    Tutorial ini memuat Macro VBA, jadi pastikan hasil akhir dari tutorial ini disimpan menggunakan format Excel Macro-Enable Workbook
    .xlsm
    atau Excel Binary Workbook
    .xlsb
  • Bagaimana Membuat Border atau Pembatas Secara Otomatis Sesuai dengan Kriteria isi Data Sel? Ini Caranya

    border otomatis Sebuah pertanyaan yang saya usung sebagai judul artikel ini memang sengaja saya tulis agar anda yang sedang ingin mengotomatisasi border dalam lembar kerja berdasarkan kriteria, bisa langsung mempraktekkannya tanpa membuang banyak waktu dalam mencari informasi sejenis yang banyak beredar di dunia maya.

    Dengan tanpa ada maksud untuk merendahkan kualitas tutorial sejenis yang diulas dan yang sudah banyak beredar tersebut, terkadang beberapa dari mereka hanya sekedar copy-paste dari situs lain demi meningkatkan jumlah pengunjung sebanyak-banyaknya tanpa melakukan uji coba terlebih dahulu terhadap tutorial yang sedang mereka tulis atau bahas di website tersebut. Ini juga tidak membuktikan bahwa tulisan yang sedang anda baca saat ini memiliki kualitas yang lebih baik dari mereka. Akan tetapi, saya berusaha semaksimal mungkin agar apa yang saya tulis dan bahas di setiap tutorial excelive.com, untuk mudah dipahami dan tentunya ada manfaatnya bagi siapa saja yang sedang membaca - yang pada akhirnya bukan saya yang akan mengatakan bahwa situs ini lebih baik dari mereka, tetapi anda.

    Tujuan awal dari apa yang akan saya bahas pada edisi kali ini berkaitan dengan efektifitas dalam memberikan garis pembatas atau yang lebih umum disebut dengan border pada lembar kerja agar bisa dilakukan secara cepat dan otomatis dibuat berdasarkan kriteria yang diinginkan oleh pengguna. Sekilas ketika kita mendengar kata-kata otomatis saat bekerja menggunakan Microsoft Excel, maka dalam pemahaman kita mungkin langsung menuju ke script kode VBA yang cukup kompleks. Tapi untuk kali ini, pemahaman tersebut tidak 100% benar, meskipun sebenarnya bisa saja dilakukan dengan script VBA. Tapi kita akan mencoba sesuatu yang lebih mudah dan sederhana terlebih dahulu, dan yang penting cocok dengan apa yang kita butuhkan.

    Anda juga bisa belajar dan mengkombinasikan tutorial ini dengan tutorial lain yang sudah saya bahas sebelumnya, seperti :
    Menjumlah setiap baris ke-n
    Cara menentukan baris akhir dalam Lembar Kerja Excel
    Menampilkan dan memberikan Ranking Berdasarkan kriteria

    Sebelum mulai belajar membuat garis pembatas otomatis, saya memiliki data penjualan sebuah produk yang masih polos tanpa ada pembatas yang saya tulis di lembar kerja Microsoft Excel. Dan berikut tampilan data yang saya maksud :
    data penjualan

    Versi sederhana dari tutorial ini bisa dilakukan tanpa mengunakan Macro VBA, yakni dengan menggunakan fitur Conditional Formatting persis dengan apa yang sudah saya bahas di tutorial Memberi warna background yang berbeda di setiap sel, hanya saja untuk bagian penulisan formula, ganti dengan formula seperti gambar berikut :
    conditional format
    Salah satu kelemahan menggunakan teknik ini adalah, garis pembatas yang dihasilkan tidak beraturan dan tentunya sangat menggangu dan mengurangi nilai keindahan dari sebuah tabel (gambar sebelah kanan).

    Untuk mengatasi ketidakaturan garis pembatas yang dihasilkan dengan menggunakan fitur Conditional Formatting, saya menggunakan bantuan Macro VBA yang mana rumusnya saya adopsi dari rumus di atas. Untuk melakukan hal ini, silahkan ikuti langkah-langkah pengerjaan berikut :
    1. Buka jendela Microsoft Visual Basic Editor dan buat sebuah module baru
    2. Setelah berhasil membuat module baru, langkah selanjutnya adalah tulis baris kode berikut di dalamnya
      Sub Pembatas()
      Dim a As Variant
      a = Application.WorksheetFunction.Match([F1], Range("A1:D1"), 0)
      Application.ScreenUpdating = False
      Range("A1:K100").Borders.LineStyle = xlNone
      For i = 1 To Range("A1").End(xlDown).Row
      If Cells(i, a) <> Cells(i + 1, a) Then
          With Range("A" & i & ":D" & i).Borders(xlEdgeBottom)
              .LineStyle = xlContinuous
              .Weight = xlMedium
          End With
      Else
          With Range("A" & i & ":D" & i).Borders(xlEdgeBottom)
              .LineStyle = xlContinuous
              .Weight = xlHairline
          End With
      End If
      Next
      
      Application.ScreenUpdating = True
      End Sub
      Penjelasan singkat kode di atas :
      • a
        Adalah sebuah nilai yang digunakan untuk menentukan indeks kolom yang sesuai dengan nilai yang ada di sel F1, sementara isi dari sel F1 dibuat menggunakan Data Validation
      • ScreenUpdating = False
        Baris kode ini bertujuan agar perubahan yang terjadi di layar tidak ditampakkan, sehingga dengan di set FALSE sebelum proses suatu prosedur maka akan menghilang tambahan waktu akibat proses screen updating. Setelah prosedur berakhir, screen updating di set kembali TRUE
      • Range("A1:C100").Borders.LineStyle = xlNone
        Berfungsi untuk menghapus semua border yang terdapat di range A1 sampai dengan C100
      • For i
        Ini adalah fungsi looping untuk membaca mulai baris 1 hingga baris terakhir yang memiliki nilai di sepanjang kolom A
      • If Cells(i, [J1])...
        Ini yang saya maksud dengan rumus yang saya adopsi dari Formula Excel, sementara a adalah sebuah indeks kolom yang saya gunakan untuk menentukan kriteria dalam menentukan garis pembatas.
      • Range("A" & i & ":D" & i).Borders(xlEdgeBottom)
        Digunakan untuk menentukan range yang akan diberi garis pembatas, dengan ketentuan yang ada di dalamnya.
      • xlMedium
        Membuat sebuah garis dengan ukuran medium
      • xlHairline
        Membuat sebuah garis dengan ukuran tipis
    3. Langkah berikutnya, saya menggunakan bantuan fitur Data Validation yang saya difungsikan untuk memilih kriteria garis pembatas, di mana caranya adalah sebagai berikut :
      • Sebagai contoh pilih sel F1 kemudian pilih tab menu Data dan pilih Data Validation
        (jika ingin mengganti alamat sel J1, maka pastikan baris kode Macro VBA sesuai dengan sel yang sudah diganti)
      • Dijendela Data Validation, pilih kategori List yang terdapat di tab setting, sementara untuk source : pilih range A1 hingga C1 dan akhiri dengan tombol OK
        data validation
    4. Untuk langkah ini sifatnya optional karena tujuannya hanyalah untuk mempermudah dalam melihat hasilnya saja, yakni menyisipkan sebuah tombol ActiveX Control yang bisa anda akses melalui menu Developer dan menempatkannya ke dalam lembar kerja Microsoft Excel, sementara untuk baris kode dari tombol ini, bisa anda gunakan script seperti ini
      Call Pembatas
    Penasaran dengan hasil akhir dari tutorial yang cukup panjang ini!!! Silahkan lihat tampilan berikut
    hasil akhir border otomatis

    Akhirnya kita sudah sampai dipenghujung tutorial untuk edisi membuat garis pembatas secara otomatis berdasarkan kriteria tertentu. Mudah-mudahan tutorial ini ada manfaatnya, dan bisa menjadikan situs ini menjadi salah satu parter Microsoft Excel yang terbaik yang selalu menemani anda. Grazie per la visita

    Tutorial ini memuat Macro VBA, jadi pastikan hasil akhir dari tutorial ini disimpan menggunakan format Excel Macro-Enable Workbook
    .xlsm
    atau Excel Binary Workbook
    .xlsb


  • Menggunakan Paremeter GoTo Untuk Melompat ke Kode Marco Lainnya

    statement GoTo Konsep sederhana dari apa yang akan kita pelajari bersama untuk edisi Macro VBA kali ini adalah, menjalankan baris kode dengan cara melewati serangkaian baris kode yang "tidak terpakai" dan mengakhiri atau menghentikannya setelah kode macro yang dimaksud dieksekusi. Hal ini cukup efektif dilakukan jika terdapat beberapa kode yang ingin dijalankan dalam satu module atau prosedur.

    Umumnya, parameter GoTo selalu disandingkan dengan parameter Logika IF yang berfungsi dalam mendeteksi suatu kondisi tertentu.

    Contoh skenario sederhana yang akan kita coba ujikan dalam memahami konsep GoTo adalah sebagai berikut:
    Jika Angka 10 dibagi angka yang berada di sel A1 menghasilkan nilai lebih kecil dari 5 maka Kode Macro yang akan dijalankan adalah perintah Satu, sementara jika hasil pembagian tersebut lebih besar dari 5 maka akan menjalankan Kode Macro Dua. Akan tetapi jika ternyata sel A1 tidak memuat nilai apapun atau kosong, dan atau hasil pembagian Error - maka perintah dengan nama variabel Tiga yang akan dijalankan

    Oke, dalam skenario di atas terdapat 3 (tiga) buah Statement yang akan dijalankan ketika kondisi terpenuhi, antara lain:
    Satu : Dijalankan ketika hasil pembagian lebih kecil dari 5
    Dua : Dijalankan ketika hasilnya adalah lebih besar dari 5
    Tiga : Dijalankan jika terjadi error atau sel A1 kosong
    dan, satu lagi statement yang harus ditulis yakni :
    Akhir : Bertugas untuk mengakhiri rangkaian parameter GoTo

    Dari ke-empat statement tersebut, maka ketika kita merangkainya dalam sebuah module VBA akan tampak kurang lebih seperti ini
    Sub CekGoTo()
    On Error GoTo Tiga
    isi = [a1]
    If 5 / isi < 5 Then
        GoTo Satu
        Else
        GoTo Dua
    End If
    
    Satu:
    MsgBox "nilai lebih kecil dari 5"
    GoTo Akhir
    
    Dua:
    MsgBox "nilai lebih besar dari 5 " 
    GoTo Akhir
    
    Tiga:
    MsgBox "error atau kosong"
    GoTo Akhir
    
    Akhir:
    End Sub
    Selanjutnya tutup jendela Microsoft Visual Basic Editor atau bisa dilakukan dengan menekan tombol pintas Alt+Q untuk langsung menuju ke lembar kerja Microsoft Excel. Di dalam lembar kerja yang sedang aktif, tambahkan sebuah Form Control berupa Button yang bisa anda akses dari Tab Menu Developer di bagian Insert - kemudian pilih kode yang akan dijalankan, untuk contoh kasus ini kode Macro adalah CekGoTo
    Insert Button on Sheet

    Tujuan dari dibuatnya Button adalah sekedar untuk memudahkan dalam mengecek hasil yang didapatkan, sehingga tidak harus bolak-balik membuka antar jendela aplikasi Excel dangan jendela aplikasi Visual Basic Editor. Dan hasil akhir dari apa yang kita kerjakan sesuai dengan panduan di atas akan tampak dalam ilustrasi berikut ini:
    Menggunakan parameter GoTo

    Selesai...
    Semoga panduan dalam menggunakan parameter GoTo ini cukup mudah untuk dipahami. Untuk hasil lain dari pengaplikasian parameter GoTo dibahas di dalam tutorial ini, silahkan anda cek dan pahami.
    Akhir kata, semoga kehadiran excelive bisa memberikan wawasan baru dalam mengolah angka serta dalam mengoperasikan Microsoft Excel khususnya dalam merancang kode Macro VBA. Gracias por visitar

    Tutorial ini memuat Macro VBA, jadi pastikan hasil akhir dari tutorial ini disimpan menggunakan format Excel Macro-Enable Workbook
    .xlsm
    atau Excel Binary Workbook
    .xlsb

  • Cara Mudah Memberikan Nilai Properti Berbeda Setiap Objek VBA Berdasarkan Kriteria

    ganti atribut objek VBA Sebenarnya penggunaan istilah Objek VBA hanya saya gunakan dalam mempermudah setiap pembelajaran Excel VBA. Dan seperti yang sudah diketahui bersama bahwa di dalam Microsoft Excel, objek-objek VBA seperti Label, ListBox, CommandButton, Frame, hingga SpinButton berada dalam kotak sebuah kotak yang bernama ToolBox yang berisi berbagai macam Controls seperti yang sudah saya sebutkan. Lihat gambar berikut
    Objek VBA

    Jadi, agar tidak menimbulkan makna yang ambigu - maka kata Controls saya ganti penyebutannya menjadi Objek VBA. Mudah-mudah hal ini bisa dimaklumi :)

    Ok, terlepas dari penggunaan serta penulisan istilah yang berbeda di atas, untuk tutorial kali ini xcelive akan share sebuah script Macro VBA yang sangat efektif untuk memberikan atribut atau properti yang berbeda-beda dari setiap objek VBA yang ada dalam UserForm.
    Dan sebelum mulai belajar membuatnya, ada baiknya anda baca terlebih dahulu tutorial terkait berikut:
    Tips dan trik menggunakan Macro VBA Excel
    Cara menggunakan properti TAG dalam Macro VBA
    Menggunakan perintah Looping dalam script VBA

    Skenario yang akan kita coba uji kali ini adalah seperti berikut
    Merubah properti warna latar 1 objek TextBox yang sesuai dengan kriteria yang saya inginkan dari beberapa TextBox yang terdapat di dalam UserForm
    Dari skenario tersebut, maka setidaknya kita harus menyiapkan terlebih dahulu design yang sesuai agar berhasil diterjemahkan menggunakan bahasa Macro VBA yang bisa dibilang cukup sederhana. Design yang saya maksud kurang lebih akan tampak seperti gambar di bawah ini
    design objek VBA
    Dari design di atas, saya menggunakan 7 buah TextBox utama yang nantinya akan berubah, sementara 1 buah TextBox lainnya berfungsi untuk mengisikan kriteria berupa angka 1 sampai 7 yang mewakili Objek VBA TextBox1 sampai TextBox7, dan yang terakhir saya menggunakan sebuah CommandButton untuk menjalankan kode yang saya tulis berikut ini :
    Private Sub CommandButton1_Click()
    Dim obj As Control
    For Each obj In UserForm1.Controls
        If obj.Name Like "TextBox" & TextBox8 Then obj.BackColor = &HC0C0&
    Next
    End Sub
    Baris kode di atas yang berfungsi untuk merubah warna latar dari TextBox yang memiliki Name yang sesuai dengan kriteria yang dimasukkan adalah obj.BackColor = &HC0C0&, sementara TextBox lainnya tidak akan mengalami perubahan warna latar. Hanya saja ketika anda mencoba mengganti kriteria beberapa kali, TextBox yang sudah berubah warna backgroundnya tidak akan kembali ke warna asal. Coba perhatikan demonstrasi berikut :
    Ganti BackGround Objek VBA

    Untuk mengatasi "error" ini, maka tambahkan baris kode berikut dan letakkan di baris bagian atas sebelum eksekusi perubahan warna latar dilakukan
    For i = 1 To 7
        Controls("textbox" & i).BackColor = &H80000005
    Next
    Sehingga jika kedua kode di atas digabungkan akan menjadi tampak seperti berikut
    Private Sub CommandButton1_Click()
    Dim obj As Control
    Dim Pesan As Boolean
    
    For i = 1 To 7
        Controls("textbox" & i).BackColor = &H80000005
    Next
    For Each obj In UserForm1.Controls
        If obj.Name Like "TextBox" & TextBox8 Then obj.BackColor = &HC0C0&
    Next
    End Sub
    
    Untuk melihat hasilnya, silahkan anda coba tekan tombol yang terdapat di menubar Visual Basic Editor, dan coba silahkan ganti nilai di kotak kriteria.

    Selamat...anda sekarang sudah bisa memberikan warna latar belakang yang berbeda setiap TextBox yang sesuai dengan kriteria. Jika anda bertanya, bagaimana cara menyisipkan sebuah kotak pesan ketika Textbox tidak ada yang sesuai dengan kriteria, maka struktur kode Macro VBA akan sedikit banyak mengalami perubahan. Untuk hasil akhirnya akan terlihat seperti berikut :
    Pilih Objek dengan paeameter GOTO

    Hasil di atas dikerjakan dengan menggunakan parameter GoTo, dan untuk pembahasannya akan saya posting di edisi berikutnya. Jadi, silahkan klik tombol Subscribe agar anda tidak ketinggalan tutorial xcelive berikutnya. See you soon

    Tutorial ini memuat Macro VBA, jadi pastikan hasil akhir dari tutorial ini disimpan menggunakan format Excel Macro-Enable Workbook
    .xlsm
    atau Excel Binary Workbook
    .xlsb
  • Still haven't found what are you looking for

    We have provided a complete list of articles on this site to make it easier for you and giving the best solution for your excel issues

    VISIT LINK
    DMCA.com Protection Status

    excelive.com
    is a blogger resources site who provides best excel tutorial based on daily needs. The main mission of excelive is to share our little experience in excel to deliver best time killer for your excel issues.
    T.O.S
    Term of service

    Read More
    Download Area
    to complete all excel lessons, we have provided a download link for you Find Here
    Copyright © 2016 - excelive.com. Some rights reserved.
    Menyalin sebagian atau keseluruhan materi situs hanya diperbolehkan dengan tautan balik terbuka ke sumbernya.
    equipped with by blogger