Excel VBA 標準體重計算

d006d1 標準體重計算
在介紹程式內容前，我們要先理解一下這些和體重有關的公式(註：以下的體重單位為 kg、身高單位為 m)

• 身體質量指數(Body Mass Index，縮寫為BMI)：
  BMI = 體重  ÷ (身高 × 身高) 
• 標準體重算法1：
  男性標準體重 = (身高 - 0.8) × 70
  女性標準體重 = (身高 - 0.7) × 60
• 標準體重算法2：
  男生標準體重 = (身高 × 身高) × 22 × ( 1 + 0.1 )
  女生標準體重 = (身高 × 身高) × 22 × ( 1 + 0.05 )

像範例這類的簡易的公式套用，使用excel內建的函數是可以解決的。
早期我也是直接在儲存格上打一函數來計算。
像範例中有提到的BMI身體質量指數，直接在儲存格上使用函數就可以計算的出來。
不過如果講到便利性的話，還是用巨集的方式處理會比較好。

Excel VBA 自製全誼成績系統之成績輸入表

成績輸入流程

在使用全誼成績系統時，成績輸入不管是系統設定還是成績輸入，步驟都很繁瑣，這裡提供一個替代方案供解決：使用自製的excel表格來簡化老師使用的困難

Excel VBA 基礎：宣告變數

變數是執行程式時存放值的地方。變數可以在程式碼中自由使用，不過通常我們會在程式開頭就對程式內的變數先行「宣告」。如下範例

Dim myName
Dim myNickName As String
Dim myDate As Date
Dim myAge As Integer
Dim myHeight As Single, myWeight As Single

myName = "豬腦爸"
myNickName = "D NOW BA"
myDate = #11/19/1981#
myAge = 70
myHeight = 162.55
myWeight = 61.3

列1~列5 就是在宣告變數，列7~列12就是將變數賦值。完整宣告變數方式是：

Dim 變數名稱 As 資料型態

變數名稱可以自訂，沒有太大的限制，不過儘量不要和內建的函數、物件名稱重複，而變數名稱的命名也有人有制定參考標準，可參考
微軟建議的Visual Basic編碼慣例、Visual Basic命名慣例
http://dnowba.blogspot.tw/2011/10/visual-basicvisual-basic.html

資料型態，和微軟的SQL資料庫的定義方式大致上是相仿的，可以參考
資料型別
http://dnowba.blogspot.tw/2011/10/blog-post_18.html

如果像程式碼列1省略資料型態，那麼變數就是自由型態 (Variant) ，不過建議對於變數的值有明確的認知的話，最好還是指定資料型態。指定資料型態比較可以有效的利用記憶體空間。例如列4和列5，年齡、身高都是數字，不過年齡一般來說是整數，所以用只佔記憶2個位元組的integer；而身高、體重一般表示方式可能會有小數，就用佔8bytes的單精度浮點數Single。

要在1行內宣告2個以上的變數時
範例中，因為myHeight和myWeight 都要宣告資料型態為Single，所以可以寫成  Dim myHeight As Single, myWeight As Single，注意不可寫成
Dim myHeight , myWeight As Single，否則 myHeight 會被當成Variant

Excel VBA 基礎：程序

程序 (procedure) 是 VBA 執行程式的單位。程序主要有 Sub 程序和 Function 程序和事件程序，以下分別簡述之

Sub 程序

在 Excel VBA 當中，通常是使用 Sub 程序來撰寫程式。如下，利用訊息方塊顯示目前的日期時間。

程序名稱的命名規則
範例中，我們可以使用中文名稱以辨識程序別，關於程序名稱的命名規則如下：(1) 第一個字不能使用數字 (2)不能使用空白字元 (3)不能使用如「!、@、$」等其他符號 (3)字元數不能超過255個半形字。

Function 程序

Function 程序是執行後，會傳回值的程序。常用在使用者定義的函數上。如下，根據出生年月日，計算年齡傳回結果。

事件程序

事件程序是指由某個動作引發而執行的程序，例如「按下按鈕」、「打開活頁簿」等動作，可以引發程式自動執行。除了工作表或活頁簿的事件程序以下，使用者定有表單(user form)也可以撰寫事件程序。如下，打開活頁簿時會出現訊息

事件名稱的命名規則
事件程序的名稱是以「物件_事件」方式命名，如上例 Workbook_Open 就是指當「活頁簿_打開」時引發的程序。名稱是不可以自已更改的，而大部分的事件預設名稱也都還算可以清楚辨識。

Excel VBA 基礎：物件階層、集合及成員

Excel 的物件階層構造

