将 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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 11:11:27  来源:igfitidea点击:

Convert vba to vb6 and create a .dll - how to - hints, tips and risks

excel-vbavb6-migrationvbaexcel

提问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

这是另一篇很棒的文章

http://richnewman.wordpress.com/2007/04/15/a-beginner%E2%80%99s-guide-to-calling-a-net-library-from-excel/

http://richnewman.wordpress.com/2007/04/15/a-beginner%E2%80%99s-guide-to-calling-a-net-library-from-excel/

回答by ErikE

Converting to VB6 is easy.

转换为 VB6 很容易。

  1. Create a VB6 DLL project. Search on the web for instructions how to do this and how to expose methods, classes, and functions.

  2. Add a reference to "Microsoft Office Excel ## library".

  3. 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.
    
  4. Proceed with your normal VBA code, with one modification: Globally accessed objects such as ActiveSheetor ActiveWorkbookor Sheetsmust become E.ActiveSheet, E.ActiveWorkbookand E.Sheets.

  1. 创建一个 VB6 DLL 项目。在 Web 上搜索有关如何执行此操作以及如何公开方法、类和函数的说明。

  2. 添加对“Microsoft Office Excel ## 库”的引用。

  3. 在将作为 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.
    
  4. 继续使用您的普通 VBA 代码,并进行一项修改:全局访问的对象,例如ActiveSheetorActiveWorkbookSheetsmust be E.ActiveSheet, E.ActiveWorkbookand E.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

但是,如果您有任何其他纯业务逻辑,则可以轻松移植(这很有趣,因为实际上没有什么可移植的)