Kode vba untuk menyembunyikan rumus di excel tanpa memproteksi sheet

Makro ini akan menyembunyikan semua rumus di dalam buku kerja dan tidak mengizinkannya untuk dihapus. Halaman tidak akan dilindungi seperti lembar kerja biasa yang dilindungi di excel; . Anda dapat memasukkan rumus baru tetapi kemudian Anda tidak dapat menghapus rumus tersebut. Selain itu, jika Anda memilih sel dengan rumus, konten sel tersebut tidak akan ditampilkan. Ini memungkinkan Anda menyembunyikan formula dari pengguna dan menambahkan tingkat keamanan ekstra. Jika Anda ingin mengedit sel tertentu, Anda harus mengklik sel itu dan kemudian "Tampilkan" sel itu dengan mengetikkan kata sandi (yang terletak di kode VBA). Juga, jika Anda memutuskan untuk menghapus makro ini dari buku kerja Anda, Anda harus memasukkan kembali kata sandi untuk membuka proteksi buku kerja setelahnya jika Anda ingin semuanya tidak terlindungi

Catatan. Untuk mengubah kata sandi, temukan di mana dikatakan "kata sandi" di kode makro vba dan ubah kata ini menjadi kata sandi apa pun yang Anda inginkan. Anda perlu mengubahnya di tiga tempat terpisah di dalam makro

Ada kemungkinan bahwa kode akan menyembunyikan formula lain serta yang berada di kisaran target A1. A20. Misalnya, misalkan B2 berisi rumus dan Anda memilih dari B2 ke A2 maka rumus B2 akan disembunyikan

Meskipun Anda mungkin tidak akan melakukan itu, atau mungkin tidak menjadi masalah bagi Anda, saya pikir perubahan sederhana pada kode Andrew harus memperbaikinya. Saya pikir satu-satunya perubahan yang diperlukan adalah

Kaya (kode BB)

If Not Application.Intersect(ActiveCell, Rng) Is Nothing Then

Kaya (kode BB)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Rng As Range
    Static Started As Boolean
    Static Cell As Range
    Static TheFormula As String
    Set Rng = Range("A1:A20")
    If Not Application.Intersect(ActiveCell, Rng) Is Nothing Then
        If Not Cell Is Nothing Then
            Cell.Formula = TheFormula
        End If
        Set Cell = ActiveCell
        With Cell
            TheFormula = .Formula
            .Value = .Value
        End With
        Started = True
    Else
        If Started Then
            With Cell
                .Formula = TheFormula
            End With
        End If
    End If
End Sub

Kadang-kadang kita mungkin perlu menyembunyikan rumus di lembar kerja Excel kita tanpa harus melindungi lembar atau melindungi sel yang memiliki rumus

Dalam tutorial ini, saya akan membahas tiga teknik untuk menyembunyikan rumus di Excel tanpa melindungi lembar/sel

Metode termudah adalah menghapus rumus dan hanya menyimpan nilainya. Saya juga akan membahas cara menggunakan event handler di VBA sehingga segera setelah Anda memasukkan rumus dalam sel, secara otomatis diubah menjadi nilai, dan rumus dihapus

Daftar isi

Metode #1. Ubah Rumus menjadi Nilai

Cara termudah untuk menyembunyikan rumus di Excel tanpa memproteksi lembar kerja adalah dengan mengubah rumus menjadi nilai

Misalkan kita memiliki dataset berikut yang memiliki rumus di kolom E

Kami ingin berbagi lembar kerja dengan beberapa orang, tapi kami tidak ingin mereka melihat rumus di kolom E

Kami dapat mengonversi rumus menjadi nilai sebelum dibagikan sehingga mereka hanya melihat hasil dari rumus dan bukan rumus itu sendiri

Di bawah ini adalah langkah-langkah untuk menyembunyikan rumus dan hanya menyimpan nilainya

1. Pilih rentang E2. E6, yang berisi rumus-rumus yang ingin kita sembunyikan
2. Tekan Ctrl + C untuk menyalin rumus

1. Tekan Ctrl + Alt + V untuk membuka kotak dialog Tempel Spesial