zZnfk5owEMf/Gmd6D+cQQIRHsWf70plO7+F6j1GiMkZiY+zp/fUNkPAjAY9isPXh5rIkS/jsd3eJjpz5/vyFwsP2G4kQHtlWdB45n0e27VsW/5saLophQ+MoNwFhOMUROtZMjBDM4kPduCJJglasZlsTXHd2gBukGZ5XEOvWlzhiW7E52yvtX1G82crbAC/IrxzZRfqI0BqeMHvMTHZ++WyJBWL6RRkfYFLbwDsh+5qBomP8Xt/kOq4/7JLQCNHcJGDiONlVKTlPI2dOCWH5f/vzHOE0LBJ5vmzRcrWAQ1HCuiwQD/8b4pPY+uxwwPEKspgkGjdKTkmE0qVg5ITrGOM5wSR/IGeRfVI7SdhzjgL4fLyhMIr5fmpzPf4R90aUoXPr/ksqXKiI7BGjlzRcYv8Co9AokBp9K6UB5JxtRRaesEHBflN4LmHxfwSvZnaOxu6F0N2SkN1H4EpAttsGKHSMAAIqIf+OhFyNkEYGJdGMUvLGRwlJuDFE55j95ENrPBGjV8ENRbwSiKWEsi3ZkATip9IaIrzMXKXIuIhxakr4pjN/cvAqnGdbSX1eR8u3S050hWoJwyDdIFbRQWsAHkGLRJvACi/fScy3ULhwFQ+B4iHfn1hUTXbFj6oFe6I4yp9Kc5SFuXiyTpGf6LkRJxGPzc2ZEWbFw0TpULC67h0zw9P4dMmMj/VvVuyOLvZJM1PBcGoAzbSxrB63CLH/WD1FLt1DPX4f9XSXQDXe097xbqlBvsLN71bMetQgWXgrmOZbSA2oaD5fLIJgABU50zuqCOhFWpdRpeTIKqTXpdurTlVyedxamU0VZE2JZzUg69F4VbG6qovOnbdt0+Y7L9Bbyw+Y8AjcrPogSHVvQPUFRkkD3FP1en+5qvq2Rtv1tbVX4502ZIXXqRKbzIKLIt6/Vr0SZ9WPQdE3dUR+N0ueyI6fRrb365Qeq0MIx2d8LMcP4+IFozptuaxOyXKocnXGk2Y2sx9Ki6Yi9RycjivpZGUfmXpiWZqKR0bJDsmZmbwMdBql7epymQyVcdLvtYxr/2ZBpFcdSnZC9i2pr5vIOGpp1hPJbyDjmCADhiDjW4uFETKqaJrQTIZCYw+BxvNC20gLc/x/iEb/4ulqBxMwqmXG6tqW2gEE7lie8tVX+QHexNwAjKf126mlqWtXcgMldGCwttTpC7ChI2W7d42U4xqLVPGWLLftDhepfmchs5Hy7bEap4ayMtDZxpv0C5LqB6gH+t5B4sPyh5B8evlDlfP0Bw==

在Excel的VBA中，我們使用程式碼控制Workbook(活頁簿)、Worksheet(工作表)、Chart(圖表)，這些對象都是物件。在Excel裡，物件以Application為最上層，以下的物件都是階層性的設計，例如上圖中的程式：

Workbooks(“aa.xls”).Worksheets(“bb”).Range(“A1:A2”)
表示在「aa活頁簿」的「bb工作表」的「A1:A2儲存格」，透過階層性的設計物件就會非常清楚。

此外，在每個物件當中包含的成員(member)，有執行動作的方法(method)、取得或設定狀態的屬性(property)。

省略母物件
參照物件的上一層物件稱為母物件，
如果參照的工作表位在作用中的活頁簿，則程式只需輸入：
Worksheets(“bb”).Range(“A1:A2”)

如果參照的儲存格位在作用中的工作表，那麼程式又可以更簡化：
Range(“A1:A2”)

撰寫時依著參照位置的不同而可以有所省略以節省撰寫程式的時間，像最上層的Application通常情況下都是不用寫的。(excel內建所有的VBA都源自於Application)

集合和成員

相同稱類的聚集，稱為集合(Collection)。多個Worksheet的集合就是Worksheets，意即除了作用中的Worksheet，透過Worksheets集合還可以參照到其他的成員，如在一個Workbooks有多個Worksheet，要指定某個Worksheet，就可以透過像「Worksheets(“bb”)」的方式。

zZffb5swEMf/GkvbQ6MYBwKPDUu2l0mT+tD1kcYOsWrszDEt6V8/E5ufhjZak6yKFOGv4bj73PlsAIqz4rtMdtufAhMGvCkuAPoGPC+cTvV/KRx6QiopNhK0Qk4x2XckJQRTdNcV14JzslYdbSNY19guSYkj3K0T5qr3FKutdc4LGv0Hoem2eg0MIjOzV4fKBiabJGfq5ih5ZrqYmrmZvf1gx2H9Qt5x4FWIrCNIsqevXSc31HphyT0KiYnsSIzypzYltAQolkIoc5UVMWFlWirk5rHVyGztqyRcnfKADf45Ybl13YElRc4xKe+31p+JVKQYfWEThq4sIjKi5KHka2Z9G7ctKlhxeGlyiap7tq08etCKiaWV1qab8PSFjXA4WuREey/k035LiNo7cW8EV3cmo94MoEUqE0x1lLFgwuQQRVEcr1ZnoFKXg8USuVRgMEAlOAOUmVsCywhECxAGYOkD7UoYgnJ5oSLRGNaCMb2IqeD/ldj8fWDoQsD8N6roiyb1Jy9X7wI2l18dVANo4jiKzoKmrpRqjc1dOHXBteHMzgBnfhoc77PA8QYa0MXghKfBQZ8GzjUrJxroQ0HZe/TaLvsQArew1Ycykj3qvfSaeAK/Vzq+Q2d+ITjV7vfWRk04vpVSvOgRF1yLCx2XPPzW42k1eCgHE+s3wc5Rq4dC2xe5XJNOhlQiU6JavXAU2E2vS98gfxyFtfJLUO3CKPP+McD4Zx9qH3R6diAKJzBsftW2V/fInmcmSMfuMU91oKelDv5T6gqqjpmbaM/M8OF4BLtGVsPhrH6ogE84aY5TgC0E5wx0/uFA9bA5rZvqaL6m0PIv

