vba函數實例講解（003.VBA過程和函數-乾貨）

2023-05-24 00:05:00

大家好，我是永不止步的老牛。

上一篇我們介紹了VBA的編輯器，本篇我們介紹VBA過程和函數以及MsgBox函數、InputBox函數、InputBox方法(用代碼給單元格設置公式)。

前面文章提到過，過程和函數就是執行某些動作的代碼組合，在程序運行時完成具體的任務。

過程以Sub開頭，以End Sub結束，過程執行一些代碼但不返回值。

函數以Function開頭，以End Function結束，函數執行一些代碼並返回值，函數可以從過程中執行，也可以在Excel工作表中使用，就像Excel內置函數一樣在公式中直接使用，Excel中內置了很多函數，但是總有內置函數無法實現的需求，我們可以自己編寫代碼去實現，建立一些Excel沒有的函數。

我們以一個例子開始我們今天的學習，身體質量指數BMI是常用的衡量人體肥胖程度和是否健康的重要標準，BMI正常範圍為18.5-23.9，低於18.5為體重過輕，高於23.9為體重過重，BMI=體重（公斤）除以身高的平方（米的平方）。

1.編寫一個過程

在名稱處輸入「CalculateBMI」，點擊確定，關閉添加過程窗體，VBA代碼窗口增加了一個過程：

Public Sub CalculateBMI

End Sub

第一句聲明過程名稱，默認是關鍵字Public，表示這個過程可以在所有模塊的所有過程裡訪問。關鍵字Public是可選的。關鍵字Sub後面是過程名稱CalculateBMI和一對空括號。在括號裡你可以添加需要傳遞的參數。過程都要以End Sub語句結束。

如果把Public替換成Private，那麼過程只能被「BMI計算」模塊裡的其它過程調用，而不能被其它模塊裡的過程調用。

我們在過程體內，就是Public Sub CalculateBMI和End Sub之間，輸入計算BMI的代碼，首先定義3個變量BMI、Height和Weight，並給Height和Weight賦值，然後計算BMI數值並將BMI值輸出至立即窗口，最後用MsgBox函數提示計算完成及BMI值。

Public Sub CalculateBMI

Dim BMI As Single 'BMI值

Dim Height As Single '身高值

Dim Weight As Single '體重值

Height = 1.81

Weight = 66

BMI = Weight / (Height) ^ 2

Debug.Print BMI

MsgBox "身體質量指數BMI計算完成，BMI為" & BMI, vbOKOnly vbInformation, "提示"

End Sub

將光標定位在過程代碼的任何地方，按F5運行過程，立即窗口會輸出BMI值，並彈出對話框顯示BMI值。

在這個過程中用Dim聲明變量並明確數據類型是Single，用=將右邊的值賦給左邊的變量，

「/」是除運算符，「^」表示計算平方，Debug.Print BMI是將變量BMI的值輸出到立即窗口，MsgBox是對話框函數，提供給用戶一些信息，並支持人機互動。

2.使用MsgBox函數

MsgBox函數語法為：MsgBox(prompt[, buttons] [, title] [, helpfile, context])

[]內的參數可以省略，具體參數的含義、參數數值、返回值後面用表格列舉，MsgBox函數可以有返回值，有2種調用方式：

A. MsgBox "消息", vbOKOnly, "提示"

B. iReturn =MsgBox("確認刪除該條數據嗎？", vbYesNo vbQuestion, "提示")

A相當於只是在界面上顯示了一個對話框，提示用戶一些信息，用戶點擊按鈕後不做任何處理。

B相當於在界面上顯示了一個對話框，將用戶點擊不同的按鈕的值返回給變量iReturn，然後程序可以根據iReturn的值決定後續代碼如何執行。

MsgBox 函數參數說明如下圖：

buttons參數值設置如下圖：

buttons參數的

用每組數字的一個值加起來就是最終buttons的值。

返回值如下圖：

MsgBox函數我們先介紹到這裡，我們繼續我們的編程，前面編寫的代碼中我們直接給定了身高和體重數值，我們現在需要讓用戶輸入身高和體重，我們將代碼修改成這樣：

Public Sub CalculateBMI

Dim BMI As Single 'BMI值

Dim Height As Single '身高值

Dim Weight As Single '體重值

Height = InputBox("請輸入自己的身高")

Weight = InputBox("請輸入自己的體重")

BMI = Weight / (Height) ^ 2

Debug.Print BMI

MsgBox "身體質量指數BMI計算完成，BMI為" & BMI, vbOKOnly vbInformation vbMsgBoxRight, "提示"

End Sub

將光標定位在過程代碼的任何地方，按F5運行過程，會先彈出對話框要求輸入身高，輸入並確定後，再彈出對話框要求輸入體重，輸入並確定後，立即窗口會輸出BMI值，並彈出對話框顯示BMI值。這裡我們使用了InputBox函數讓用戶輸入信息。

3.使用InputBox函數

InputBox函數語法：InputBox(prompt, [ title ], [ default ], [ xpos ], [ ypos ], [ helpfile, context ])

[]內的參數可以省略。

Prompt：顯示在對話框上的信息。

Title：對話框的標題，默認的標題是Mictosoft Excel。

Default：文本框裡顯示一個默認值，如果忽略，顯示空白文本框。

xpos和ypos：對話框在屏幕上出現的位置，如果忽略，顯示在當前窗口的中央，xpos決定對話框在屏幕上從左起的水平位置，忽略它時，對話框顯示在水平中央，ypos決定對話框在屏幕從上而下的豎直位置，忽略它時，對話框就在豎直大約三分之一的位置。

