1234567
返回列表 上一主題 發帖

[發問] 誰能幫這幾乎重複動作的VBA瘦身 謝謝

lcctno

帖子
142 
主題
18 
精華
0 
積分
160 
點名
294  
作業系統
win10 64位元 
軟體版本
office2003 
閱讀權限
20 
性別
男 
來自
臺灣 高雄市 
註冊時間
2015-7-18 
最後登錄
2026-11-1 

2025年全勤獎章2024年全勤獎章2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章
1# 跳轉到 » 正序看帖
打印
tT
發表於 2015-8-23 18:49 | 只看該作者

[發問] 誰能幫這幾乎重複動作的VBA瘦身 謝謝

本帖最後由 lcctno 於 2015-8-23 18:52 編輯

由於在下是vba初學者 故嘗試將我使用函數寫的數百mb之excell檔 使用錄製巨集的方式 來將該檔瘦身 雖然瘦身後可正常使用 但我想應該程式碼也有簡化的可能性
我知道可以將  Rows("3:101").Select 改成 Rows("3:1001").Select 但會發生類似系統資源不夠的警告語 故只好寫成一次只判斷100筆的資料 但我目前的資料也接近6000筆了 且還會繼續增加
希望有高手能幫助我簡化它 並讓它能順利的執行到 10001筆 (為將來留後路) 謝謝了

目前瘦身的處理過程是將每一分頁 指保留一列之函數 使用vba將之複製並貼上99列函數後 在將附製上之結果變成純文字再貼回原處 依此類推...工作表.由"析1" 到"析10"  總共10分頁 如附圖






分隔線++++++++++++++++++++++++++++++++++++++++++++++
'判斷列1之值是否在工作表"Data"對應之每日高低值的範圍內

Sub 判斷列()

'2~101

'製作判斷列
    Sheets("析1").Select
    Rows("2:2").Select
    Selection.Copy
    Rows("3:101").Select
    ActiveSheet.Paste
   
'瘦身
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
'製作判斷列
    Sheets("析2").Select
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Rows("3:101").Select
    ActiveSheet.Paste
        
'瘦身
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
  ...............
. ....略過很多比工程碼...............


'製作判斷列
Sheets("析10").Select
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Rows("202:301").Select
    ActiveSheet.Paste
            
'瘦身
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
Sheets("Data").Select
End Sub


匯出之巨集
判斷列.bas.zip (640 Bytes)
收藏 分享

准提部林

帖子
2848 
主題
10 
精華
0 
積分
2903 
點名
0  
作業系統
〔略〕 
軟體版本
〔略〕 
閱讀權限
100 
性別
男 
來自
〔略〕 
註冊時間
2013-5-13 
最後登錄
2026-7-25 
68#
發表於 2015-8-31 16:12 | 只看該作者
回復 67# lcctno


