论文部分内容阅读
［摘 要］ 本文主要以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。
单击确定，得出贷款期限为4.892 98年。
现在的问题是，不是一个员工提出这个问题，而是很多员工提出类似问题。比如其中一个员工提出自己每月可还款1 500元，贷款期限（年）为多少？当然我们可以分别使用Excel单变量求解功能解决，但比较费时。我们希望从两个方面改进Excel单变量求解：①单变量求解对话框中的目标值可以像目标单元格那样通过压缩对话框图标输入；②单变量求解可以批量处理数据。
我们通过以下步骤来实现：
1. 设计窗体
在Excel菜单中选择工具，然后选择宏，然后选择Visual Basic编辑器。在VB编辑器中选择插入菜单，然后选择用户窗体在工具箱中选择控件，做成窗体（见图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，可见，问题得到圆满解决。
［关键词］ 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。
单击确定，得出贷款期限为4.892 98年。
现在的问题是，不是一个员工提出这个问题，而是很多员工提出类似问题。比如其中一个员工提出自己每月可还款1 500元，贷款期限（年）为多少？当然我们可以分别使用Excel单变量求解功能解决，但比较费时。我们希望从两个方面改进Excel单变量求解：①单变量求解对话框中的目标值可以像目标单元格那样通过压缩对话框图标输入；②单变量求解可以批量处理数据。
我们通过以下步骤来实现：
1. 设计窗体
在Excel菜单中选择工具，然后选择宏，然后选择Visual Basic编辑器。在VB编辑器中选择插入菜单，然后选择用户窗体在工具箱中选择控件，做成窗体（见图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，可见，问题得到圆满解决。