Board logo

標題: [轉貼] VBA的寫作技巧與增進效能 [打印本頁]

作者: Hsieh    時間: 2010-4-30 23:06     標題: VBA的寫作技巧與增進效能

VBA的寫作技巧與增進效能

經由錄製產生的巨集,通常程式碼都會含有很多 Select,甚至往後自己寫的程式也習慣用一堆 Select。寫程式的人以為必須 Select 一個物件後才能對它做處理,但這是 [錄製巨集] 誤導的錯誤觀念 (自己也沒有徹底了解語法),而且是造成巨集執行效率不佳的原因之一。

一、數數看你的程式裡有多少 "Select" ?
除非程式就是要依使用者選取的物件來做動作,否則 Select 和 Selection 都是多餘的.
◎ 標準的物件控制語法:
  物件.方法 (例如 Range("A1").Copy)
  物件.屬性 = 值 (例如 Range("A1").ColorIndex = 15)
而不是一定要先 Select 物件然後再對 Selection 做動作.

舉例而言,你要複製 Sheet1.A1 的值到 Sheet2.B1 --
 Range("A1").Copy
 Sheets(2).Select
 Range("B1").Select
 Range("B1").PasteSpecial xlPasteValues
其實可以這麼寫 --
 Sheets(2).Range("B1") = Sheets(1).Range("A1")
如果內容與格式都要複製,可以這麼寫 --
 Sheets(1).Range("A1").Copy Sheets(2).Range("B1")

不要看這沒什麼,你的VBA觀念和程度能否更進一步,這是很重要的一點。

二、關閉螢幕更新 (Application.ScreenUpdating)
程式裡做的動作越多,螢幕更新的問題就越明顯。例如選取了儲存格、選取物件、複製、貼上、切換工作表... Excel 都會改變焦點 (Focus). 每改變一次,就是一次螢幕更新。想想看,在一連串的螢幕更新之中,不但令使用者眼花撩亂,程式執行的整體效能也會下降。

這與減少 Select 是一體兩面的事,其實很多選取儲存格、選取物件、複製、貼上、切換工作表... 的動作都是不必要的。只要技巧用的好,ScreenUpdating 幾乎可以束之高閣。

三、過多/不必要的迴圈也會降低執行效率
迴圈 (如 For...Next、Do...Loop等等) 是很重要的寫作技巧之一,它能大幅簡化程式中重複的動作,而且是錄製不出來的。
這裡所謂不必要的迴圈是指處理的範圍太大,浪費過多時間。例如
For Each cell In Columns(1)
 ......
Next
For Each cell In [A1:A65536]
 ......
Next
以上兩個迴圈都是處理 A 欄 6 萬多個儲存格。
說實在的,連幾千個Cell我都有點擔心了,何況幾萬個 -- 有必要嗎??
何不判斷好資料的範圍再來做迴圈 --
For Each cell In Range([A1], [A65536].End(xlUp))
 ......
Next

參考: 如何判斷資料範圍
http://gb.twbts.com/index.php/topic,315.0.html
http://gb.twbts.com/index.php/topic,584.0.html

四、釋放物件變數佔用的記憶體空間
在這裡尤指對應用程式(Appliation)的引用與存取,下例從Word表格取回資料至Excel工作表 --

Sub get_word_table( )
Dim wrdApp As Object
Set wrdApp = CreateObject("Word.Application") '建立引用Word應用程式的物件
Set wrdDoc = wrdApp.Documents.Open("D:\Temp\ole_test.doc") '引用Word文件
With wrdDoc.Tables(1)
 For r = 1 To .Rows.Count
  For c = 1 To .Columns.Count
  Cells(r, c) = .Cell(r, c)
  Next c
 Next r
End With
wrdDoc.Close 'close the document
wrdApp.Quit 'close Word
Set wrdDoc = Nothing '釋放物件變數
Set wrdApp = Nothing
End Sub

初學者常常會忽略最後兩句,如果不寫雖然不會影響程式的運行,但從記憶體管理和效能控制的角度而言,這是個很不好的習慣。

當省則省,省的是多餘重複的程式碼;
當用則用,用的是不可或缺的程式碼。

--------------------------------------------------------------------------------

