Formula jadi Value saat close

  1. 4 bulan yang lalu

    Assalamu Alaikum Wr. Wb.

    Saya saat ini sedang mencoba membuat worksheet excel yang dilengkapi dengan maco, seperti persoalan klasiknya bahwa macro akan aktif kalau pilihan enable content dipilih, sehingga supaya program makronya berjalan maka user harus dipaksa untuk memilih enable content. saya ber-ide bahwa apabila user akan menggunakan worksheet dan dipaksa mengklik pilihan enable content maka formula dalam worksheet (sel di WS) tersebut menjadi value saat worksheet tersebut ditutup dan pada saat file tersebut dibuka apabila enable content tidak diklik sel tersebut tetap value tetapi apabila di enablekan maka isi sel tersebut menjadi formula kembali misalnya :
    Range{"C1").Formula = "=A1+B1"
    Range("C1").Value = Range("C1").Value

    saya mau sel C1 pada saat close WS isisnya menjadi value dan pada saat dibuka tanpa enable content nilai tetap value tetapi kalau di enable content isinya jadi formula kembali.
    Terima kasih sebelumnya kepada para suhu, semoga amal kebaikannya dibalas berlipat ganda
    Wassalamu Alaikum Wr. Wb.

    Mas @manweljs_ ...

    Izin ikut menambahkan solusi berbeda...

    Wa'alaikumsalam Mas @ayon suherman ...

    Saya punya ide, coba gunakan prosedur Workbook_Open dan Workbook_BeforeClose pada objek ThisWorkbook untuk memicu proses yang diinginkan, misalkan mengubah formula pada sel target menjadi #VALUE! dan sebagainya. Hanya saja, bagaimana formula "menyimpan" pada sel target akan disimpan? Sekedar ide dari saya sebagai berikut:

    1 — Formula diubah menjadi #VALUE!
    Untuk mengubah isi atau nilai sebuah sel menjadi sebuah nilai kesalahan (seperti #VALUE!, #N/A, #DIV/0! dan sebagainya) menggunakan VBA, maka dapat menggunakan script seperti berikut:

    Sheet1.Range("C1") = CVErr(xlErrValue)

    Adapun konstanta kesalahan yang dapat digunakan:

    xlErrDiv0  = #DIV/0!
    xlErrNA    = #N/A
    xlErrName  = #NAME?
    xlErrNull  = #NULL!
    xlErrNum   = #NUM!
    xlErrRef   = #REF!
    xlErrValue = #VALUE!

    Lalu bagaimana formula sebenarnya akan disimpan. Idenya bisa bermacam-macam. Ide saya antara lain sebagai berikut:

    A) Dengan membuat sheet "bayangan" yang disembunyikan. Dalam hal ini, sheet bayangan akan berisi formula sebenarnya dari sheet sebenarnya pada alamat sel yang sama. Misalkan untuk menyimpan dan mengembalikan nilainya digunakan script:

    '+-- Simpan!
    Sheet2.Range("C1").Formula = Sheet1.Range("C1").Formula
    Sheet1.Range("C1").Formula = CVErr(xlErrValue)
    
    '+-- Ambil!
    Sheet1.Range("C1").Formula = Sheet2.Range("C1").Formula

    B) Menyimpan nama sheet, alamat sel dan formula ke dalam bentuk teks gabungan. Teks berisi ketiga entiti tersebut yang harus dapat dipisahkan kembali nantinya. Contohnya:

    +-- Simpan!
    With Sheet1
       Sheet2.Range("A1").Value = .CodeName & "|" & .Range("D6").Address(0,0) & "|" & .Range("D6").Formula
    End With
    Sheet1.Range("C1").Formula = CVErr(xlErrValue)
    
    '+-- Ambil!
    Dim xArray
    
    xArray = Split(Decrypt(Sheet2.Range("A1")), "|")
    Worksheets(xArray(0)).Range(xArray(1)).Formula = xArray(2)

    2 — Formula Dienkripsi (diacak).
    Dalam hal ini, formula langsung dienkripsi (diacak), dan formula hasil acakan tersebut dikembalikan ke sel tersebut. Normalnya, Excel akan menghasilkan kesalahan berupa #NAME? karena formula pada sel tersebut tidak dikenali. Untuk mengembalikan formula, pastikan ada prosedur untuk mengembalikan formula yang sudah diacak. Ide scriptnya dapat seperti berikut:

    +-- Simpan!
    Sheet1.Range("C1").Formula = Encrypt(Sheet1.Range("C1").Formula)
    
    '+-- Ambil!
    Sheet1.Range("C1").Formula = Decrypt(Sheet1.Range("C1").Formula)

    Untuk modul String Cipher ini, dapat dicari di internet yang sesuai. Saya rasa dengan Cipher sederhana sudah cukup memadai.

    Mana metode yang disukai, saya kembalikan kepada mas @ayon suherman. Sebagai gambaran apa yang saya jelaskan di atas, terlampir contohnya yang dapat saya berikan, namun masih berupa inti scriptnya saja (bagaimanapun, script-script tersebut harus disesuaikan dengan keadaan sebenarnya). Coba periksa file dalam keadaan macro enabled atau disabled.

    Demikian, semoga sesuai dan semoga bermanfaat.

  2. manweljs_

    Jul 22 Terverifikasi + 5.786 Poin

    @ayon suherman

    ...seperti persoalan klasiknya bahwa macro akan aktif kalau pilihan enable content dipilih...

    setahu saya tidak selalu seperti itu :)

    pesan tersebut bisa saja tidak muncul berdasarkan setting option pada excel, misalnya saat setingan makro sudah di enable atau seting pesannya di disable :

    Capture.PNG

  3. Caton

    Jul 22 Terverifikasi Jawaban Terpilih Indonesia + 12.569 Poin
    Di sunting 4 bulan yang lalu oleh Caton

    Mas @manweljs_ ...

    Izin ikut menambahkan solusi berbeda...

    Wa'alaikumsalam Mas @ayon suherman ...

    Saya punya ide, coba gunakan prosedur Workbook_Open dan Workbook_BeforeClose pada objek ThisWorkbook untuk memicu proses yang diinginkan, misalkan mengubah formula pada sel target menjadi #VALUE! dan sebagainya. Hanya saja, bagaimana formula "menyimpan" pada sel target akan disimpan? Sekedar ide dari saya sebagai berikut:

    1 — Formula diubah menjadi #VALUE!
    Untuk mengubah isi atau nilai sebuah sel menjadi sebuah nilai kesalahan (seperti #VALUE!, #N/A, #DIV/0! dan sebagainya) menggunakan VBA, maka dapat menggunakan script seperti berikut:

    Sheet1.Range("C1") = CVErr(xlErrValue)

    Adapun konstanta kesalahan yang dapat digunakan:

    xlErrDiv0  = #DIV/0!
    xlErrNA    = #N/A
    xlErrName  = #NAME?
    xlErrNull  = #NULL!
    xlErrNum   = #NUM!
    xlErrRef   = #REF!
    xlErrValue = #VALUE!

    Lalu bagaimana formula sebenarnya akan disimpan. Idenya bisa bermacam-macam. Ide saya antara lain sebagai berikut:

    A) Dengan membuat sheet "bayangan" yang disembunyikan. Dalam hal ini, sheet bayangan akan berisi formula sebenarnya dari sheet sebenarnya pada alamat sel yang sama. Misalkan untuk menyimpan dan mengembalikan nilainya digunakan script:

    '+-- Simpan!
    Sheet2.Range("C1").Formula = Sheet1.Range("C1").Formula
    Sheet1.Range("C1").Formula = CVErr(xlErrValue)
    
    '+-- Ambil!
    Sheet1.Range("C1").Formula = Sheet2.Range("C1").Formula

    B) Menyimpan nama sheet, alamat sel dan formula ke dalam bentuk teks gabungan. Teks berisi ketiga entiti tersebut yang harus dapat dipisahkan kembali nantinya. Contohnya:

    +-- Simpan!
    With Sheet1
       Sheet2.Range("A1").Value = .CodeName & "|" & .Range("D6").Address(0,0) & "|" & .Range("D6").Formula
    End With
    Sheet1.Range("C1").Formula = CVErr(xlErrValue)
    
    '+-- Ambil!
    Dim xArray
    
    xArray = Split(Decrypt(Sheet2.Range("A1")), "|")
    Worksheets(xArray(0)).Range(xArray(1)).Formula = xArray(2)

    2 — Formula Dienkripsi (diacak).
    Dalam hal ini, formula langsung dienkripsi (diacak), dan formula hasil acakan tersebut dikembalikan ke sel tersebut. Normalnya, Excel akan menghasilkan kesalahan berupa #NAME? karena formula pada sel tersebut tidak dikenali. Untuk mengembalikan formula, pastikan ada prosedur untuk mengembalikan formula yang sudah diacak. Ide scriptnya dapat seperti berikut:

    +-- Simpan!
    Sheet1.Range("C1").Formula = Encrypt(Sheet1.Range("C1").Formula)
    
    '+-- Ambil!
    Sheet1.Range("C1").Formula = Decrypt(Sheet1.Range("C1").Formula)

    Untuk modul String Cipher ini, dapat dicari di internet yang sesuai. Saya rasa dengan Cipher sederhana sudah cukup memadai.

    Mana metode yang disukai, saya kembalikan kepada mas @ayon suherman. Sebagai gambaran apa yang saya jelaskan di atas, terlampir contohnya yang dapat saya berikan, namun masih berupa inti scriptnya saja (bagaimanapun, script-script tersebut harus disesuaikan dengan keadaan sebenarnya). Coba periksa file dalam keadaan macro enabled atau disabled.

    Demikian, semoga sesuai dan semoga bermanfaat.

  4. manweljs_

    Jul 22 Terverifikasi + 5.786 Poin
    Di sunting 4 bulan yang lalu oleh manweljs_

    Seperti yang saya duga

    skrip file mas @Caton gak berpengaruh pada excel saya (enable contentnya ga muncul - karena sudah enable :P)

    tapi trik Workbook_Open dan Workbook_BeforeClose nya sippp mas !

    diskusi ini masalah utamanya adalah enable makro kan? bagaimana kalau mas @Caton memberikan contoh solusi agar excel yang makronya disable bisa aktif sendiri saat filenya dibuka -- kalau kata mas @ayon suherman : " user harus dipaksa " ;)

  5. Caton

    Jul 22 Terverifikasi Indonesia + 12.569 Poin
    Di sunting 4 bulan yang lalu oleh Caton

    Mas @manweljs_ ...

    Sayang sekali... :( Mungkin mas bisa mencoba meletakkan filenya ke folder yang kira-kira saat filenya dibuka akan menampilkan Warning, misalkan folder Download. Atau pada prosedur Workbook_Open() tambahkan perintah Exit Sub di awal prosedur:

    Private Sub Workbook_Open()
       Exit Sub
       ...
    End Sub

    Dengan demikian bisa diketahui perbedaannya.

    As Value.gif

    File tersebut memang tidak akan terlihat perbedaannya jika macro sudah Enabled. Sesuai yang saya pahami dari pertanyaan TS, bagaimana caranya agar formula sebuah sel berubah menjadi #VALUE! saat Workbook ditutup. Kemudian, jika Workbook dibuka dan apabila pengguna tidak mengaktifkan macro, maka sel-sel yang harusnya berisi formula tetap benilai #VALUE!. Namun jika macro Enabled, maka formula dikembalikan ke selnya masing-masing...

    ... bagaimana kalau mas @Caton memberikan contoh solusi agar excel yang makronya disable bisa aktif sendiri saat filenya dibuka -- kalau kata mas @ayon suherman : " user harus dipaksa " ;) ...

    Setahu saya tidak ada caranya mas kalau mau diotomasi melalu script (VBA) juga. Karena mengaktifkan opsi macro itu merupakan keputusan pengguna... :D Selama macronya disabled, otomatis script yang ada pun tidak akan bisa dieksekusi. Dengan add-in pun saya rasa sama saja. Jika pengguna tidak mengaktifkan add-in, semua script juga gak bakalan jalan.... :D

  6. manweljs_

    Jul 22 Terverifikasi + 5.786 Poin
    Di sunting 4 bulan yang lalu oleh manweljs_

    iya, saya paham mas @Caton :)

    itu sebabnya saya katakan trik Workbook_Open dan Workbook_BeforeClose nya sippp ! (tentu saja sudah saya coba)

    namun saya membayangkan kasus komputer yang berbeda-beda. misalnya security warningnya disable, message nya disable dll yang menyebabkan pengguna tidak menemukan tombol untuk enable content

    jadi saya pikir enable makro otomatis adalah solusi yg paling baik... silahkan mas ;)

  7. Caton

    Jul 22 Terverifikasi Indonesia + 12.569 Poin
    Di sunting 4 bulan yang lalu oleh Caton

    Mas @manweljs_ ...

    Masalah pengguna tidak dapat melihat Security Warning atau tidak tahu caranya, saya rasa bukan fokus utama yang diinginkan TS. Jika pengguna tidak bisa mengaktifkan macro, mungkin TS atau seseorang akan turun tangan mengaktifkannya... :)

    Saya yakin, TS pengennya pengguna mengaktifkan macro agar script khusus dapat dieksekusi. Bisa saja TS pengen pengguna harus memasukkan kode aktivasi, atau melalui script dapat diketahui Serial Number Harddisk (misalkan untuk membatasi penggunaan) dan sebagainya. Intinya, jika pengguna akan menggunakan file yang TS buat, macro harus enabled. Klo gak, hasil formulasinya akan #VALUE! semua... :D Menurut saya sih begitu... :)

    Saya pribadi tidak tahu bagaimana trik mengaktifkan macro secara otomatis. Sudah lama saya pengen mendapatkan trik atau caranya mengaktifkan macro secara otomatis. Sayang sampai sekarang ini belum saya temukan...

  8. Terima kasih pada suhu2 yang sudah memberikan penjelasan...saya rasa jawaban @Caton sangat memuaskan saya meskipun saya masing harus pelajari lagi dengan keterbatasan kemampuan....

  9. Akhirnya saya bisa berhasil juga membuat WS seperti yang dimau, terima kasih @Caton sudah ngasih ijin nyontek, mohon ijin saya modifikasi...harap maklum masih belajar.....filenya saya upload

  10. Caton

    Jul 24 Terverifikasi Indonesia + 12.569 Poin

    Sama-sama mas @ayon suherman ... Semua script VBA dan formula yang sudah dibagikan di forum ini bebas digunakan dan dimodifikasi sesuai kebtuhan, jadi pada dasarnya tidak perlu izin... :) FYI, selain menggunakan prosedur pada objek ThisWorkbook, mas @ayon suherman juga bisa menggunakan contoh script seperti berikut:

    Option Explicit
    
    Sub Auto_Open()
        Sheet1.Protect Password:="a7x", UserInterfaceOnly:=True
        Sheet1.Range("D6").Formula = "=A6+B6"
        Dim BatasWaktu As Date
        BatasWaktu = "12/31/2018" ' ini merupakan tanggal batas waktu expire silakan ganti
        If Date > BatasWaktu Then
            MsgBox ("Masa Pakai File telah habis"), vbInformation, "Informasi"
            ActiveWorkbook.Close
        End If
    End Sub
    
    Sub Auto_Close()
        Application.DisplayAlerts = False
        If Sheet1.Range("D6").HasFormula Then
            Sheet1.Range("D6").Formula = CVErr(xlErrValue)
        End If
        ThisWorkbook.Save
    End Sub

    Kedua prosedur tersebut (Sub Auto_Open dan Sub Auto_Close) merupakan prosedur yang akan dieksekusi secara otomatis oleh VBA, dan harus disusun di dalam Standard Module.

    Demikian, semoga bermanfaat... ;)

 

atau Mendaftar untuk ikut berdiskusi!