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

［關(guān)鍵詞］ Excel；VBA；拓展

［中圖分類號］F232［文獻(xiàn)標(biāo)識碼］A［文章編號］1673-0194（2007）12-0037-03

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

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

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

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

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

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

我們通過以下步驟來實(shí)現(xiàn)：

1. 設(shè)計窗體

在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）

'單變量求解循環(huán)

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

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

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