大家認為有不足或疑問之處,歡迎提出補充和討論。
作者: yuch8663    時間: 2010-5-6 14:15

請問版主
 Sheets(2).Range("B1") = Sheets(1).Range("A1")
這種寫法如果要針對某範圍例如從sheet1的A1:a10做陣列轉換到sheet2的a1:j1
要如何編寫程式碼?
作者: Hsieh    時間: 2010-5-6 15:03

回復 2# yuch8663


    sheet2.[A1:J1]=application.transpose(sheet1.[A1:A10])
作者: yuch8663    時間: 2010-5-7 01:13

原來是這樣寫,承蒙版主指導,讓我增廣見聞了。
作者: ddlam    時間: 2010-7-6 23:58

専心看了一片,很實用.
作者: kingaaron    時間: 2010-9-2 10:50

感謝版主的分享,獲益良多
節省新手很多的時間也培養良好的習慣
作者: jack-wyc    時間: 2010-9-4 10:41

回復 6# kingaaron


    謝謝大大的用心,受益匪淺,感謝!
作者: James    時間: 2010-9-15 16:41

感謝板主分享:
一、數數看你的程式裡有多少 "Select" ?
select 真的是不大需要, 我目前已經很少使用了, 大部分用在要取得特定位址, 以作為下一個指令參考之用o

三、過多/不必要的迴圈也會降低執行效率
我以前會用 do while or if 指令來判斷後繼續執行, 後來碰到 i=i+1 時會碰到溢位問題, 後來就改用 find 指令,
我感覺執行速度是快了很多, 這是否比較好?
作者: eclife    時間: 2010-9-15 21:28

感謝版主分享
我剛開始使用VBA的確也是把錄製到的巨集照單全收
不過 現在已經能寫出比較有效率的CODE了
作者: asus103    時間: 2010-12-31 08:13

感謝板主分享:
這是一篇好文章,對於初學者的我而言有很大的幫助
作者: 土行孫    時間: 2010-12-31 17:19

拜讀後,覺得愛益良多,感謝分享心得,謝謝
作者: FAlonso    時間: 2010-12-31 17:37

Cells(r, c) = .Cell(r, c)

這行,究竟幾時用單數的物件,例如cell,worksheet等字眼,有單數眾數,比較混亂
作者: skybbs    時間: 2011-1-17 21:40

本帖最後由 skybbs 於 2011-1-19 20:23 編輯

回復 01# Hsieh
請問謝大,

在EXCEL2003裡寫了如下:
01 Range("D6:E6").Copy
02 Range("h3").PasteSpecial xlPasteValues
因為各有一個值分別在D6&E6, 所以直接複製到H3時, 就會變成複製到H3 & I3

想要依照謝大的方式簡短, 分別試了如下:
Sheet1.[H2:I2] = Application.Transpose(Sheet1.[D6:E6])
結果僅會把D6的值複製到H2&I2, E6並不會複製到I2.

另外也試了:
Range("H2:I2") = Range("D6:E6")
結果都沒有動作.
請問如果要把同一個Sheet的D6 & E6複製到H3 & I3應該如何寫語法呢 ?
謝謝.
作者: jeromefu    時間: 2011-1-18 12:11

回復 1# Hsieh


    感謝分享;對新手來說真是獲益良多:)
作者: jslin    時間: 2011-1-18 15:53

Looking Ahead on Request
「Wish you all the best」 Thanks a lot !!
↖(^ω^)↗

作者: fpoqua    時間: 2011-1-31 10:44

版大受教了

以後錄製的巨集會修正後再使用
作者: superjolihi    時間: 2011-1-31 21:20

高手~~ 繼續拜讀你的 資料範圍選擇~ 在公司我用 巨集寫完 稍微修改~就自得意滿~果真是學深似海。
作者: Hsieh    時間: 2011-1-31 23:12

回復 13# FAlonso

cells並無所謂擔負數之分,在EXCEL中cells就是全部儲存格,而括號中的2個引數,分別是列號與欄號
Cells(1,1)就是指到A1儲存格,他就是單一儲存格,若CELLS則會指向所有儲存格。
WorkSheet是工作表物件,這唯一會造成單數現象是發生在變數宣告時,當變數要宣告成工作表物件型態時
Dim Sh As WorkSheet
這就表示Sh變數是一個工作表
那麼,當我們在眾多工作表中,取得單一工作表就是在複數工作表中指名工作表
Set Sh=WorkSheets(index)
作者: Hsieh    時間: 2011-1-31 23:12