Excel VBA 四捨六入五成雙的設計

Excel VBA d005d1四捨六入

現在被廣泛使用的取概數規則，除了四捨五入以外，還有四捨六入五成雙規則。

四捨五入簡化規則中，因逢五就進，會造成結果的系統偏高，誤差偏大。因為它進位的「機率」高於捨棄，在統計學上會產生偏差，解釋如下：

取位位值是：1、2、3、4 →機率 4/9
取位位值是：5、6、7、8 、9→機率 5/9

如上面舉例，一個數字在四捨五入中，捨去的機率比進位還低，這樣子不大公平，所以後來有了四捨五入六成雙規則。所以我們如果要使用四捨六入五成雙的設計，就得自已設計一個，在設計之前，必須先了解這個數值規則，可以參考：http://zh.wikipedia.org/wiki/數值修約規則，我自已理解後，認為規則應該是：

一個數字，取概數到n位的話，那麼一樣是看(n-1)位的數字

1.如果數字是4以下則捨去，6以上則進位

2.如果是5，那麼：
   (1)如果5後面的有效數字中，有非0的數字，進位
   (2)如果5後面的有效數字中，都是0：
        a.如果5的前面是奇數，進位
        b.如果5的前面是偶數或是0，捨去

整個過程還是在對「進位」和「捨去」，不過規則上修改的比較複雜就是了，以下就把過程寫下來

Excel VBA 自動流水號

Excel VBA d001d1自動流水號

流水號的設計可以幫使用者解決一堆工作的資料排序問題，例如：通訊錄資料、學生成績名次、會議座次編號。如本例的採購表單，使用者使用巨集可以快速在所需要的欄位上自動填加流水號。

Dnow 試題分析(雙向細目)試算表

這個試算表是五年前做的
因為最近期末評量由我命題
為了檢視自已命題的方向和配分的方式是否正確
又把這張表再搬出來一次

製作這個Dnow 試題分析(雙向細目)試算表的原因
說起來蠻好笑的…五年前以替代役的身份參加了某教授的課程
測驗統計與命題分析…李坤x的那套
三個整天的課程，讓我快吐血…
從第一天由教授講解後
接下來二天，全部是實作，
最累的部分除了分辨題目是二元/多元、題目的知識向度外
我想測驗前雙向細目表的「統計」和「製表」更讓人望之却步

個人覺得不可思議的是在研習後二日
一個教「統計」的專業講師
竟然給我拿「計算機」在那邊算雙向細目表
教授都拿計算機算半天了，可想而知台下是什麼盛況了

所以我後來就依據他的理論，試著製作這個試算表
這個試算表改過好多次了，後來歷經二次「優良試卷評選」的洗禮後誕生了
這麼好的試算表，我當然要毛遂自薦
Dnow 試題分析(雙向細目)試算表可以讓你節省「製作」「統計」雙向細目表的時間
可以省多少時間…我只能說，從命題後到製表，熟練的話只要10分鐘
省下的時間全還你去檢視試卷的核心
一般人在做命題分析時，常常因為怕改雙向細目表，然後索性不改試卷內容，
使用Dnow 試題分析(雙向細目)試算表 ，我保證你可以從此不再畏懼。

點我下載：Dnow 試題分析(雙向細目)試算表

使用方式我就不贅述了，在檔案裡有大概的使用方式
特別提醒一點，這裡我只做到測驗前的部分
後測的統計有提供表格但已年久失修，我也沒在維護了
這個試算表對我而言是重要的里程碑…
(1) 寫程式之前一定要熟悉該系統是怎麼運作
(2) 寫程式之後二、三次的實際操作才能確保程式沒有太致命的bug
(3) 寫程式過程中，有時要打破 / 跳脫現有的格局/規範 (←這是我製表完後覺得最有價值的部分)

Excel中如何使用陣列公式

如果你曾寫過程式，對「陣列」(Array)這個名詞一定不陌生

如果你曾使用過Excel製作複雜的計算，那你應該也碰過很麻煩的撰寫函式的過程，甚至可能難到你不能用一般函數來解決，那麼原因十之八九是因為陣列的關係

如下範例1，如果設計了這樣的表格，那麼你應該知道總銷售額的寫法…每一行都要寫，這裡當然沒什麼問題。但是萬一業務代表變多了…車型也不只二種，那麼惡夢就來了。

試一試用陣列處理吧：