helpfile和context：和本章前面介紹的MsgBox函數相應的參數使用方法一樣。

具體參數說明見下表：

明白InputBox函數的用法後，我們將上述代碼再修改一下，不使用InputBox函數的默認標題「Mictosoft Excel」。

Public Sub CalculateBMI

Dim BMI As Single 'BMI值

Dim Height As Single '身高值

Dim Weight As Single '體重值

Height = InputBox("請輸入一個數值", "輸入自己的身高")

Weight = InputBox("請輸入一個數值", "輸入自己的體重")

BMI = Weight / (Height) ^ 2

Debug.Print BMI

MsgBox "身體質量指數BMI計算完成，BMI為" & BMI, vbOKOnly vbInformation vbMsgBoxRight, "提示"

End Sub

4.編譯一個函數，並在過程及公式中使用

過程我們先介紹到這裡，我們看一下函數，函數也是一種過程，只是函數能返回值，函數只能由過程調用或在Excel工作表的的公式中使用，而不能像過程一樣用F5或菜單「運行」執行。

函數的建立可以像前面介紹的建立過程一樣，通過菜單「插入」-「模塊」，選擇「函數」來建立。

也可以自己手工編寫代碼來建立，我們在模塊「BMI計算」的代碼窗口，直接輸入如下代碼：

Public Function GetBMI(w, h As Single) As Single

GetBMI = w / (h) ^ 2

End Function

關鍵字Function後面是函數名稱GetBMI和一對空括號。括號裡的w和h是傳遞給函數的參數，函數以Function開頭，以End Function語句結束。

Public表示這個函數可以在所有模塊的所有過程裡訪問，在Excel公式中也可以使用，如果將Public換成Private，那麼函數只能被同一模塊裡的其它過程調用，而不能被其它模塊裡的過程調用，也不能被Excel公式中使用。

最後面的As Single表示函數返回值的數據類型，如果省略，默認成Variant。

函數的返回值就是把要返回的內容賦值給函數名稱。

如果給參數前加關鍵字Optional，那麼表示這個是可選參數，就是說調用函數時，可以傳遞這個參數值，也可以不傳遞，注意的是，如果某個參數設置成可選參數，那麼這個參數之後的參數必須都是可選參數。

函數我們先簡單介紹這麼多，還有按地址和按值傳遞參數等內容，後續我們用到時再細說。

寫好函數後，我們可以修改前面的過程CalculateBMI，在過程中調用函數GetBMI，代碼如下：

Public Sub CalculateBMI

Dim BMI As Single 'BMI值

Dim Height As Single '身高值

Dim Weight As Single '體重值

Height = InputBox("請輸入一個數值", "輸入自己的身高")

Weight = InputBox("請輸入一個數值", "輸入自己的體重")

BMI = GetBMI(Weight, Height)

Debug.Print BMI

MsgBox "身體質量指數BMI計算完成，BMI為" & BMI, vbOKOnly vbInformation vbMsgBoxRight, "提示"

End Sub

F5運行，結果和剛才的一樣。

我們看一下Excel公式中如何引用函數GetBMI。

大家現在已經了解了過程和函數的寫法，以及MsgBox函數InputBox函數的用法。

5.使用InputBox方法

前面說的InputBox函數屬於VBA庫，在Excel庫中有一個InputBox方法，大家可以在對象瀏覽器中搜索InputBox，搜索結果可以看到2個InputBox，分別屬於VBA和Excel。

InputBox方法的語法：expression.InputBox (Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)

expression：表示 Application 對象的變量。

參數說明如下圖：

除了最後一個參數Type，其餘參數基本和InputBox函數對應，我們重點說一下Type參數，Type參數的值為下表中的數據：

Type設置可以允許用戶輸入的數據類型，可以是一個值，也可以將多個值相加。 假如允許輸入文本和數字，Type就設置為 1 2。

如果 Type 為 0，InputBox 返回文本格式的公式，如果 Type 為 4，InputBox 返回True或False，如果 Type 為 8，InputBox 返回 Range 對象，如果是8， 必須使用 Set 語句，將結果分配給 Range 對象，

如果不使用 Set 語句，此變量就會被設置為區域中的值，而不是 Range 對象本身。

如果使用 InputBox 方法提示用戶輸入公式，必須使用 FormulaLocal 屬性，將公式分配給 Range 對象。

InputBox 方法與 InputBox 函數的區別在於，前者可以對用戶輸入進行選擇性驗證，並能與 Excel 對象、錯誤值和公式結合使用。 Application.InputBox 調用的是 InputBox 方法；不帶對象限定符的 InputBox 調用的是 InputBox 函數。

如果用戶輸入的內容和Type設置的不相符，會出提示，確認後，繼續等待用戶輸入。

用下面的示例展示一下Type 為 8時，使用和不使用Set的區別，代碼如下，區別效果見動圖：

Public Sub TestInputBox

Dim Value

Dim Value2

Set Value =, Type:=8)

Set Value2 =, Type:=8)

MsgBox Value

End Sub

我們在演示一下Type為0時，如何給一個單元格設置我們動態輸入的公式，代碼如下，效果見動圖：

Public Sub TestInputBox2

Dim Value

Value =, Type:=0)

Sheet5.Range("F7").FormulaLocal = Value

End Sub

VBA過程和函數以及MsgBox函數、InputBox函數、InputBox方法介紹到這裡，今天的內容有點多，大家最好能自己實際練習一下，下一篇文章我們介紹Excel宏。