回復 14# skybbs


    Range("H2:I2") = Range("D6:E6").Value
作者: skybbs    時間: 2011-2-1 21:50

回復 21# Hsieh

請問謝大:
Range("H2:I2").Value = Range("D6:E6").Value
Range("H2:I2") = Range("D6:E6").Value
此兩個表示式有何不同.
作者: Hsieh    時間: 2011-2-1 22:08

回復 22# skybbs

效果一樣阿
作者: cchaha_captain    時間: 2011-3-2 10:23

請教一下版大,當我的程式需要參考好幾個excel檔案的內容時,我以前的做法就是worksheets("data.xls")....Range(xxx)這樣取得或更新資料,可是當資料內容龐大時,速度變的好慢好慢,就算是程式內部創造一個陣列把資料先讀進來,也是得面臨那段緩慢的讀取時間。
請問VBA中是不是有甚麼正規做法可以解決大量儲存格資料存取的問題呢?在這裡麻煩一下版大,謝謝!
作者: Hsieh    時間: 2011-3-2 10:28

回復 26# cchaha_captain


http://forum.twbts.com/thread-712-1-1.html
作者: GBKEE    時間: 2011-3-2 10:58

回復 26# cchaha_captain
試試下列程式
在開啟的所有活頁簿中,將除了作用中活頁簿 (ActiveWorkbook) , 之外的Sheets(1).UsedRange ,收集到作用中活頁簿中的ActiveSheet
  1. Sub Ex()
  2.     Dim Book As Workbook, Rng(1 To 2) As Range
  3.     With ActiveWorkbook.ActiveSheet
  4.         Set Rng(1) = .Range("A" & Rows.Count).End(xlUp)
  5.         For Each Book In Workbooks
  6.             If Book.Name <> .Parent.Name Then
  7.                 Set Rng(2) = Book.Sheets(1).UsedRange
  8.                 Rng(1).Resize(Rng(2).Rows.Count, Rng(2).Columns.Count) = Rng(2).Value
  9.                 Set Rng(1) = .Range("A" & Rows.Count).End(xlUp).Offset(1)
  10.             End If
  11.         Next
  12.     End With
  13. End Sub
複製代碼

作者: kim595    時間: 2011-3-2 14:33

好文+1,受益頗多
作者: cchaha_captain    時間: 2011-3-2 14:38

我了解意思了,但我需要想一下來看看怎麼做,
謝謝Hsieh與GBKEE兩位大哥。
作者: a741124a    時間: 2011-3-2 15:53

我正在學習怎麼縮小記憶體的浪費,真是一篇好文章。
作者: homeman    時間: 2011-3-4 01:24

真的可以省下很多的垃圾指令..Thanks
作者: panet    時間: 2011-3-4 12:16

這是一篇好文章,覺得受益良多,謝謝
作者: lumark1976    時間: 2011-4-4 16:19

剛入門學習,努力學習中,謝謝大大精湛的解說!!
作者: Happkkevin    時間: 2011-7-14 19:35

從此文章學到精華,這些簡化方式在各先進提供解答中
都可以學到,經由此篇更精華吸收,感恩
總是利用巨集錄製,再依學習到的VBA語法
想想怎麼簡化,來到這看看其他人的寫法
再看看以前寫的,總是可以學到一些
像是單純現存數據,以前總是犯了選取cells
再處理,近日學到一些,針對利用處理數據
寫了一段處理格式的語法
目前處理資料尚可,但一直持續學習簡化增進效能技巧中
關於Application.ScreenUpdating 學到,正在運用簡化中
目前正在學習如何簡化,如果有簡化idea 煩請提供供學習,感恩

KRowEnd = Cells(Rows.Count, 1).End(xlUp).Row '以 A欄資料為基礎 =1 判斷範圍
kcolend = Cells(1, Columns.Count).End(xlToLeft).Column '以 第一列資料為基礎 =1判斷判斷範圍

MsgBox "列數KRowEnd=" & KRowEnd
MsgBox "欄數kcolend=" & kcolend

