2012年12月23日 星期日

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身體質量指數,直接在儲存格上使用函數就可以計算的出來。
不過如果講到便利性的話,還是用巨集的方式處理會比較好。

2012年12月16日 星期日

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

成績輸入流程

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

image

2012年12月11日 星期二

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 程序來撰寫程式。如下,利用訊息方塊顯示目前的日期時間。

image

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

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

image

事件程序

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

事件名稱的命名規則
事件程序的名稱是以「物件_事件」方式命名,如上例 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

2012年12月8日 星期六

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,捨去

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

2012年12月1日 星期六

Excel VBA 自動流水號

Excel VBA d001d1自動流水號

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

Related Posts Plugin for WordPress, Blogger...
// Dnow Function