2011年7月20日 星期三

Optimizing VBA

資料出處: http://www.cpearson.com/excel/optimize.htm

This page lists some suggestions and strategies for optimizing your Visual Basic For Applications (VBA) code, so that it will run faster.   There are few absolute rules for optimizing VBA;  you'll see the best increases in performance by streamlining the basic logic.

        
Accessing Cells In A Range


You do not need to use the .Cells method to access specific cells in a range.  For example, you can use


Range("MyRange")(1,2)   rather than
Range("MyRange").Cells(1,2)



See Alan Beban's explanation of this method for more details.
Related to this is the shortcut method of refering to cells.  VBA will allow you reference cells with [A1] rather than Range("A1"). While the [A1] syntax is easier to type, it is slower to execute than the Range("A1") syntax.

Calculation Mode


Normally, Excel will recalculate a cell or a range of cells when that cell's or range's precedents have changed.  This may  cause your workbook to recalculate too often, which will slow down performance.  You can prevent Excel from recalculating the workbook by using the statement:


Application.Calculation = xlCalculationManual
At the end of your code, you can set the calculation mode back to automatic with the statement:


Application.Calculation = xlCalculationAutomatic
Remember, though, that when the calculation mode is xlCalculationManual, Excel doesn't update values in cells.  If your macro relies on an updated cell value, you must force a Calculate event, with the .Calculate method, which may be applied to either a specific range (Range("MyRange").Calculate) or to the entire workbook (Calculate).

Collection Indexes


An individual item of a collection object may be accessed by either its name or by its index into the collection.  For example, if you have three worksheets ("Sheet1", "Sheet2", and "Sheet3")  in a workbook ("MyWorkbook"), you can reference "Sheet2" with either


Worksheets("Sheet2")  or
Worksheets(2)
In general, the index number method (Worksheets(2)) is considerably faster than the index name method (Worksheets("Sheet2")).
However, the number and order of items in a collection may change, so it is usually safer and easier to refer to items in a collection by their name, rather than their index number.
Constants


Whenever you can, declare values as constants, rather than variables.   Since their values never change, they are evaluated only once when your code is compiled, rather than each time they are used at run time.

Early Binding

This is closely tied with Specific Object Type Declaration.  If you're going to work with another application, such as Word, declare your OLE object directly, rather than as an Object type variable.  By doing so, a great deal of overhead is done at compile time ("Early Binding") rather than at run time ("Late Binding").   For example, use
Dim WordObj As Word.Application        rather than
Dim WordObj As Object
FOR EACH Loops


When looping through a collection it is usually faster than the FOR EACH statement rather than using the index.  For example, the first code loop is faster than the second:
Dim WS as Worksheet
For Each WS In Worksheets
    MsgBox WS.Name
Next WS
Dim i as Integer
For i = 1 To Worksheets.Count
    MsgBox Worksheets(i).Name
Next i

Range Objects Not Selection Object


Generally, it is not necessary to select a range before working with it.  For example, it is more efficient to use

Range("A1").Font.Bold = True



Rather than

Range("A1").Select
Selection.Font.Bold = True

Screen Updating


You can turn off screen updating so that Excel does not update the screen image as your code executes.  This can greatly speed up your code.

Application.ScreenUpdating = FALSE
Be sure to restore the setting to True at the end of your macro.  Older version of Excel would automatically restore the setting; Excel97 does not.

Simple Objects Rather Than Compound Objects


If you've got to make repeated references to an object, such a range, declare an object of that type, set it to the target object, and then use your object to refer to the target.   For example,

Dim MyCell As Range
Set MyCell = Workbooks("Book2").Worksheets("Sheet3").Range("C3")
'....
MyCell.Value = 123


By referring directly to MyCell , VBA can access the object directly, rather than resolving the complete path to the object each time.  This method is useful only when you are accessing an object several times during code execution.


Specific Object Type Declaration


If possible avoid using the Object or Variant data types.  These data types require considerable overhead to determine their types.  Instead, use explicit data types, such as

Dim MySheet As Worksheet
  rather than

Dim MySheet As Object  
Or
Dim NumRows As Long rather than
Dim NumRows As Variant
This is especially true with index variables in For Next loops, since a Variant type has to be re-evaluated each iteration of the loop.

WITH Statements


If you are using several statement in a row that apply to the same object, use a WITH statement, rather than fully qualifying the object each time.  For example,

With Worksheets("Sheet1").Range("A1")
    .Font.Bold = True
    .Value = 123
End With




Worksheet Functions


You can use Excel's standard worksheet functions in your VBA code, rather than writing the functions in VBA.  Since these are fully executable instructions in native code, rather than interpreted VBA code, they run much faster.  For example, use


MySum = Application.WorksheetFunction.Sum(Range("A1:A100"))   


rather than

For Each C In Range("A1:A100")
    MySum = MySum + C.Value
Next C

2011年6月24日 星期五

天氣概況~2011/06/24~米雷颱風快速接近~

文章出自 ptt.cc ID: caterlanse #1E0-Yff9 (TY_Research)

經過一天的繼續整理~米雷颱風的環流總算是正式穩定了下來~

原本分成兩大塊的對流逐漸趨於合併~

右邊的對流雲團向北旋繞~逐漸轉化為外圍旋臂雲帶~

左邊的對流雲團則繼續鞏固為圍繞中心的螺旋雲帶~

今天清晨的雲圖才總算有正常的颱風型態出現~

結構慢慢穩定後~強度也開始有增強發展的趨勢~