Range(Cells(1, 1), Cells(1, kcolend)).Select

    With Selection.Interior
        .ColorIndex = 36 '淺黃色
        .Pattern = xlSolid
    End With
    Selection.Font.ColorIndex = 3


Range(Cells(2, 1), Cells(KRowEnd, kcolend)).Select

    ActiveWindow.FreezePanes = True
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=MOD(ROW(),2)"
    Selection.FormatConditions(1).Interior.ColorIndex = 15
Range(Cells(1, 1), Cells(1, kcolend)).Select
    Selection.AutoFilter
    Cells.EntireColumn.AutoFit
作者: sammyc    時間: 2011-8-3 11:28

専心看了一片,很實用.
作者: koiiven    時間: 2011-9-13 13:02

感謝大大分享!
作者: 桔色    時間: 2011-10-14 12:31

看完成之後~觀念還是很模糊~應該先去買本書來看~
作者: tomking    時間: 2011-10-14 12:44

被版主猜中了,,,就是,用那種笨方式,,得到結果..

好好給它學起來,版主所教的...

感恩..
作者: wind6424    時間: 2011-10-19 15:01

如果我復製儲存格後,只想貼上部份格式,
目前程式如下, 請問這可以在簡化嗎?
或是有其他的寫法

    Sheet9.Rows(1).Copy
    Sheet1.Rows(1).PasteSpecial Paste:=xlPasteFormats
    Sheet9.Rows(1).Copy
    Sheet1.Rows(1).PasteSpecial Paste:=xlPasteValidation
作者: linsurvey2005    時間: 2011-10-20 21:57

好實際的貼切問題說明
我的頭腦突然出現一線光
真是~讚
作者: luhpro    時間: 2011-10-20 23:01

本帖最後由 luhpro 於 2011-10-31 22:33 編輯
如果我復製儲存格後,只想貼上部份格式,目前程式如下, 請問這可以在簡化嗎?
Sheet9.Rows(1).Copy
    Sheet1.Rows(1).PasteSpecial Paste:=xlPasteFormats
    Sheet9.Rows(1).Copy
    Sheet1.Rows(1).PasteSpecial Paste:=xlPasteValidation
wind6424 發表於 2011-10-19 15:01

1. 當重複參照同一標的物時
可善用 With ... End With

2. 當 Copy 後若貼上後馬上又要貼上的話,
無須再次 Copy

3. Sheet1 是保留字不能直接拿來引用,
會發生錯誤
Set Sh9R = Sheets("Sheet9").Rows(1)
Set Sh1R = Sheets("Sheet1").Rows(1)

Sh9R.Copy
With Sh1R
  .PasteSpecial Paste:=xlPasteFormats
  .PasteSpecial Paste:=xlPasteValidation
End With

後記修正 :
後來經過測試發現問題點應該不在 Sheet1 (即其並非保留字),
它應是代表 Sheets(1).
而 Sheet9 發生問題的原因是 我測試時所新建的 Excel 檔其預設值都只會開到 Sheets(3),
故而用 Sheet9 會發生錯誤.

然而因為 Sheet Name 並不會與 Sheet No. 完全相對應,
所以該 Sheet1 到底是不是 Sheet(1) 沒進 VBA 一般是看不出來的,
所以還是建議使用 Sheets("Sheet1") 形式再搭配 With 來簡化程式,
這是個人的寫程式習慣僅供參考.
作者: wind6424    時間: 2011-10-28 09:56

1. 當重複參照同一標的物時
可善用 With ... End With

2. 當 Copy 後若貼上後馬上又要貼上的話,
無須 ...
luhpro 發表於 2011-10-20 23:01

請問 luhpro 大
如果我的要貼的地方是變數,是不是不能這樣寫
因為我執行會有問題
正確的方式是要如何寫??
謝謝
y = Sheet1.Range("A1").End(xlDown).Row+1
Set Sh9R = Sheets("Sheet9").Rows(1)
Set Sh1R = Sheets("Sheet1").Rows(x)

Sh9R.Copy
With Sh1R
  .PasteSpecial Paste:=xlPasteFormats
  .PasteSpecial Paste:=xlPasteValidation
End With
作者: luhpro    時間: 2011-10-31 21:42

