Excel cell 用得多vba整既function 問題

bongbong3481

自己用vba 整左幾個public function,  係cell 入邊直接用public function 黎運算，而public function 主要用黎在一個range 內搵資料(10萬筆左右)。

現在出現左個問題，次次excel 一load 完資料，成個excel 進入左running 狀態，要人手click stop 先停，如果吾停左佢，excel 其它button 又沒反應,完全click 吾到。

請問解決方法，是否只能將cell 入面的public function 轉番係vba 運算完先放數值入去？

cyberyeah

自己用vba 整左幾個public function,  係cell 入邊直接用public function 黎運算，而public function 主要 ...
bongbong3481 發表於 2024-7-14 18:50

你個vb可能有bug,放上嚟睇下

via HKEPC IRF 5.1.14 - Android(5.1.2)

ww9876

每個cell都行同一個function?

via HKEPC Reader for Android

rfdingo

係咪因為一有data郁
佢就直接 重新calc/run過一次....

bongbong3481

回覆 3# ww9876


    其實得3個cell 是用到public function, 明明結果已經出左，但個excel 仍然顯示vba running緊

bongbong3481

係咪因為一有data郁
佢就直接 重新calc/run過一次....
rfdingo 發表於 2024-7-14 20:51

    郁對應地方既data 佢看運算

bongbong3481

回覆 2# cyberyeah


公司野吾出得街。  明明已經計完,  但顯示vba 仍然running,    將function 轉番系vba 運算， 最後將數值放入cell,  又沒出現呢個問題。

s20012797

大數據集同 VBA 函數性能慢嘅經典問題！

你面對嘅問題係因為由 Excel 儲存格重複調用 VBA 函數所引起嘅計算時間過長。以下係一啲可以幫你優化效能嘅解決方案：

將 VBA 函數轉換成陣列公式，而唔係喺個別儲存格入面用 VBA 函數，可以考慮將佢哋轉換成可以一次過處理整個數據範圍嘅陣列公式。噉樣可以大大減少計算次數同埋提升效能。

使用快取或者記憶，喺你嘅 VBA 函數入面實現快取或者記憶技術，嚟儲存中間結果。噉樣可以透過重用之前計算出嚟嘅值嚟幫助減少計算次數。

優化 VBA 函數調用，將 VBA 函數調用次數減至最少，使用基於範圍嘅輸入而唔係個別儲存格參考。將多個函數調用組合成一個函數，並且有多個儲存格輸入範圍。明智噉用應用程式。易失性，避免不必要嘅重新計算。

利用 Excel 嘅內置函數，盡可能使用 Excel 嘅內置函數，例如 INDEX/MATCH 或者 FILTER ，呢啲函數係為咗性能而優化，可以更有效率噉處理大型數據集。

避免喺工作表儲存格入面用 VBA 函數，而係用 VBA 喺後台執行計算，然後將結果寫入工作表儲存格。噉樣可以幫助減少計算次數同埋提升效能。

使用應用程式畫面更新同應用程式計算，暫時將應用程式畫面更新設定為 False 同埋應用程式計算為 xlCalculationManual , 以減少重新計算同畫面更新嘅開銷。

考慮用更有效率嘅數據結構，如果你用緊大型數據集，可以考慮用更有效率嘅數據結構，例如陣列或者數據庫，嚟儲存同操縱數據。

關於喺將值放入單元格之前將公共函數轉換返 VBA 計算嘅問題，係嘅，呢個係一種可行嘅方法。你可以建立一個 VBA 子程式嚟執行計算，然後將結果寫入儲存格。噉樣，你就可以控制計算過程同埋優化佢嘅效能。

以下係點樣實現呢一點嘅範例：

Sub CalculateAndWriteResult()
    Dim result As Variant
    result = MyPublicFunction(Range("A1:A100000"))'adjust the range as needed
    Range("B1").Value = result
End Sub

喺呢個範例入面，以資料範圍作為輸入呼叫 MyPublicFunction ，同埋將結果儲存喺結果變數入面。然後將結果寫入儲存格 B1。你可以根據需要調整範圍同輸出單元格。透過實施呢啲策略，你應該可以提高 Excel 工作簿嘅效能同埋減少計算時間。

https://learn.microsoft.com/en-u ... ulation-performance
https://learn.microsoft.com/en-u ... rmance-obstructions

bongbong3481

回覆 8# s20012797

你介紹的方法,我諗到一個提速方法，set 1個global range (不過未試過)， 開excel 既時間，就先load 預定既global range, 吾洗每次行個function 先load.

s20012797

回覆 9# bongbong3481

你似乎已經對優化 VBA 程式碼進行咗一啲研究同埋搵到一啲相關資訊。

你嘅研究得出嘅主要結論，將範圍儲存為陣列，動態範圍，避免使用單執行緒函數，更快嘅 VBA 使用者定義函數，最大程度減少工作表存取。噉樣可以透過減少同 Excel 工作表嘅互動次數嚟提高效能。你可以將範圍值儲存喺陣列入面同埋對陣列執行操作，而唔係循環遍歷範圍。使用動態範圍可以透過將公式嘅 COUNTA 部分儲存喺單獨嘅儲存格或者定義嘅名稱入面嚟幫助最大程度減少效能下降。某啲本機 Excel 函數係單執行緒嘅，噉可能會導致效能問題。盡量避免使用呢啲函數或者搵替代嘅多執行緒函數。將多個函數呼叫組合到具有多單元格輸入範圍嘅單一函數中，有助於最大程度減少開銷。減少程式碼存取工作表嘅次數可以明顯提高效能。

你建議嘅解決方案，呢種方法可以透過以下方式幫助提高性能。你正喺考慮設定全域範圍同埋喺 Excel 開啟嗰陣載入佢，而唔係喺每次呼叫函數嗰陣載入佢。減少載入範圍嘅次數。允許你對數組而唔係範圍執行操作。

附加建議，使用 Application.ScreenUpdating 同 Application.Calculation ，優化你嘅公共函數，考慮使用更有效率嘅資料結構。暫時將呢啲屬性分別設為 False 同 xlCalculationManual ，以減少重新計算同螢幕更新嘅開銷。檢查你嘅公共函數，睇下有冇最佳化嘅機會，例如減少循環次數或者使用更有效率嘅演算法。如果你處理緊大型資料集，請考慮用更有效率嘅資料結構（例如資料庫或者資料表）嚟儲存同操作資料。透過實施呢啲策略，你應該可以提高 Excel 工作簿嘅效能同埋減少計算時間。

https://learn.microsoft.com/en-u ... rmance-obstructions