Sebagai alternatif, Anda dapat membuka kotak dialog Tempel Spesial dengan membuka tab Beranda dan memilih Tempel Spesial di grup Clipboard

1. Pilih Values ​​pada kotak dialog Paste Special yang muncul dan klik OK

Rumus dikonversi menjadi nilai

Salah satu kekurangan yang jelas dari metode ini adalah setelah Anda menghapus rumus dan hanya menyimpan nilainya, saat Anda membuat perubahan di salah satu sel yang digunakan dalam rumus, rumus tidak akan diperbarui secara otomatis.

Metode #2. Gunakan Sub-prosedur dalam Modul Kode Standar

Kita bisa menggunakan sub-prosedur dalam modul kode standar untuk menyembunyikan rumus di Excel tanpa melindungi lembar kerja.  

Bayangkan kita memiliki kumpulan data berikut yang menunjukkan gaji karyawan bank tertentu. Dataset ada di Sheet1 buku kerja

Kami ingin menggunakan sub-prosedur dalam modul standar untuk menghitung bonus karyawan (10% dari gaji)

Rumus yang digunakan untuk menghitung bonus ada di sub-prosedur dan bukan di kolom E.  

Berikut adalah langkah-langkah menggunakan subrutin di Excel VBA untuk melakukan perhitungan dan mendapatkan nilainya

1. Aktifkan Lembar1
2. Pada tab Pengembang, di grup Kode, klik Visual Basic untuk membuka Editor Visual Basic

Jika Anda tidak melihat tab Pengembang di pita Anda, Anda juga dapat menggunakan pintasan keyboard Alt + F11 (tahan tombol ALT lalu tekan tombol F11)

1. Di Editor VB, klik opsi Sisipkan di menu, lalu klik opsi Modul. Ini akan memasukkan modul baru untuk buku kerja

1. Salin dan tempel kode berikut di jendela kode modul

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub CalculateBonus()
Dim i As Long, lastRow As Long
lastRow = Sheets("Sheet1").Range(“A” & Rows.Count).End(xlUp).Row
Sheet1.Activate
    For i = 2 To lastRow
        Cells(i, 5) = Cells(i, 4) * 0.1
    Next i
End Sub

1. Simpan kode makro dengan mengklik tombol simpan di bilah alat
2. Tempatkan kursor di mana saja dalam kode makro lalu tekan tombol F5 untuk mengeksekusi kode. Anda juga dapat menjalankan kode dengan mengeklik tombol hijau Run Sub/Userform pada toolbar

1. Klik ikon Lihat Microsoft Excel untuk menyembunyikan Project Explorer dan kembali ke lembar kerja tempat Anda memiliki kumpulan data. Anda juga dapat menggunakan pintasan ALT + F11 untuk kembali ke lembar kerja

Hasil rumus (dan bukan rumus yang digunakan dalam kode) ditampilkan dalam rentang E2. E6

Saya telah membuat kode ini untuk kumpulan data yang saya gunakan dalam contoh ini. Anda dapat mengubah kode ini berdasarkan kumpulan data Anda

Penjelasan sub-prosedur

Dalam prosedur

• Dua variabel dari tipe data Long dideklarasikan.  
• Nomor baris terakhir yang digunakan dalam dataset ditetapkan ke variabel lastRow
• Sheet1, yang berisi kumpulan data, diaktifkan
• Konstruk For…To…Next digunakan untuk mengulang rentang E2. E6 menugaskan hasil rumus yang digunakan ke setiap sel dalam rentang

Also read: How to Remove Password From Excel? 5 Easy Ways!

Metode #3. Gunakan Sub Prosedur Penangan Peristiwa Perubahan Seleksi dalam Modul Kode Lembar Kerja

Kita bisa menggunakan event handler worksheet untuk menyembunyikan formula di Excel tanpa memproteksi worksheet

Event handler terpicu setiap kali ada sel di lembar kerja yang diubah. jadi segera setelah Anda memasukkan rumus ke dalam sel di Excel, Anda mendapatkan hasilnya, dan rumus tersebut langsung dihapus, dan sel tersebut hanya mendapatkan nilai statis