本帖最後由 luhpro 於 2011-10-31 22:27 編輯
請問 luhpro 大
如果我的要貼的地方是變數,是不是不能這樣寫
因為我執行會有問題
正確的方式是要如何寫 ...
wind6424 發表於 2011-10-28 09:56

使用 With ... End With 的關鍵就是 將 "重複使用" 的元件藉由 With 宣告,
而能改以 . 來取代以減少程式的大小及判讀(或解析)的複雜度.
有點像DOS指令用 * 來取代某一 "不確定大小與範圍" 的文字組合.

y = Sheet1.Range("A1").End(xlDown).Row+1
    (此處我會改為 y = Sheets("Sheet1").Range("A1").End(xlDown).Row+1 原因詳參 #39)
與底下 Sh1R 的
Sheets("Sheet1").Rows(x)
兩者共有的部份就是 "Sheets("Sheet1")" 了.

故而我們可以改為 :
Set Sh9R = Sheets("Sheet9").Rows(1)

With Sheets("Sheet1")
  y = .Range("A1").End(xlDown).Row+1
  Sh9R.Copy
  With .Rows(x)
    .PasteSpecial Paste:=xlPasteFormats
    .PasteSpecial Paste:=xlPasteValidation
  End With
End With

不過因為這個討論串是專門討論 "VBA的寫作技巧與增進效能" 的,
為避免模糊了首篇文章的討論焦點,
若還需要繼續討論建議請你另開新串討論為宜(你可以在開串文首打上:
此篇為延續 "VBA的寫作技巧與增進效能" 串中第 XX 篇的討論<建議要加上連結>),
就可以順利接續下去討論了.
作者: ag324    時間: 2011-11-1 17:08

本帖最後由 ag324 於 2011-11-2 10:33 編輯

受益良多 最近一堆處理資料工作都丟給我 害我狂寫程式
這些方法真的幫助很大

A1=B1 這種
我有一個疑問 如果是不同檔案的
有辦法只寫一行嘛!?

我現在都用COPY PASTE 來寫 就變得很多欄位
作者: howdyisme    時間: 2011-11-1 17:24

我只希望這能在MAC上用...回家來測試看看好了!
作者: minshing    時間: 2011-11-3 20:42

感謝分享重要觀念!受益良多!!
作者: yagami12th    時間: 2012-1-3 15:43

第一個問題:Sheets(2).Range("B1") = Sheets(1).Range("A1")<<這招超實用的,我也是從銷售管理的例子學來的,有些能錄好再回去編輯看,但學到後來就沒辨法用這招了。

程式學的有點挫折,不過還好發現這個論壇,好像又有希望了。
作者: s823342    時間: 2012-2-2 12:29

學了好多,原來我有這麼多不好的習慣!
作者: dobest    時間: 2012-2-2 21:54

受益良多 感謝分享 這方面我還是菜鳥 需要多多學習才行
作者: openpc    時間: 2012-2-16 10:34

讚!程式的功力是要前輩指引
作者: freeffly    時間: 2012-2-16 17:06

回復 1# Hsieh


    感覺學vba到某依種程度會有卡住的情形
   從版主回覆的資料都可以看到新東西
   不知道這些對於我們是新東西
   但是對於高手而言是普通技巧的東西
   這部份要怎麼突破?
    譬如字典、陣列..
作者: samenameboy    時間: 2012-3-14 17:48

這是一篇好章,值得學習。
作者: miketwbts    時間: 2012-3-14 18:13

當省則省,省的是多餘重複的程式碼;
當用則用,用的是不可或缺的程式碼。

謝謝版主分享知識, 這在很多層面都是共通的道理
作者: everyday99    時間: 2012-3-28 20:48

認真看了, 學習中,謝謝大大
作者: test61011    時間: 2012-4-10 19:57

好的觀念~很捧的經驗談~
很高興還在初學的我~可以看到板大的分享~
謝謝
作者: icestormer    時間: 2012-4-21 22:09

是呀 感謝超版的分享 太多東西要學了.. 這真是好地方^^
作者: bbojj    時間: 2012-4-22 10:15

感謝啟發,讓我又多學了一些東西!
作者: HUAMA    時間: 2012-5-1 17:00

感謝分享,許多重要的觀念
作者: alexlkklin    時間: 2012-5-16 11:26

小的寫程式就是只想要結果,
但是這個結果出來了,
只是程式就跑很久,
有時還擔心它會跑到掛點,
真的需要好好的修一下了.............
作者: waterfox    時間: 2012-6-6 22:23

多謝你的提示, 我都成日用Select的, 我以後會學下用少一點...
作者: s_chen    時間: 2012-9-24 20:56

感謝分享~學習更多的經驗!
作者: stillfish00    時間: 2012-9-26 19:31

因為先學過其他語言 , 一開始用錄製在學習VBA的時候
, 反而是常常忘記加select , 搞不清現在是作用在哪個工作簿/表/單元格
, 後來摸熟了也比較少用select去選取了...
作者: ppexcel    時間: 2012-9-28 18:29

好文章,謝謝分享~
作者: stillfish00    時間: 2012-9-30 02:46

回復 1# Hsieh

對了 , 突然想到 , 一般使用Range變數
Dim A As Range
Set A = ......

這樣最後也要用Set A= nothing釋放掉嗎?
好像比較少見
作者: jeff2004tpe    時間: 2012-9-30 15:04

回復 1# Hsieh
  好文章,謝謝分享
  好文章,謝謝分享
作者: blue10211    時間: 2012-10-4 10:56

很受用,從錄製巨集開始慢慢在學習,了解了很多~~
作者: x32438    時間: 2012-10-13 15:11

範圍選擇那邊真的受益良多,之前常常寫vba寫道當掉~!
作者: jak    時間: 2012-10-22 09:27

本帖最後由 jak 於 2012-10-22 09:29 編輯

最近開始練習錄巨集,最常看到的就是select:)
作者: 蔡jim    時間: 2012-10-22 11:38

