用ＶＢＡ拓展Ｅｘｃｅｌ

［摘 要］ 本文主要以Excel單變量求解工具為例，探討了Excel VBA 在拓展Excel現有功能方面的應用。

［關鍵詞］ Excel；VBA；拓展

［中圖分類號］F232［文獻標識碼］A［文章編號］1673-0194（2007）12-0037-03

Excel是管理信息化過程中非常重要的一款軟件，很多大型國際企業都是使用Excel進行數據管理。Excel它不僅能夠方便地處理表格和進行圖形分析，其更強大的功能體現在對數據的自動處理和計算，毫無疑問，Excel給我們帶來很大的便利。然而，隨著應用的深入，我們也會感到Excel現有功能的局限和不足，需要用Excel VBA 開發工具來增強Excel的現有功能。下面以Excel 單變量求解功能來說明此問題。

有一個單位欲通過集資的方式建房。房子總價

150 000元。假設單位職工現金支付房款的30%，其余部分通過銀行貸款方式支付，貸款利率3.825‰ ，貸款期限由職工自己選擇，不妨先假設為10年，問月付款多少？這個問題可通過Excel PMT函數求出，見表1。

單位職工收入是有差異的，有的員工收入高，有的收入低，所以，員工的還款期限是不一樣的。假設一位員工每月有2 000元現金可用于還款，那么他會問自己的還款期限為多少。對這類問題，可以使用Excel提供的單變量求解工具來解決，使B6成為活動單元格，單擊Excel菜單，然后單擊單變量求解選項，出現單變量求解對話框，見圖1。

單擊確定，得出貸款期限為4.892 98年。

現在的問題是，不是一個員工提出這個問題，而是很多員工提出類似問題。比如其中一個員工提出自己每月可還款1 500元，貸款期限（年）為多少？當然我們可以分別使用Excel單變量求解功能解決，但比較費時。我們希望從兩個方面改進Excel單變量求解：①單變量求解對話框中的目標值可以像目標單元格那樣通過壓縮對話框圖標輸入；②單變量求解可以批量處理數據。

我們通過以下步驟來實現：

1. 設計窗體

在Excel菜單中選擇工具，然后選擇宏，然后選擇Visual Basic編輯器。在VB編輯器中選擇插入菜單，然后選擇用戶窗體在工具箱中選擇控件，做成窗體（見圖2）。

2. 修改控件的屬性

3. 為控件添加代碼

為確定按鈕添加的代碼：

Private Sub OkButton_Click（）

'首先定義3個Range變量

Dim TargetVal As Range

Dim DesiredVal As Range

Dim ChangeValAs Range

'獲得控件TargetRef的Text屬性

Set TargetVal = Range（TargetRef.Text）

Set DesiredVal = Range（DesiredRef.Text）

Set ChangeVal = Range（ChangingRef.Text）

'單變量求解循環

For i = 1 To WorksheetFunction.Max（TargetVal.Columns.Count， TargetVal.Rows.Count）

TargetVal.Cells（i）.GoalSeek Goal:=DesiredVal.Cells（i）.Value， ChangingCell:=ChangeVal.Cells（i）

Next i

MutipleGoalSeek.Hide

End Sub

為取消按鈕添加的代碼：

'卸載窗體

Private Sub CommandButton1_Click（）

Unload Me

End Sub

在VBAProject中的ThisWorkbook中添加代碼，目的是在Excel界面中添加一個懸浮按鈕：

Private Sub Workbook_Open（）

On Error Resume Next

Application.CommandBars（“Goalseek”）.Delete

Set jnxsCommandBar = Application.CommandBars.Add（“Goalseek”）

With jnxsCommandBar.Controls

Set jnxsCommandBarButton = .Add（msoControlButton）

With jnxsCommandBarButton

.Style = msoButtonIconAndCaption

.Caption = “單變量求解”

'單變量求解按鈕引用名為chen的宏

.OnAction = “chen”

End With

End With

jnxsCommandBar.Visible = True

End Sub

最后在Excel VB中插入一個模塊，寫入如下代碼：

Sub chen（）

MutipleGoalSeek.Show

End Sub

我們先設計表格（見表3），然后運行VBA，看一下效果。在Excel界面中出現了單變量求解懸浮框（見圖3）。

單擊單變量求解，出現對話框，作如下引用（見圖4）后，單擊確定，Excel表格結果見表4，可見，問題得到圓滿解決。