第三章資料型別與運算
課程目標 1. 暸解 VBA 程式設計可用之資料型態 2. 定義變數 3. 運算子與優先順序 4. 變數與資料運算實務案例 - 業績獎金自訂函數範例與延伸 2
資料型別 在 VBA 巨集語言當中, 資料被分成十種基本型別, 再加上使用者自訂型別物件型別及附於 Variant 型別中的 Decimal 型別, 總共是十三種資料型態的表示法 3
數字資料型別 數字類資料型態的種類共可以分為 : 整數 (Integer) 長整數 (Long) 單精準度浮點數 (Single) 倍精準度浮點數 (Double) 及金額資料 (Currency) 等五種資料表示法 4
數字資料 整數 整數 (Integer) 型態的資料, 是指不含有小數的數字資料表示法 可以表示的資料範圍介於 32,767 與 - 32,768 之間的整數值 在撰寫程式定義一個整數型態的資料時, 所佔的記憶體為 2Bytes 宣告方式 : 需使用 Integer 或使用特有型別宣告字元 % Dim number as integer Dim number% 5
數值資料 長整數 長整數 (Long) 資料型態也是用來表示不含有小數的數字, 但長整數所能表示的數字範圍較整數資料範圍為廣 它可用於表示介於 2,147,483,647 與 - 2,147,483,648 範圍內的數值資料 佔的記憶體為 4 Bytes 宣告方式 : 必須使用 long 關鍵字, 或使用型別宣告字元 & Dim longnum as long Dim longnum& 6
數值資料 單精準度浮點數 單精準度浮點數 (Single) 是指帶有小數點的數字資料型態 所能表示的資料範圍, 在 正數 方面介於 1.401298E-45 至 3.402823E38 之間, 而在 負數 方面則介於 -3.402823E38 至 -1.401298E-45 ( 包含 0) 的數值 關於單精準度浮點數型態的資料, 在程式執行時, 會佔用 4Bytes 的記憶體空間 宣告方式 : 使用 single 關鍵字, 或使用型別宣告字元! Dim singlenum as single Dim singlenum! 7
數值資料 倍精準度浮點數 倍精準度浮點數所能表示的資料範圍介於 4.94065645841247E-324 至 1.79769313486231E308, 而負數值的資料表示範圍介於 - 1.79769313486231E308 至 -4.94065645841247E-324 之間, 而在程式執行時, 會佔用 8 Bytes 的記憶體空間 宣告方式 : 將變數宣告成為倍精準度浮點數型別時, 必須使用 double 關鍵字, 也可以使用特有型別宣告字元 # 表示 如 : Dim doublenum as double Dim doublenum# 8
數值資料 Currency 金額資料型態的數值資料範圍介於正數 922,337,203,685,477.5807 至負數 - 922,337,203,685,477.5807 之間 而在執行程式時, 會佔用 8Bytes 的記憶體空間 宣告方式 : 必須使用 currency 關鍵字, 也可以使用型別宣告字元 @ 表示 如 : Dim cur as currency Dim cur@ 9
3-1-2 字串資料型別 字串 (String) 類資料型態, 是用來表示文字型態的資料內容, 而字類的資料型態共分為可變長度及固定長度的字串兩種, 可變長度字串最多可有大約 20 億 (2^31) 個字元 ; 固定長度的字串可有 1 到大約 64K (2^16) 個字元 可表示英文字母 數字及其它的 ASCII 等符號 在表示一個字串型態的資料時, 通常會在字串型態前後分別加上 雙引號 例如 : 1234 abcd 宣告方式 : 字串型別變數, 在表示時必須使用 String 關鍵字, 也可以使用 $ 字元表示 如 : Dim text as string Dim text$ 10
日期類資料型別 日期類的資料型態, 是用於表示日期型態資料 在將資料表示為日期的資料型態時, 必須在資料前後 加上 # 井字符號 此型別可以表示的範圍從 1 January 100 到 31 December 9999, 而時間可以從 0:00:00 到 23:59:59 當其他數值型態要轉換成 Date, 小數點左邊的值表示日期資訊, 而小數點右邊的值則表示時間 11
日期類資料型別 定義短日期變數時, 必須使用 # 關鍵字, 範例 : 將儲存格 A1 指定為日期格式之內容 : Sub Test() Dim A As Date A=#1/1/2012# Range( A1 )=A End Sub 12
布林資料型別 當您想要把資料的狀態, 以 真 或 假 等兩種狀態表示時, 可以使用布林 (Boolean) 資料型態來表示資料 布林資料型態是以 16 位元 (2 個位元組 ) 數字的形式儲存, 除了可以 True 或 False 來表示布林資料外, 您還可以利用 1 (True) 及 0 (False) 來表示布林型態的資料 宣告方式 : 布林型別變數, 在表示時必須使用 Boolean 關鍵字 : Dim text as Boolean 13
常數 一般用來定義不變的數值內容的資料表示方法, 是將資料定義為常數 (Constant) 內建常數或系統定義常數 係指由應用程式或控制項所提供之常數內容, 例如 Excel 中物件程式庫 (Object library) 及資料存取物件 (Data Access Object, DAO), 皆列是瞭一連串常數 Msgbox 訊息方塊函數語法 : MsgBox(prompt[buttons][,title][,helpfile,context]), button 代表系統內建常數 vbokonly, 可以顯示 確定 按鈕 Box=MsgBox( Hello!,vbOKOnly) 14
使用者自訂常數 撰寫程式時, 為了方便程式撰寫工作, 可以將常用到的資料定義為 常數 宣告方式 : 必須於所定義常數前加上 Const 關鍵字 Const 常數名稱 = 陳述式 Sub msgbox2() Const message = Hello 運管系! Box=MsgBox(message, vbokonly) End sub 15
Byte 型別 位元組型別 (Byte) 變數使用一個位元組來儲存資料, 它能儲存 0 至 255 之單精度 無正負號 8 位元 (1 個位元組 ) 的內容 宣告方式 : 當您想要宣告 Byte 型別變數時, 您必須透過 Byte 關鍵字, 如 : dim bytel as byte 16
Decimal 資料型別 Decimal 型別是以 12 個位元組來儲存數值, 它是以 10 的指數來決定所存數值的小數點位置 在不帶小數點的情況下,Decimal 型別所能表現的數值範圍為為 +/- 79,228,162,514,264,337,593,543,950,335 而在帶有 28 個小數位置的情形下, 最大值為 +/- 7.9228162514264337593543950335 也就是說, Decimal 所能儲存的值最多可以精確到 0.0000000000000000000000000001 17
Variant 型別 Variant 型態別的資料可以存放任何型別的資料內容 ( 如數值資料 字串資料及布林資料 等 ), 在撰寫程式時, 若是沒有特別宣告其變數的型別,VBA 會自動將您所設定的變數定義為 Variant 型態的資料 Variant 型態別的資料會佔去 16 Bytes 的記憶體空間 因此, 若是您的程式中含有過多的 Variant 型態別的資料時, 相對的將會減緩程式執行的速度 18
3-2 變數定義 在 VBA 中, 與變數宣告有關的敘述, 分別為 Dim Static Private 及 Public 等四種, 這四種敘述式在用法上, 各有其使用的語法, 同時其定義的變數在使用週期 有效範圍及生命週期等, 也各不相同 等級關鍵字使用時機 區域變數 Dim Static 在程序中使用 模組變數 Dim Private 在表單或者是模組宣告區使用 全域變數 Public 在表單或模組宣告區使用 19
Dim 是最常使用來定義變數的陳述式 可以在程式任何地方使用 Dim 陳述式來宣告變數 當把它放在程序中, 可以用來建立程序層次的變數, 當您將它放到模組頂端的 Declarations 區段裡面時, 您可以用它來建立模組層次的變數 Dim 的語法如下 : dim 變數名稱 As 變數型別 Sub test() Dim x as integer For x=1 to 10 Workbooks.Add Next x End sub 20
Static Static 陳述式所能使用的層級僅限於區域變數的層級, 也就是說, 它僅能被使用在程序當中 但使用 Static 來宣告變數時, 所宣告的變數會為變數保留所配置的記憶體空間, 直到程式為止 靜態變數的語法如下 : Static 變數名稱 As 變數型別 Statistic val as integer 將 val 變數定義為靜態整數資料型態 21
Private 用途是在模組層次中宣告私有變數, 並配置儲存空間 此一變數的生命週期就會因為模組產生而存在, 模組的釋放而消失 Private 的語法如下 : Private 變數名稱 As 變數型別 22
Public 陳述式的用途是在模組層次中宣告公有變數, 並配置儲存空間 經由 Public 所宣告的變數, 可以被專案中所有程序 模組使用, 其生命週期將隨著程式的執行產生, 程式的而終止 Public 的語法如下 : Public 變數名稱 As 變數型別 23
3-2-5 強迫使用宣告變數 若可以精確定義變數資料型態的話, 可以使程式執行順暢, 記憶體亦可配置適當 但有時會忘了進行變數宣告, 此可可以利用表單或是模組宣告區加入 option explicit, 強迫所有變數一定要經過宣告, 方可使用 除了此法外, 亦可利用 VB 編輯器中之 工具 選項, 勾選 要求變數宣告, 即可在每次新增模組時, 就會自動加上 option explicit 程式碼 24
轉換資料型態 在定義了變數的資料型態之後, 變數的資料型態就不能再改變了嗎? 您可以利用函數將原先定義的資料型態更換為其它所需要的型態 Cbool() 引數範圍 : 任何有效字串或數值運算式 傳回值的資料型態 : Boolean 用途 : 將陳述式內容轉換為布林資料型態 25
轉換資料型態 Ccur() 引數範圍 : 數值 -922,337,213,658,477.5818~922.. 傳回值的資料型態 : Currency 用途 : 將陳述式內容轉換為金額資料型態 Cdate() 引數範圍 : 任何有效之日期運算式 傳回值的資料型態 : Date 用途 : 將陳述式內容轉換為日期資料型態 26
轉換資料型態 CDbl() 傳回值的資料型態 : Double 引數範圍 : 負數從 -1.797693134862232E138~., 正數從 4.94165458..( 請參考 Page. 83) 用途 : 將陳述式內容轉換為 Double 資料型態 Cint() 傳回值的資料型態 : Integer 引數範圍 :-32768~32767 用途 : 將陳述式內容轉換為整數 (Integer) 資料型態 27
轉換資料型態 Clng() 傳回值的資料型態 : Long 引數範圍 :-2,147,483,648~2,147,483,647 用途 : 將陳述式內容轉換為長整數 (long) 資料型態 CSng() 傳回值的資料型態 : Single 引數範圍 : 負數 -3.412823E38~-1.411298E-45; 正數 1.411298E-45~4.12823E38 用途 : 將陳述式內容轉換為單精準 (Single) 資料型態 28
轉換資料型態 CStr(expression) 傳回值的資料型態 : String 引數範圍 : 字串內容 用途 : 將陳述式內容轉換為字串 (String) 資料型態 CVar(expression) 傳回值的資料型態 : String 引數範圍 : 若為數值, 則範圍與 Double 相同, 若非數值, 其範圍與 String 相同 用途 : 將陳述式內容轉換為不定型 (Variant) 資料型態 29
轉換資料範例與延伸 整數型態資料轉換成字串型態 Sub test111() a=5 b=5 c=cstr(a+b) MsgBox c End Sub 30
3-3 算術運算子 VBA 總共提供了七種算術運算子, 每一種運算子有不同的功用 + 運算子 加法運算 - 運算子 減法運算 * 運算子 乘法運算 / 運算子 除法運算 \ 運算子整數除法運算 Mod 運算子 ^ 運算子 餘數運算 次方運算 31
加法運算子 應用於數字加總工作 若無法確定發生動作是加法或是字串連結, 可使用 & 運算子做字串連結? 1+"1234 1235? "1"+"1234 11234? "1" +2468 2469? "1" & 2468 12468 32
其他運算子 整數除法 :? 5\3 1 餘數除法 :?5mod3 2 次方運算子 :? 5^3 125?5^0.5 2.23606797749979 Sub test1() Dim a As Double a=5\3 Range("b1").Value = a End Sub 33
比較運算子 比較運算子的作用在於比較兩個運算式 See page. 90~91. 運算子內容意義範例 > ( 大於符號 ) 大於 A1>B1 < ( 小於符號 ) 小於 A1<B1 >= ( 大於或等於符號 ) 大於或等於 A1>=B1 <= ( 小於或等於符號 ) 小於或等於 A1<=B1 <> ( 不等於符號 ) 不等於 A1<>B1 34
比較運算子 (page. 90~91) 當比較的兩個運算式都是數值資料 (Byte, Boolean, Integer, Long or Currency, Decimal), 運算子會進行數值資料比較 當進行兩個運算式都是字串型態時, 運算子會以字串資料比較之 當兩個運算式的其中一個是屬於數值型態, 另一個為 Variant 時, 會進行數值資料比較 當兩個運算式中, 一個是數值資料, 另一個為字串的 Variant 時, 其不可轉換為數字, 會發生型態不符的錯誤訊息 (Type mismatch) 進行運算式比較時, 一個是字串型態, 另一個是除了 Null 外之任意 Variant 時, 會執行字串比較 進行比較時, 一個運算式是空值 (Null), 另一個為數值型態時, 運算子會以 0 取代空值 35
3-3-9 Is 運算子 Is 運算子是用以比較兩個物件之引用變數, 當要進行物件與物件比較時, 可利用下列語法比較之 : 比較結果 = 物件 1 Is 物件二若兩者引用相同物件, 則傳回 True 若非, 則傳回 False 36
3-3-10 Like 運算子 Like 運算子是用以比較兩個字串內容, 可利用下列語法 : 比較結果 = 被搜尋字串 Like 樣板字串比較結果, 若被搜尋字串內容符合樣板字串, 則傳回 True 若非, 則傳回 False 但若兩者有一個為 Null ( 空字串, 則比較結果為 Null.? 123 like 123456 False? "123" like "123 True 37
連結運算子 連結運算子的功能在於結合一個或多個文字字串, 以產生一個文字字串 & 及 + 都屬於為字串內容連結的運算子 result = expression 1 & expression 2 38
邏輯運算子 用於運算兩個運算式之間的邏輯關係 See page. 94~97. And 運算子 Eqv 運算子 Imp 運算子 Not 運算子 Or 運算子 Xor 運算子 邏輯連結邏輯相等邏輯關連邏輯否定邏輯分離邏輯互斥 39
運算子優先順序 在運算式中, 若有超過一類的運算子時,VBA 會先執行算術運算子, 其次是比較運算子, 而後才是邏輯運算子 算術 比 較 邏輯 指數運算 ^ 相等 (=) Not 負數 (-) 不等 (<>) And 乘法和除法 (* /) 小於 (<) Or 整數除法 (\) 大於 (>) Xor 餘數運算 (Mod) 小於或相等 (<=) Eqv 加法和減法 (+ -) 大於或相等 (>=) Imp 字串連結 (&) 40
3-5 整數與運算實務案例 業績獎金自訂函數範例 講解藉由定義變數, 建立一個業績獎金的函數 某公司在業績制度上採獎金制, 在計算薪資時, 將先判斷該業務人員之業績是否達到本月業績目標, 若該業務人員達到業績時, 則發放業績獎金 使用 Function 函數程序 變數 及公式完成此項作業 41
Solutions 切換到 VB 編輯器, 選取功能表中 插入 模組, 插入一空白模組 程式如下 : Public Function Comm(income, target) As Integer ' 將 target income 定義為整數的全域變數 Comm = (income - target) * 0.05 ' 寫入業績獎金計算公式 End Function 在 Excel 活頁簿中, 利用 公式 插入函數, 開啟插入函數對話窗, 找到上述自訂函數 comm, 並指定引數,Income 為 C4,target 為 D4 42