確實有用吔...,對初學者有一定影響的,感恩了.
作者: robinhome0801    時間: 2013-1-8 08:04

専心看了一片,很實用.
作者: 水元素    時間: 2013-1-17 16:41

回頭檢查我還真的用滿多select .
謝謝板大的指導
作者: jcchiang    時間: 2013-1-22 12:28

感謝版主分享
看完之後受益良多
作者: lamihsuen    時間: 2013-1-23 21:39

回復 1# Hsieh

感謝大大的指導
    For Each cell In Range([A1], [A65536].End(xlUp))
 ......
Next
上列的語法真的受用不盡
作者: hsien33    時間: 2013-1-29 01:22

請教版大
像我的程式想簡化
但裡面有要變的欄位
必須要用 ActiveSheet.Paste
才能動作
請問這樣要如何簡化
我用您的方式
修改我的程式為
    Sheets("北區IC表").Range("B2").Copy Sheets("統計").Range("B2")
    Sheets("統計").Range("E3:E22").Copy Sheets("北一美富").Range("B3")
但原本該變動的"E3:E22"數值就不變動了
請版大幫我看看問題所在
感謝您

我原本的巨集如下

Sub 北一美富()
' 北一美富 Macro
在 2012/7/5 錄製的巨集

    Sheets("北區IC表").Select
    Range("B2").Select
    Selection.Copy
    Sheets("統計").Select
    Range("B2").Select
    ActiveSheet.Paste
    Range("E3:E22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("北一美富").Select
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   

    Sheets("北區IC表").Select
    Range("B3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("統計").Select
    Range("B2").Select
    ActiveSheet.Paste
    Range("E3:E22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("北一美富").Select
    Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("北區IC表").Select
    Range("B4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("統計").Select
    Range("B2").Select
    ActiveSheet.Paste
    Range("E3:E22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("北一美富").Select
    Range("D3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("北區IC表").Select
    Range("B5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("統計").Select
    Range("B2").Select
    ActiveSheet.Paste
    Range("E3:E22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("北一美富").Select
    Range("E3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("北區IC表").Select
    Range("B6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("統計").Select
    Range("B2").Select
    ActiveSheet.Paste
    Range("E3:E22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("北一美富").Select
    Range("F3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("北區IC表").Select
    Range("B7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("統計").Select
    Range("B2").Select
    ActiveSheet.Paste
    Range("E3:E22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("北一美富").Select
    Range("G3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub
作者: lin    時間: 2013-1-29 13:33

引用主文
其實可以這麼寫 --
 Sheets(2).Range("B1") = Sheets(1).Range("A1")
如果內容與格式都要複製,可以這麼寫 --
 Sheets(1).Range("A1").Copy Sheets(2).Range("B1")
作者: hsien33    時間: 2013-1-29 21:54

回復 1# Hsieh


請教版大
像我的程式想簡化
但裡面有要變的欄位
必須要用 ActiveSheet.Paste
才能動作
請問這樣要如何簡化
我用您的方式
修改我的程式為
    Sheets("北區IC表").Range("B2").Copy Sheets("統計").Range("B2")
    Sheets("統計").Range("E3:E22").Copy Sheets("北一美富").Range("B3")
但原本該變動的"E3:E22"數值就不變動了
請版大幫我看看問題所在
感謝您

我原本的巨集如下

Sub 北一美富()
' 北一美富 Macro
在 2012/7/5 錄製的巨集

    Sheets("北區IC表").Select
    Range("B2").Select
    Selection.Copy
    Sheets("統計").Select
    Range("B2").Select
    ActiveSheet.Paste
    Range("E3:E22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("北一美富").Select
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   

    Sheets("北區IC表").Select
    Range("B3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("統計").Select
    Range("B2").Select
    ActiveSheet.Paste
    Range("E3:E22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("北一美富").Select
    Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("北區IC表").Select
    Range("B4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("統計").Select
    Range("B2").Select
    ActiveSheet.Paste
    Range("E3:E22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("北一美富").Select
    Range("D3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("北區IC表").Select
    Range("B5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("統計").Select
    Range("B2").Select
    ActiveSheet.Paste
    Range("E3:E22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("北一美富").Select
    Range("E3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("北區IC表").Select
    Range("B6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("統計").Select
    Range("B2").Select
    ActiveSheet.Paste
    Range("E3:E22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("北一美富").Select
    Range("F3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("北區IC表").Select
    Range("B7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("統計").Select
    Range("B2").Select
    ActiveSheet.Paste
    Range("E3:E22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("北一美富").Select
    Range("G3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub
作者: alolen    時間: 2013-2-3 21:28

很棒的內容
要好好學習一下
作者: moci1231    時間: 2013-11-4 04:41

感謝分享 感謝分享
作者: ldneye    時間: 2013-11-4 09:48

[attach]16569[/attach]回復 1# Hsieh


     超級版主您好

有一點問題想跟您請教

我想用您這篇題到的 " 在不同sheet間複製貼上" 的技巧 來對照兩個不同sheets之間的公司的名稱  如果相符  就將該公司2005年的return複製貼上到sheet1

不知道為何沒辦法使用 ( 總是只有三個名字有抓到資料)

附上我的檔案 請您過目

謝謝
作者: takya    時間: 2014-1-15 10:12

謝謝教學.值得加以思考一下.
作者: a12723954    時間: 2014-1-15 14:18

感謝版主分享

讓我受益匪淺
作者: bulletin    時間: 2014-1-18 00:18

多謝分享
我才剛進到VBA的世界
真的都是透過巨集學習

雖然看了似懂非懂  但是我會時時放在心上的...
作者: wl02353427    時間: 2014-1-18 19:24

小弟我是新手,因為需要時才會寫一下VBA,所以技巧還不純熟,在此想請教一下各位前輩。

  最後兩行"釋放物件變數"是甚麼意思? 都是在寫在sub的最後嗎? 因為執行迴圈或其他動作時記憶下來的那些資料暫存要讓它消失嗎?! 可是當執行完一輪後都已經輸出到指定的地方了欸。

                                                                                                                                                           感謝前輩指導
作者: 小工友    時間: 2014-3-14 23:55

對新手的我受益良多,感謝分享,謝謝
作者: apao2000    時間: 2014-3-16 12:54

VBA的寫作技巧與增進效能

經由錄製產生的巨集,通常程式碼都會含有很多 Select,甚至往後自己寫的程式也 ...
Hsieh 發表於 2010-4-30 23:06



    睇完版主的講解就發覺我做的巨集真的有很多select, selection等字眼,因為一開始時就是利用錄制巨集的方法而不斷増加設備
    如果有 Worksheets("Sheet1").PageSetup.PrintArea = "$A$1:$C$60" 那 Range("A1:C60").Select 是多餘的?
作者: blue2263    時間: 2014-4-12 04:40

本帖最後由 blue2263 於 2014-4-12 04:43 編輯

請教版主
(a工作表)範圍資料,貼到(a工作表)其它位置,我用下列程式碼,是可行的,但如果要將(a工作表)範圍值,貼到(b工作表)範圍,程式碼應要如何改

Range(Cells(2, y欄 - x欄數), Cells(z列, y欄)) = Range(Cells(2, y欄 - x欄數), Cells(z列, y欄)).Value
作者: saysorryap    時間: 2014-4-16 14:37

看到開頭就知道一定要推了!!
作者: smart3135    時間: 2014-4-22 10:05

回復 1# Hsieh
所以樓主的建議是寫VBA時儘量是用敲的,而不是用錄製的嗎?但對像我這種新手而言,有很多語法如果不用錄製就無法寫出
作者: barrykuo    時間: 2014-7-25 11:03

除非程式就是要依使用者選取的物件來做動作,否則 Select 和 Selection 都是多餘的.非常同意。
之前只會利用錄製巨集,所以以為都是這樣做,看過論壇理的一些高手大大的例子才知道不需要這樣。除非需要利用當前選定時才需要這樣應用。
作者: diolin    時間: 2014-8-8 13:21

VBA 不會自動收垃圾嗎?

最後兩行沒寫, 不會自動釋放記億體嗎?
作者: scjiao    時間: 2014-8-8 21:24

謝謝大大,受教了,又學習到一課
作者: tsmc2330    時間: 2014-11-12 13:55

感謝版主分享
我剛開始使用VBA的確也是把錄製到的巨集照單全收
不過 現在已經能寫出比較有效率的CODE了
作者: wjt9093    時間: 2014-11-19 10:21

回復 1# Hsieh
請問不同的Sheet及Range,得不到正確結果!?應該入何修正:L
Sheets(a1).Range("A4:B36") = Sheets(a2).Range("B5:B37,E5:E37")
作者: Hsieh    時間: 2014-11-19 14:21

回復 93# wjt9093
儲存格範圍非連續儲存格,必須分開寫入
Sheets(a1).Range("A4:A36") = Sheets(a2).Range("B5:B37")
Sheets(a1).Range("B4:B36") = Sheets(a2).Range("E5:E37")
作者: wjt9093    時間: 2014-11-19 18:05

回復 94# Hsieh
感謝您的指導,不過發現執行後仍有問題,最後試很久,須加.value才成功!再次感謝您,讓程式更精簡!
Sheets(a1).Range("A4:A36").Value = Sheets(a2).Range("B5:B37").Value
Sheets(a1).Range("B4:B36").Value = Sheets(a2).Range("E5:E37").Value
作者: prince120101    時間: 2014-12-9 09:46

回復 1# Hsieh


剛進入 EXCEL VBA 世界   
看到這篇文章
多謝分享~~
作者: w445296265    時間: 2015-4-22 14:58

回復 1# Hsieh


    看完整個覺得「超讚」對於新手來說很實用!!!!!!!

作者: sghsgh1111    時間: 2015-4-27 00:40

嗯, 又學到一些東西
看了自己寫的, 還要多多改進
作者: gn001038600    時間: 2015-4-29 11:05

一開始學習的時候完全不會顧慮到處理時間,跑出來就謝天謝地了
但是越學到後面,處理的資料越來越多時,才發現能省則省的重要性
感謝大大分享優良觀念^^
作者: viggen93    時間: 2015-5-28 00:35

用心看完 感謝板主
值得好好地學習
作者: starry1314    時間: 2015-7-2 13:16

回復 1# Hsieh

版主~請問以下兩段程式碼,的doc是指word嗎?
那app是指當前程式碼嗎?
Set wrdDoc = Nothing '釋放物件變數
Set wrdApp = Nothing
作者: EGBT    時間: 2015-9-3 16:03

此篇非常實用~
作者: mdr0465    時間: 2016-1-28 10:24

版主,我是一名新手學VBA 的人,
我想問有好多時寫程式都會用到"APPLICATION.XXXXXX" 其實在什麼時候才會用到"APPLICATION"呢?
請賜教




歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)