必看电影推荐:Excel VBA封装为Dll的例子、方法与总结【逐步完善中...】
来源:百度文库 编辑:偶看新闻 时间:2024/10/02 18:12:28
看看下面常用的VBA界面处理代码,封装为Dll时应该如何改代码?
'需要封装的VBA代码
Sub 恢复系统界面()
On Error Resume Next
With Application
.Caption = "版权所有:GoodFortune From www.ExcelHome.net"
.CommandBars("Worksheet Menu Bar").Enabled = True
.CommandBars("Toolbar List").Enabled = True
.CommandBars("Standard").Visible = True
.CommandBars("Formatting").Visible = True
.DisplayFormulaBar = True
End With
With ActiveWindow
.DisplayGridlines = True
.DisplayHeadings = True
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
End With
End Sub
Sub 隐藏系统界面()
On Error Resume Next
With Application
.CommandBars("Worksheet Menu Bar").Enabled = False
.CommandBars("Toolbar List").Enabled = False
.CommandBars("Standard").Visible = False
.CommandBars("Formatting").Visible = False
.DisplayFormulaBar = False
End With
With ActiveWindow
.DisplayGridlines = True
.DisplayHeadings = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
End With
End Sub
首先,分析一下,上面的VBA代码中有几个需要传递的变量(或者先分析“对象”),一个是Application,一个是ActiveWindow,其中ActiveWindow是Application的下一级对象,因此,按第2条原则,从最上一层开始,完整形式为Application.ActiveWindow,因此上面的代码可以改成如下的形式,用一个变量传递就可以了。
'封装为Dll的代码
Sub 恢复系统界面(oExcel as Excel.Application)
On Error Resume Next
With oExcel
.Caption = "版权所有:GoodFortune From www.ExcelHome.net"
.CommandBars("Worksheet Menu Bar").Enabled = True
.CommandBars("Toolbar List").Enabled = True
.CommandBars("Standard").Visible = True
.CommandBars("Formatting").Visible = True
.DisplayFormulaBar = True
End With
With oExcel.ActiveWindow
.DisplayGridlines = True
.DisplayHeadings = True
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
End With
End Sub
Sub 隐藏系统界面(oExcel as Excel.Application)
On Error Resume Next
With oExcel
.CommandBars("Worksheet Menu Bar").Enabled = False
.CommandBars("Toolbar List").Enabled = False
.CommandBars("Standard").Visible = False
.CommandBars("Formatting").Visible = False
.DisplayFormulaBar = False
End With
With oExcel.ActiveWindow
.DisplayGridlines = True
.DisplayHeadings = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
End With
End Sub
由上可见,封装中需要修改的是将对象变量换成从根一级开始的完整形式,其他部分则不需要修改。
上面的例子是我学习中的经验总结,拿出来分享,虽然写的不好,但却是自己一步一步琢磨的,希望能抛砖引玉,请朋友们多指点。