但是有一個問題 儲存離開後 再開啟 發現若改變統計日期之區間 就不會改變數據 且打開時並沒有明顯的運算動做
這樣那檔案就是只能一次性使用(除非是不想改變統計區間 或輸入其他股票的data 有沒有辦法加一個開關 隨時可隨性而用
想來源資料變動，公式自動變動，那就是〔常駐公式〕，也是因這個而讓操作檔案礙手，
所以，資料變動，只能提供〔以VBA刷新〕方式得到最新結果，，因此，兩種VBA只是用來驗證兩程結果是否一致？
若資料不多，就保持公式常態，連VBA都可省去了！
若資料很多又要保持公式，恕無方法讓檔案不卡（關掉自動重算，與使用VBA同樣效果，但會造成其它公式無法運算，不是好方法），

.Formula = "=IF(A3="""","""",SUMPRODUCT((A3<=資料列_最高價)*(A3>=資料列_最低價)))"  
這不是附檔中的公式嗎？只是利用VBA去置入！也可用〔錄製〕取得FormulaR1C1的公式！

有些問題需要累積經驗才懂，一時無法說太詳細，請慢慢參酌∼∼
　

TOP

lcctno

帖子
142 
主題
18 
精華
0 
積分
160 
點名
294  
作業系統
win10 64位元 
軟體版本
office2003 
閱讀權限
20 
性別
男 
來自
臺灣 高雄市 
註冊時間
2015-7-18 
最後登錄
2026-11-1 

2025年全勤獎章2024年全勤獎章2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章
67#
發表於 2015-8-31 14:46 | 只看該作者
回復 66# 准提部林
報告高手
執行中須等待些時間（有如當機）
執行這項用不到4秒 很值得
但是有一個問題 儲存離開後 再開啟 發現若改變統計日期之區間 就不會改變數據 且打開時並沒有明顯的運算動做 這樣那檔案就是只能一次性使用(除非是不想改變統計區間 或輸入其他股票的data 有沒有辦法加一個開關 隨時可隨性而用
很報歉較慢回覆 因在做測試那執行檔 (由於看不懂 只能實做看現象) 所以花了不少時間

.Formula = "=IF(A3="""","""",SUMPRODUCT((A3<=資料列_最高價)*(A3>=資料列_最低價)))"  
請麻煩告訴我有這行是什麼用途?

謝謝您的幫忙

TOP

准提部林

帖子
2848 
主題
10 
精華
0 
積分
2903 
點名
0  
作業系統
〔略〕 
軟體版本
〔略〕 
閱讀權限
100 
性別
男 
來自
〔略〕 
註冊時間
2013-5-13 
最後登錄
2026-7-25 
66#
發表於 2015-8-31 12:46 | 只看該作者
回復 64# lcctno


本來穿褲子（公式）嫌慢，改成裙子（vba）較快些，
現在想褲子再穿搭裙子，問題又回到原點，
公式範圍第一格留公式，其下複製後再貼成值（以５５樓範例檔為準）：
Sub 更新公式()
With [結果1!B4:B2562]
　　　[結果1!B3].Copy .Cells
　　　.Value = .Value
End With
End Sub

這樣只有第一格公式會影響運作∼∼

或如下：
Sub 更新公式2()
With [結果1!B3:B2562]
　　.Formula = "=IF(A3="""","""",SUMPRODUCT((A3<=資料列_最高價)*(A3>=資料列_最低價)))"
　　.Value = .Value
End With
End Sub

執行中須等待些時間（有如當機），完成後即不會影響其它操作！
　

TOP

准提部林

帖子
2848 
主題
10 
精華
0 
積分
2903 
點名
0  
作業系統
〔略〕 
軟體版本
〔略〕 
閱讀權限
100 
性別
男 
來自
〔略〕 
註冊時間
2013-5-13 
最後登錄
2026-7-25 
65#
發表於 2015-8-31 12:39 | 只看該作者
回復 62# ML089


哈! 還是新版有料~~
謝謝！

之前都用奇摩知識+，現在版面一改已面目皆非，幾無excel專用效果！
excel-home非常專業，但台灣少人用，有時串串門子，
台灣比較專于excel論壇的，似乎只剩這裡，而各位板主功力也幾可幫忙大部份問題，所以也少上來（上來也是看看而已）！

TOP

lcctno

帖子
142 
主題
18 
精華
0 
積分
160 
點名
294  
作業系統
win10 64位元 
軟體版本
office2003 
閱讀權限
20 
性別
男 
來自
臺灣 高雄市 
註冊時間
2015-7-18 
最後登錄
2026-11-1 

2025年全勤獎章2024年全勤獎章2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章
64#
發表於 2015-8-30 22:59 | 只看該作者
本帖最後由 lcctno 於 2015-8-30 23:00 編輯

回復 47# 准提部林

請問有沒有辦法讓我這檔案 (除了拆單外)
能夠於運算完後 就將某個功能關掉 好讓它把資源讓出來
也就是說在執行同樣那檔 但不需要它在即時運算
因為統計完後就照表操課了 只是還會需要用到別的部分
有勞您了 謝謝
我現在是"函數+vba" double check 也比較放心

TOP

ML089

帖子
2025 
主題
13 
精華
0 
積分
2053 
點名
0  
作業系統
WIN7 
軟體版本
Office2007 
閱讀權限
100 
性別
男 
來自
台北市 
註冊時間
2011-3-2 
最後登錄
2024-3-14 

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
63#
發表於 2015-8-30 20:01 | 只看該作者
回復 61# 准提部林

＿＿登錄時都勾選了〔自動登錄〕，每次進來論壇還要再輸入帳號密碼，真不方便！

我的會自動登錄(WIN10+CHROME)
但一段時間(好像一個星期吧)就會要求重新登錄，若你不常上來就會感覺每次登錄都要重新輸入帳號密碼。
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

帖子
2025 
主題
13 
精華
0 
積分
2053 
點名
0  
作業系統
WIN7 
軟體版本
Office2007 
閱讀權限
100 
性別
男 
來自
台北市 
註冊時間
2011-3-2 
最後登錄
2024-3-14 

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
62#
發表於 2015-8-30 19:57 | 只看該作者
回復 61# 准提部林


MMULT 在2007版
1. 測試 1000000X2 及 2X1000000 都可以跑
2. 測試 4000X4000以上就會出現公式資源不足的訊息
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

准提部林

帖子
2848 
主題
10 
精華
0 
積分
2903 
點名
0  
作業系統
〔略〕 
軟體版本
〔略〕 
閱讀權限
100 
性別
男 
來自
〔略〕 
註冊時間
2013-5-13 
最後登錄
2026-7-25 
61#
發表於 2015-8-30 19:39 | 只看該作者
本帖最後由 准提部林 於 2015-8-30 19:50 編輯

回復 60# ML089


全域陣列確實較快省資源!

MMULT在XP+OFFICE2000下, 最多可用到5643列, 再多就#VALUE,
不知其他版本可用到多少列?


＿＿登錄時都勾選了〔自動登錄〕，每次進來論壇還要再輸入帳號密碼，真不方便！
　

TOP

ML089

帖子
2025 
主題
13 
精華
0 
積分
2053 
點名
0  
作業系統
WIN7 
軟體版本
Office2007 
閱讀權限
100 
性別
男 
來自
台北市 
註冊時間
2011-3-2 
最後登錄
2024-3-14 

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
60#
發表於 2015-8-30 18:08 | 只看該作者
回復 53# lcctno


原來的也是OK，修改一些公式感覺快一些。

大盤2011-12-19起 (ML089).rar (120.57 KB)
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

        靜思自在 : 【時間無法遮擋】怕時間消逝，花了許多心血，想盡各式方法要遮擋時間，結果是：浪費了更多時間，且一無所成！
1234567
返回列表 上一主題