不過昨天到今天的整合過程明顯的讓米雷的走向偏北許多~

右側雲帶向北拉伸的結果~環流中心也被迫偏北調整~

跟原本預期可能較偏西北的方向有所差異~

而右側雲帶始終無法旋繞到中心西北方也是颱風中心偏西分量不足的原因~

繞不過來可能是因為昨天通過颱風以北的對流層高層淺槽分離出一個高空冷渦~

高層淺槽本體如預期迅速東移消滅~

但是這個分離出來的冷渦卻順著颱風外圍向西南跑到台灣上空~

導致颱風右側捲上來的雲層總是繞不過來~中心西北面一直空空如也~

相對的颱風本身的雲團卻受惠於冷渦東南側的良好輻散~出現顯著的爆發增強~

如此一來更顯現出不對稱的雲層分布結構~東強而西弱~

在這樣的情況下颱風中心只能往偏北方向調整~不如預期的走向西北~

而先前一直提到華北有西風槽會東移~並且造成副高壓向東調整後退~

目前來看這種情況在昨天逐漸出現~目前西風槽仍在華北~繼續東移中~

雖然有淺化的趨勢~不過已經造成副高壓延伸脊的西側向東退後~逐漸退到長江口附近~

在颱風過去走向偏北以及高壓後退的情況下~預估米雷通過台灣東北海域時距離會比較遠~

最靠近台灣的位置可能偏到124E以東~甚至到125E附近~

這樣一來對迎風面地區的影響會比較小一些~

不過米雷的速度很快~颱風本身強度也還在繼續增強~範圍有擴大趨勢~

因此北部東北部東部等地的天氣將會很快有變化~

目前的雷達顯示東邊海面的降雨回波快速在靠近中~宜蘭地區已經有下雨的情況~

稍後回波繼續進來~台北桃園還有花蓮台東等地也都會開始有下雨的現象~

颱風氣流場也已經影響到迎風面地區~沿海高地的風勢比昨天增強許多~

而隨著颱風持續向台灣東北海域靠近~

慢慢的外圍雲雨帶會影響到新竹以南地區~到了下午以後~中南部也逐漸有下雨的機會~

迎風的北部東北部東部地區隨著時間越晚~風雨的感受也會繼續加大~

今晚到明晨間颱風會移動到離台灣最靠近的位置~

雖然看起來不會很接近~但是米雷的環流範圍頗大~因此北台灣迎風面的感受仍會比較明顯

有陣雨並且有較強的風~特別是在沿海以及高地風勢會更強~

提醒北部東北部山區的朋友要注意颱風的環流可能帶來較大的累積雨量~

花東地區今晚可能慢慢進入背風面~看起來天氣受到的衝擊會比北臺灣小得多~

中部南部今晚在沿海有較強的偏北陣風~颱風外圍的雲雨區也可能帶來降雨的機會~

明天清晨以後米雷會繼續快速北上~逐漸要開始遠離台灣附近~

北台灣的風雨應該到中午以後就會趨緩了~

不過緊接著要注意的是米雷的西南氣流將開始影響台灣~

中部南部還有東南部地區的降雨從明天下半天以後有逐漸增加的趨勢~

後天週日受到西南氣流的影響~各地仍有下雨的機會~

特別是中部南部東南部地區要留意大雨或豪雨發生的情況~山區雨量可能會較明顯~

沿海的風浪受到西南氣流影響~不會很快的小下來~

目前來看西南氣流大概會持續到下週一週二這兩天~

雖然預報資料看起來已經沒有前幾天預測的那麼驚悚~

但是提醒中南部以及東南部的朋友仍不可以掉以輕心~仍然會有較大的降雨出現~

特別是山區以及低窪地區要多加注意淹水山崩等災害出現的機會~

下週三以後高壓慢慢增強~西南氣流的位置可能逐漸偏向華南沿海~

台灣這邊的降雨可望趨緩~慢慢恢復到夏季午後雷雨的天氣型態~

而米雷離開台灣附近後將快速的沿著高壓邊緣逼近長江口~

包括浙江沿海以及上海等地在週六下半天起風雨都會增強~

週六晚間到週日上午之間會是這些地方風雨感受最明顯的時間~

提醒要前往浙江北部以及上海的朋友務必多加注意~

週日上午以後米雷掠過上海以東~像裝了噴射渦輪一樣將會繼續高速進入黃海~

目前來看很可能威脅到山東半島~遼東半島以及北韓等地

下週一這些黃海周邊的區域就會明顯感受到風雨的情況~

不過來的快去的也快~下週二左右米雷應該就會北上到西伯利亞東部並迅速轉化~

照目前的趨勢來看米雷本身對台灣的威脅較為減輕~

但是還是很有可能帶來不小的降雨~特別是西南氣流的部份~

請大家仍要嚴加戒備~不要因為颱風本身的影響減小就大意輕忽後續降雨的威脅~

以上簡單供參考囉~

2011年6月17日 星期五

衛生署"塑化劑"追蹤進度(6月17日)

文章出自 ptt.cc ID: mydreamcome #1DtYEqh4 (Gossiping)

本篇爆掛重點是要打開所有的連結來看
 ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄
衛生署更新資料的話,我會盡快更新爆掛
 ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄

Blogger現在新增+1的功能

現在blogger文章的部分
新增一個+1的功能
就是在原本 顯示分享按鈕 後面多了一個+1
+1這個功能其實就類似Facebook的讚

如果原本有用這功能的
可以先把功能取消掉儲存
再啟用一次後儲存
應該就會出現了