将 vba 转换为 vb6 并创建一个 .dll - 如何 - 提示、技巧和风险
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/2041363/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert vba to vb6 and create a .dll - how to - hints, tips and risks
提问by Thomas Kremmel
I should convert a huge load of code which was written in VBA (Excel) to VB6. But I really do not know what I have to take care of or where to begin. Therefore it would be great to get some hints here from the VB6 experts.
我应该将大量用 VBA (Excel) 编写的代码转换为 VB6。但我真的不知道我必须照顾什么或从哪里开始。因此,从 VB6 专家那里得到一些提示会很棒。
I installed already MS Visual Studio and played a bit around. But I'm not a VB6 expert and do not really know what I have to do.
我已经安装了 MS Visual Studio 并玩了一些。但我不是 VB6 专家,也不知道我必须做什么。
The final goal is to have all the VBA code, which is currently placed in one excel vba macro into a VB6 project and create a .dll out of it. This .dll should be referenced by the excel and the excel should run like it does now :-)
最终目标是将当前放置在一个 excel vba 宏中的所有 VBA 代码放入 VB6 项目中,并从中创建一个 .dll。这个 .dll 应该被 excel 引用,并且 excel 应该像现在一样运行 :-)
For example what do I have to do to convert this vba code to VB6.
例如,我必须做什么才能将此 vba 代码转换为 VB6。
Public Function getParameterNumberOfMaterial() As Integer
10 On Error Resume Next
Dim a As String
20 a = Sheets("Parameters").name
30 If IsNumeric(Application.Worksheets(a).range("C3").Value) Then
40 If Application.Worksheets(a).range("C3").Value > 0 Then
50 getParameterNumberOfMaterial = Application.Worksheets(a).range("C3").Value
60 Else
70 MsgBox "Please check cell C3 in the sheet 'Parameters'. It should include a numeric value which is greater than zero"
80 MsgBox "Parameter Number of Material/Cost is set to the default value of 10"
90 getParameterNumberOfMaterial = 10
100 End If
110 Else
120 MsgBox "Please check cell C3 in the sheet 'Parameters'. It should include a numeric value which is greater than zero"
130 MsgBox "Parameter Number of Material/Cost is set to the default value of 10"
140 getParameterNumberOfMaterial = 10
150 End If
160 On Error GoTo 0
End Function
Edit: Yes and if it is possible to convert vba code into a .dll this would be fine also. Then I would not have to convert the code. But I think it is only possible to create a .dll out of vb6 code.
编辑:是的,如果可以将 vba 代码转换为 .dll,这也很好。那么我就不必转换代码了。但我认为只能从 vb6 代码中创建一个 .dll。
回答by Vivek Bernard
@Tom
@汤姆
Ok, I'm actually learning this with you, so here goes,
好的,我实际上正在和你一起学习这个,所以这里是,
VB.Net code (I am using .net 2.0)
VB.Net 代码(我使用的是 .net 2.0)
In Visual Studio 2005 open a new Class Library Project Then remove all the garbage already written there and paste the code
在 Visual Studio 2005 中打开一个新的类库项目然后删除所有已经写在那里的垃圾并粘贴代码
'First thing to do is add a reference the Excel Runtime Imports Microsoft.Office.Interop.Excel Imports System.Runtime.InteropServices Namespace ExcelExample ' the following is an Attribute spcifying that the class can be accesses in a unmanaged (non-.net) way Imports Microsoft.Office.Interop.Excel Imports System.Runtime.InteropServices Public Class ExcelVB Public Function getParameterNumberOfMaterial() As Integer On Error Resume Next Dim a As String Dim appInst As New Microsoft.Office.Interop.Excel.Application a = appInst.Sheets("Parameters").name If IsNumeric(appInst.Worksheets(a).range("C3").Value) Then If appInst.Worksheets(a).range("C3").Value > 0 Then getParameterNumberOfMaterial = appInst.Worksheets(a).range("C3").Value Else MsgBox("Please check cell C3 in the sheet 'Parameters'. It should include a numeric value which is greater than zero") MsgBox("Parameter Number of Material/Cost is set to the default value of 10") getParameterNumberOfMaterial = 10 End If Else MsgBox("Please check cell C3 in the sheet 'Parameters'. It should include a numeric value which is greater than zero") MsgBox("Parameter Number of Material/Cost is set to the default value of 10") getParameterNumberOfMaterial = 10 End If On Error GoTo 0 End Function End Class End Namespace
Build the solution by pressing F6 go to Project->Project Proerties and Check Register for COm interop
按 F6 构建解决方案,转到 Project->Project Proerties 并检查 COM 互操作的注册
So the output is a .DLL and a .tlb , the Excel file should reference the .tlb file,
所以输出是一个 .DLL 和一个 .tlb ,Excel 文件应该引用 .tlb 文件,
you have to register the DLL by regasm /codebase c:\Excel\dllname.dll
您必须通过 regasm /codebase c:\Excel\dllname.dll 注册 DLL
Then you can access the Function from Excel.
然后您可以从 Excel 访问该函数。
Heres a link to my project folder unrar it, and you'll find a an excel workbook that contains a reference to the .dll via the .tlb
这是我的项目文件夹的链接,解压缩它,您会发现一个 Excel 工作簿,其中包含通过 .tlb 对 .dll 的引用
http://cid-4af152a1af4d7db8.skydrive.live.com/self.aspx/Documents/Debug.rar
http://cid-4af152a1af4d7db8.skydrive.live.com/self.aspx/Documents/Debug.rar
Heres another great article
这是另一篇很棒的文章
回答by ErikE
Converting to VB6 is easy.
转换为 VB6 很容易。
Create a VB6 DLL project. Search on the web for instructions how to do this and how to expose methods, classes, and functions.
Add a reference to "Microsoft Office Excel ## library".
In a procedure of the project that will be exposed as a method in the DLL, add the following code:
Dim E As Excel.Application Set E = GetObject(, "Excel.Application") 'or if Excel is not running use CreateObject("Excel.Application") 'You can use error handling to figure out which one you need.
Proceed with your normal VBA code, with one modification: Globally accessed objects such as
ActiveSheet
orActiveWorkbook
orSheets
must becomeE.ActiveSheet
,E.ActiveWorkbook
andE.Sheets
.
创建一个 VB6 DLL 项目。在 Web 上搜索有关如何执行此操作以及如何公开方法、类和函数的说明。
添加对“Microsoft Office Excel ## 库”的引用。
在将作为 DLL 中方法公开的项目过程中,添加以下代码:
Dim E As Excel.Application Set E = GetObject(, "Excel.Application") 'or if Excel is not running use CreateObject("Excel.Application") 'You can use error handling to figure out which one you need.
继续使用您的普通 VBA 代码,并进行一项修改:全局访问的对象,例如
ActiveSheet
orActiveWorkbook
或Sheets
must beE.ActiveSheet
,E.ActiveWorkbook
andE.Sheets
。
If you have forms in your VBA project it will be a bit more work to convert them because forms are completely different in VB6 and VBA (they don't work the same by a long shot).
如果您的 VBA 项目中有表单,则转换它们将需要更多的工作,因为表单在 VB6 和 VBA 中完全不同(从长远来看,它们的工作方式不同)。
回答by Vivek Bernard
@Tom Tom
@汤姆汤姆
You should have no trouble in converting the code from .VBA to vb6.as a matter of fact you virtually don't have to.
将代码从 .VBA 转换为 vb6 应该没有问题。事实上,您实际上不必这样做。
The problem is in VB6 context, the langugae cannot understand what
问题是在VB6上下文中,语言无法理解什么
"Application.Worksheets(a).range("C3").Value)" means,
“Application.Worksheets(a).range("C3").Value)”意味着,
the object Application has a different meaning in VB6
对象 Application 在 VB6 中有不同的含义
VBA (the VBA version you have) is almost a customized implementation of VB6 in Excel(or word , or anything that comes with MSO).
VBA(您拥有的 VBA 版本)几乎是 Excel(或 word 或 MSO 附带的任何内容)中 VB6 的自定义实现。
It would be a bad Idea to try to access Excel UI from VB6 (I'm not even sure if its possible )
尝试从 VB6 访问 Excel UI 是个坏主意(我什至不确定是否可能)
what you should do is seperate the bussiness logic from the Code then make it into a VB6 library.
您应该做的是将业务逻辑与代码分开,然后将其放入 VB6 库中。
For example your code (as I understand) returns the value of the cell C3
例如,您的代码(据我所知)返回单元格 C3 的值
It is very much tied to the Excel UI, so it would be very counter productive if not impossible to convert you code to VB6.
它与 Excel UI 密切相关,因此即使不是不可能将代码转换为 VB6,也会适得其反。
because even if you convert the code, most of the variables like (Application.Worksheets(a).range("C3").Value) must be called from VBA, which is pointless
因为即使你转换了代码,像 (Application.Worksheets(a).range("C3").Value) 这样的大多数变量都必须从 VBA 中调用,这是毫无意义的
however if you have any other pure Bussiness logic, that can be ported (its funny because theres really nothing to port) easily
但是,如果您有任何其他纯业务逻辑,则可以轻松移植(这很有趣,因为实际上没有什么可移植的)