麻辣家族討論版版 - Powered by Discuz! Board

標題: [轉貼] 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

Sub Ex()
Dim Book As Workbook, Rng(1 To 2) As Range
With ActiveWorkbook.ActiveSheet
Set Rng(1) = .Range("A" & Rows.Count).End(xlUp)
For Each Book In Workbooks
If Book.Name <> .Parent.Name Then
Set Rng(2) = Book.Sheets(1).UsedRange
Rng(1).Resize(Rng(2).Rows.Count, Rng(2).Columns.Count) = Rng(2).Value
Set Rng(1) = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next
End With
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/)