Prosedur penanganan peristiwa lembar kerja harus ada dalam modul kode untuk lembar kerja itu. Jika kami memasukkan kode ke dalam modul kode standar, itu tidak akan berfungsi

Misalkan kita memiliki kumpulan data berikut yang menunjukkan gaji karyawan bank tertentu. Dataset ada di Sheet2 buku kerja

Kami ingin memasukkan kode event handler di modul kode Sheet2 sehingga setiap kali kami memasukkan rumus di sel dalam rentang E2. E6, rumus diubah menjadi nilai

Kami melanjutkan sebagai berikut

1. Klik kanan tab Sheet2 dan pilih Lihat Kode pada menu pintasan yang muncul

1. Di modul lembar kerja yang muncul, ketikkan kode berikut

'Code developed by Steve Scott from https://spreadsheetplanet.com
Option Explicit
Dim myDict As New Dictionary
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myCell As Range, myRng As Range
Set myRng = Range("E2:E6")
    If myDict.Count <> myRng.Count Then
        For Each myCell In myRng
         myDict.Add myCell.Address, myCell.FormulaR1C1
        Next
    End If
    If (Target.Count = 1) And (Not Application.Intersect(myRng, Target) _
        Is Nothing) And (Target.HasFormula) Then
        With Target
            .Value = .Value
        End With
    End If
    myDict.RemoveAll
End Sub

1. Sekarang kode dapat berfungsi, lakukan hal berikut

• Buka menu Alat dan pilih Referensi

• Pada fitur References – VBAProject yang muncul, scroll ke bawah pada kotak Available References dan pilih opsi Microsoft Scripting Runtime dan klik OK

1. Klik tombol Lihat Microsoft Excel pada bilah alat untuk beralih ke lembar kerja aktif yang berisi kumpulan data

Alternatifnya, tekan Alt + F11 untuk beralih ke lembar kerja yang aktif.  

1. Pilih sel E2 dan ketik rumus berikut

=D2*0.20

1. tekan enter. Perhatikan bahwa saat Anda menekan Enter, event handler dipicu dan mengonversi rumus menjadi nilai

1. Masukkan rumus berikut

• Di sel E3 masukkan D3*0. 15
• Di sel E4 masukkan D4*0. 12
• Di sel E5 masukkan D5*0. 11
• Di sel E6 masukkan D6*0. 1

Perhatikan bahwa kita dapat memasukkan formula yang berbeda di dalam sel, dan event handler mengonversi setiap formula menjadi sebuah nilai.  

Ini juga akan berfungsi jika Anda memasukkan rumus dalam satu sel dan kemudian menyeret pegangan gagal untuk memasukkan rumus di semua sel yang tersisa di kolom

Penjelasan tentang event handler

• Variabel koleksi seluruh modul dari tipe data Kamus dideklarasikan.  
• Kami menggunakan prosedur SelectionChange lembar kerja
• Dua variabel dari tipe objek Range dideklarasikan
• Rentang E2. E6 ditugaskan ke variabel myRng menggunakan kata kunci Set. Jika Anda memasukkan rumus dalam rentang lain, Anda dapat mengubahnya di sini
• Konstruksi For…Each…In…Next digunakan untuk menambahkan nilai ke variabel myDict. Alamat myCell adalah kuncinya dan myCell. FormulaR1C1 adalah isinya
• Prosedur memeriksa apakah sel yang ditargetkan berada dalam rentang E2. E6 memiliki rumus dan kemudian mengonversi rumus menjadi nilai.  
• Di akhir sub-prosedur, semua nilai yang dimasukkan dalam variabel myDict akan dihapus.  

Dalam tutorial ini, kita melihat tiga teknik yang bisa kita gunakan untuk menyembunyikan rumus di Excel tanpa memproteksi lembar kerja

Bagaimana cara menyembunyikan formula di Excel tanpa perlindungan?

Bagaimana cara menyembunyikan rumus di Excel VBA?

Bagaimana cara menyembunyikan hasil rumus di Excel?

Apa jalan pintas untuk menghapus rumus dan menyimpan nilai di Excel VBA?