Pertanyaan Pembaruan Excel VBA Function hanya pada perubahan


Saya memiliki fungsi yang telah saya terapkan sebagai modul VBA yang mengambil satu sel sebagai parameter dan meminta layanan web untuk mengembalikan hasil. Karena sumber data jauh, fungsi ini dapat memakan waktu beberapa detik untuk kembali.

Masalahnya adalah bahwa beberapa tindakan, seperti menghapus kolom apa pun, tampaknya menyebabkan setiap rumus untuk menghitung ulang, bahkan jika tidak diperlukan perubahan. Ketika lembaran saya memiliki beberapa ratus baris, ini bisa memakan waktu beberapa menit selama waktu Excel tidak responsif.

Saya ingin menemukan beberapa cara untuk mendeteksi bahwa tidak ada perubahan yang diperlukan dan lewati langkah ini. Beberapa hal yang saya coba:

  • Tentukan nilai sel saat ini untuk diubah, dari mana saya bisa menentukan apakah pembaruan diperlukan. Tapi ActiveCell.Value kosong, dan mencoba untuk melewati sel itu sendiri sebagai parameter kedua bertemu dengan peringatan referensi melingkar.
  • Buat kolom kedua untuk menyimpan nilai input lama, yang kemudian diteruskan ke fungsi dan dibandingkan dengan nilai baru. Namun, fungsi tidak dapat memperbarui nilai lama ini pada keberhasilan, dan upaya memperbarui hanya dengan rumus selalu dipenuhi dengan peringatan referensi melingkar. Lebih jauh lagi, sepertinya tidak ada fungsi yang dapat mengembalikan "tidak ada perubahan", jadi saya perlu menyimpan dan menggunakan hasil lama.

Adakah cara untuk mencegah beberapa tindakan memperbarui setiap rumus pada sheet?

Persyaratan lainnya:

  • Mungkin ada cara untuk menonaktifkan semua perhitungan otomatis rumus, tetapi saya pasti ingin setiap baris untuk memperbarui segera setelah sel parameter dimasukkan. Saya juga tidak ingin memaksa pengguna untuk menekan tombol untuk memperbarui fungsi.
  • Saya mencoba melakukan spreadsheet khusus di Visual Studio, tetapi menemukan penempatannya terlalu sulit untuk dikelola untuk basis pengguna saya. Solusi harus sepenuhnya terkandung dalam buku kerja makro-enabled.
  • Versi Excel untuk menggunakan lembar ini adalah 2007 dan 2010.

4
2017-11-12 22:47


asal


Jawaban:


saya akan

  1. secara sistematis memblokir perhitungan kembali dengan menetapkan File / Opsi / Rumus / Perhitungan Buku Kerja ke "Manual" dan matikan "Penghitungan Ulang pada Simpan" juga

  2. membuat Private Sub Worksheet_Change(ByVal Target As Range) dan menentukan, di mana kondisi rekalkulasi harus dilakukan (Target.Worksheet.Calculate) - misalnya menentukan coord's dari target (menggunakan Target.Column dan Target.Row), dan panggil kueri dari dalam pemicu Worksheet_Change.

  3. Akhirnya saya akan menempatkan instruksi perhitungan ulang dalam Sub sendiri dan membuat tombol ke lembar sehingga pengguna dapat - sesuai keinginannya - secara manual memicu perhitungan ulang (kalau-kalau tombol F9 tidak diketahui oleh pengguna)

  4. Hal ini juga masuk akal untuk menggunakan variabel Boolean global need_Query yang ditetapkan oleh pemicu Worksheet_Change If Target.Row = X And Target.Column = Y (= parameter) dan memiliki fungsi query memeriksa (dan mengatur ulang) yang Boolean, dan masuk hanya dalam query If need_Query Then, jika tidak, keluar tanpa tindakan. Pastikan bahwa need_Query diatur FALSE pada waktu buka worksheet juga.

  5. Akhirnya, Anda bisa mengonversi fungsi kueri menjadi Subdan merumuskan beberapa kondisi yang lebih kompleks di pemicu Worksheet_Change untuk mengaktifkannya (misalnya jika kolom tertentu berubah, dll.).


2
2017-11-13 08:00