I have written a DLL function in C++, which I am calling from VBA (Excel).
我用c++编写了一个DLL函数，我从VBA (Excel)调用它。
How can I setup the Visual Studio properties to allow me to debug the function? I have tried specifying Excel, but that doesn’t seem to work.
如何设置Visual Studio属性以允许我调试函数?我尝试过指定Excel，但似乎行不通。
2 个解决方案
#1
3
You have two choices: "direct debug", or "attach".
您有两个选择:“直接调试”或“附加”。
I strongly prefer the "direct debug" approach for a long list of reasons omitted from here.
我强烈倾向于“直接调试”方法，因为这里省略了一长串的原因。
There are steps required on both the DLL and Excel/VBA sides, your posting is unclear if all of those steps are addressed.
在DLL和Excel/VBA方面都需要一些步骤，您的发布不清楚是否所有这些步骤都得到了处理。
There are variations on the following:
有以下变化:
1) In VS, depending on the version, enter Project Settings, or Project Properties, or equivalent, in the "Debug (not release) Target", go to the Debug or Debugging settings. :
1)在VS中，根据版本不同，在“Debug(非release)目标”中输入项目设置或项目属性，或等效的项目属性，进入Debug或Debug设置。:
a) There will be an field called "Executable for debugging session", or "command", or something like that depending on VS ver. Here, enter the full path of your Excel exe
a)会有一个字段叫做“可执行调试会话”，或者“命令”，或者类似的东西，这取决于VS ver。在这里，输入Excel exe的完整路径
b) Optionally, if the same "test spread sheet" is used frequently, enter the full path of your xls (or whatever) in the field called "Command argument", or "program argument" or as in your VS ver.
b)可选地，如果经常使用相同的“测试扩展表”，则在称为“命令参数”或“程序参数”或与VS服务器中一样的字段中输入xls(或其他)的完整路径。
You may need to surround this with double quotes (e.g. if there are spaces in your path/file names).
您可能需要用双引号将其括起来(例如，如果路径/文件名中有空格)。
c) You can also set the output of your project to a Dir that is "addin helpful", such as a Dir called AddIn (c.f. having the DLL end up in Debug (or Release) Dirs)
c)您还可以将项目的输出设置为“addin helpful”的目录，例如名为addin的目录(c.f.将DLL以Debug(或Release) Dirs形式结束)
... it is assumed that your DLL has all the bits required to export the functions, with the project being of type DLL, plus any DLLEXPORT and compiler directives, etc etc.
…假设您的DLL具有导出函数所需的所有位，项目类型为DLL，加上任何DLLEXPORT和编译器指令等等。
... the specifics of the DLLEXPORT settings (and related compiler switches) and Calling Convention will determine many things ... it is assumed you have done all that correctly and consistently (and especially consistently with what the Excel-side is expecting).
…DLLEXPORT设置(以及相关的编译器切换)和调用约定的细节将决定许多事情……假设您已经正确地、一致地完成了所有这些工作(尤其是与优秀员工的期望一致)。
... your DLL may or may not have a DLL_Main, if it does, more discussion is required.
…您的DLL可能有也可能没有DLL_Main，如果有，需要进行更多的讨论。
2) Before anything else, be sure to have created the Excel-side "interface" for your DLL, ie. the "Add-In". This can be either via .xla, or via .xll. I strongly suggest the .xla route as your first approach.
2)在做任何事情之前，一定要为你的DLL(即DLL)创建优秀的“接口”。“插件”。可以通过。xla，也可以通过。xll。我强烈建议您首先走。xla路线。
See the Excel help files etc for creating the .xla
请参阅创建.xla的Excel帮助文件等
Then, in your XLA's VBA Module(s), declare the functions/subs etc from your DLL. For example, if you have a DLL called Add2_DLL.dll, which contains an exported function "add2_pho_xl", then:
然后，在您的XLA的VBA模块中，从DLL中声明函数/subs等。例如，如果您有一个名为Add2_DLL的DLL。dll，其中包含一个导出函数“add2_pho_xl”，则:
Public Declare Function Add2_Pho_XX Lib "E:\EclipseWorkSpace\Add2_DLL\Debug\Add2_DLL.dll" _
Alias "add2_pho_xl" (A As Double, B As Double) As Double
I have used the Alias approach here, for reasons required below.
我在这里使用了别名方法，原因如下。
In many instances, this declaration can be used directly as User Defined Function (UDF) in your sheets, etc. However, for a vast number of cases, you will need to create a VBA "spinner" function/sub that creates the "ultimate" UDF, and relies on this direct entry function (see below). This is also a very long story, but necessary where more complex matters are required (arrays, variants, etc etc).
在许多情况下，这个声明可以直接作为用户定义函数(UDF)在您的表中使用，等等。但是，对于大量的情况，您将需要创建一个VBA“spinner”函数/sub来创建“ultimate”UDF，并依赖于这个直接输入函数(见下面)。这也是一个很长的故事，但是在需要更复杂的事情(数组、变体等等)时是必要的。
NOTICE:
注意:
a) the DLL's full path is required unless special steps have been taken. If your Addin is for general distribution ... a much longer discussion is required.
a)除非采取特殊步骤，否则需要DLL的完整路径。如果你的Addin是用于一般分发…需要更长的讨论。
b) the Alias must be the EXACT entry name of the function in your DLL. If you view near the end of the DLL (or .Def) files, and unless you set your DLL modules as Private, those will show the entry names expected on the DLL side.
b)别名必须是DLL中函数的确切入口名。如果在DLL(或. def)文件的末尾查看，并且除非将DLL模块设置为Private，否则这些模块将显示DLL端预期的条目名称。
In this example, the entry name is NOT "decorated" due to the choices in the Calling Convention and compiler switches, but it could look something like
在本例中，由于调用约定和编译器开关中的选择，条目名没有“修饰”，但它可以看起来像这样
"_add2_pho_xl_@08" etc depending your choices.
“_add2_pho_xl_@08”等等，取决于您的选择。
... in any case, you must use whatever the DLL has.
…无论如何，您必须使用DLL所拥有的任何东西。
3) Once both the .xla and dll exist (it is best if they are in the same Dir), Excel must be "told" about the Add-In. The easiest approach is Excel/Tools/Addins, but there are various strategies for "registering" DLL functions.
3).xla和dll都存在(如果它们在同一个目录中是最好的)，那么Excel就必须“告知”外接程序。最简单的方法是Excel/Tools/Addins，但是“注册”DLL函数有各种策略。
4) CRUCIALLY, the argument list properties/declarations MUST BE CONGRUENT with BOTH those in your DLL and the Calling Convention. Three (of many possible) examples of "issues" are,
关键的是，参数列表属性/声明必须与DLL中的属性/声明以及调用约定一致。“问题”的三个(可能的)例子是，
(i) A Boolean on the VBA-side is two-bytes, if the Bool/Logical on your DLL side is 1-byte, then the Debug will fail, since the two sides "cannot connect" properly.
(i) vba端的布尔值为2字节，如果DLL端的Bool/Logical值为1字节，那么调试将失败，因为双方“无法正确连接”。
(ii) Strings ... this can be a very long story. It depends if sent ByVal or ByRef, it depends if the DLL side has "hidden length" Args, etc. etc. Remember, VBA uses COM/OLE VBStrings, which have their own can of worms.
(2)字符串…这可能是一个很长的故事。它取决于发送的是ByVal还是ByRef，它取决于DLL端是否有“隐藏长度”Args等。
(iii) Variants/Objects: these require a tome onto themselves
(iii)变异体/对象:它们需要一个大部头
5) If ALL (and likely more) of the above have gone well, then in VS set your break points, if required, and "Go" or "Start" the debug (depending on VS ver, etc.). This should launch Excel, and if you also set the target xls, it will launch too. Go to the cell(s) where you addin function (e.g. =add2_pho_XX(A1, B1) ) resides, and execute the cell (sometimes using the "fx" menu item is useful, but there are other ways also).
5)如果以上所有(以及可能更多)都进行得很好，那么在VS设置断点(如果需要)和“Go”或“启动”调试(取决于VS . ver等)。这应该会启动Excel，如果你也设置了目标xls，它也会启动。转到您添加函数所在的单元格(例如=add2_pho_XX(A1, B1))，并执行单元格(有时使用“fx”菜单项是有用的，但是还有其他方法)。
Some hints:
一些提示:
a) if the func execution crashes/hangs etc Excel and does not even arrive back to the VS side, then likely there is a Arg list conflict (e.g. you are passing a Double to an Int or a million other possibilities), or Calling Convention conflict, etc.
a)如果func执行崩溃/挂起等Excel，甚至没有返回到VS端，那么很可能存在Arg列表冲突(例如，您正在传递一个Double到Int或其他上百万种可能性)，或者调用约定冲突等。
b) In general, you may (while in the VS debug session) simultaneously perform a VBA debug session. That is, after starting the VS bebug, entre the VBA IDE, and set break points in VBA UDF's, if a "spinner" UDF's have been created. For example, a VBA UDF that relies also on the DLL's function.
通常，您可以(在VS调试会话中)同时执行VBA调试会话。也就是说，在启动VS bebug之后，进入VBA IDE，并在VBA UDF中设置断点，如果已经创建了“spinner”UDF。例如，VBA UDF也依赖于DLL的函数。
Private Function Add2_Pho( FirstNum as Double, SecondNum as Double, Optional lMakeRed as Variant) As Variant
'
'
Add2_Pho = Add2_Pho_XX( FirstNum, SecondNum ) ' this is the actual DLL func accessed via Delcare above
'
If( IsMissing(lMakeRed) ) Then
Else
If( cBool(lMakeRed) ) Then
If( Add2_Pho < 0 ) Then
'
' ' make the result Red, or something
'
End If
End If
End If
'
'
End Function
... here, setting a break point at the first line can be helpful to see if the UDF is even entered on the VBA side. If it is, click Continue in VBA, and see if it makes it to the VS side, if not, check Args, Calling Convention, etc again, etc etc
…在这里，在第一行设置一个断点可以帮助查看UDF是否在VBA端输入。如果是，在VBA中单击Continue，查看它是否到达VS端，如果不是，检查Args，再次调用约定，等等
c) If the cell's content are #Value or some other unexpected result, then at least the UDF is "recognised" but not functioning correctly, either due to sheet->VBA issues, or VBA-> DLL issues, or after the return DLL-> VBA
c)如果单元格的内容是#Value或其他一些意料之外的结果，那么至少UDF是“认可”的，但由于sheet->VBA问题，或VBA-> DLL问题，或返回DLL-> VBA之后，UDF不能正常工作
d) Save often! and Use the VBA IDE's Debug/ Compile VBA Project before running anything just make sure VBA internal consistency.
d)拯救经常!在运行任何东西之前，使用VBA IDE的调试/编译VBA项目，确保VBA内部一致性。
e) Also, if you are using VBA/XLA's, then get a copy of CleanProject (VBA can mess up its internals sometimes, and this Tool will be a life saver)
e)此外，如果您正在使用VBA/XLA的工具，那么您将获得一个CleanProject的副本(VBA有时可能会打乱其内部结构，这个工具将是一个救生圈)
#2
3
Please make sure that Debug mode is the active mode.
请确保调试模式是活动模式。
How to debug your DLL with Excel/VBA
如何使用Excel/VBA调试DLL
#1
3
You have two choices: "direct debug", or "attach".
您有两个选择:“直接调试”或“附加”。
I strongly prefer the "direct debug" approach for a long list of reasons omitted from here.
我强烈倾向于“直接调试”方法，因为这里省略了一长串的原因。
There are steps required on both the DLL and Excel/VBA sides, your posting is unclear if all of those steps are addressed.
在DLL和Excel/VBA方面都需要一些步骤，您的发布不清楚是否所有这些步骤都得到了处理。
There are variations on the following:
有以下变化:
1) In VS, depending on the version, enter Project Settings, or Project Properties, or equivalent, in the "Debug (not release) Target", go to the Debug or Debugging settings. :
1)在VS中，根据版本不同，在“Debug(非release)目标”中输入项目设置或项目属性，或等效的项目属性，进入Debug或Debug设置。:
a) There will be an field called "Executable for debugging session", or "command", or something like that depending on VS ver. Here, enter the full path of your Excel exe
a)会有一个字段叫做“可执行调试会话”，或者“命令”，或者类似的东西，这取决于VS ver。在这里，输入Excel exe的完整路径
b) Optionally, if the same "test spread sheet" is used frequently, enter the full path of your xls (or whatever) in the field called "Command argument", or "program argument" or as in your VS ver.
b)可选地，如果经常使用相同的“测试扩展表”，则在称为“命令参数”或“程序参数”或与VS服务器中一样的字段中输入xls(或其他)的完整路径。
You may need to surround this with double quotes (e.g. if there are spaces in your path/file names).
您可能需要用双引号将其括起来(例如，如果路径/文件名中有空格)。
c) You can also set the output of your project to a Dir that is "addin helpful", such as a Dir called AddIn (c.f. having the DLL end up in Debug (or Release) Dirs)
c)您还可以将项目的输出设置为“addin helpful”的目录，例如名为addin的目录(c.f.将DLL以Debug(或Release) Dirs形式结束)
... it is assumed that your DLL has all the bits required to export the functions, with the project being of type DLL, plus any DLLEXPORT and compiler directives, etc etc.
…假设您的DLL具有导出函数所需的所有位，项目类型为DLL，加上任何DLLEXPORT和编译器指令等等。
... the specifics of the DLLEXPORT settings (and related compiler switches) and Calling Convention will determine many things ... it is assumed you have done all that correctly and consistently (and especially consistently with what the Excel-side is expecting).
…DLLEXPORT设置(以及相关的编译器切换)和调用约定的细节将决定许多事情……假设您已经正确地、一致地完成了所有这些工作(尤其是与优秀员工的期望一致)。
... your DLL may or may not have a DLL_Main, if it does, more discussion is required.
…您的DLL可能有也可能没有DLL_Main，如果有，需要进行更多的讨论。
2) Before anything else, be sure to have created the Excel-side "interface" for your DLL, ie. the "Add-In". This can be either via .xla, or via .xll. I strongly suggest the .xla route as your first approach.
2)在做任何事情之前，一定要为你的DLL(即DLL)创建优秀的“接口”。“插件”。可以通过。xla，也可以通过。xll。我强烈建议您首先走。xla路线。
See the Excel help files etc for creating the .xla
请参阅创建.xla的Excel帮助文件等
Then, in your XLA's VBA Module(s), declare the functions/subs etc from your DLL. For example, if you have a DLL called Add2_DLL.dll, which contains an exported function "add2_pho_xl", then:
然后，在您的XLA的VBA模块中，从DLL中声明函数/subs等。例如，如果您有一个名为Add2_DLL的DLL。dll，其中包含一个导出函数“add2_pho_xl”，则:
Public Declare Function Add2_Pho_XX Lib "E:\EclipseWorkSpace\Add2_DLL\Debug\Add2_DLL.dll" _
Alias "add2_pho_xl" (A As Double, B As Double) As Double
I have used the Alias approach here, for reasons required below.
我在这里使用了别名方法，原因如下。
In many instances, this declaration can be used directly as User Defined Function (UDF) in your sheets, etc. However, for a vast number of cases, you will need to create a VBA "spinner" function/sub that creates the "ultimate" UDF, and relies on this direct entry function (see below). This is also a very long story, but necessary where more complex matters are required (arrays, variants, etc etc).
在许多情况下，这个声明可以直接作为用户定义函数(UDF)在您的表中使用，等等。但是，对于大量的情况，您将需要创建一个VBA“spinner”函数/sub来创建“ultimate”UDF，并依赖于这个直接输入函数(见下面)。这也是一个很长的故事，但是在需要更复杂的事情(数组、变体等等)时是必要的。
NOTICE:
注意:
a) the DLL's full path is required unless special steps have been taken. If your Addin is for general distribution ... a much longer discussion is required.
a)除非采取特殊步骤，否则需要DLL的完整路径。如果你的Addin是用于一般分发…需要更长的讨论。
b) the Alias must be the EXACT entry name of the function in your DLL. If you view near the end of the DLL (or .Def) files, and unless you set your DLL modules as Private, those will show the entry names expected on the DLL side.
b)别名必须是DLL中函数的确切入口名。如果在DLL(或. def)文件的末尾查看，并且除非将DLL模块设置为Private，否则这些模块将显示DLL端预期的条目名称。
In this example, the entry name is NOT "decorated" due to the choices in the Calling Convention and compiler switches, but it could look something like
在本例中，由于调用约定和编译器开关中的选择，条目名没有“修饰”，但它可以看起来像这样
"_add2_pho_xl_@08" etc depending your choices.
“_add2_pho_xl_@08”等等，取决于您的选择。
... in any case, you must use whatever the DLL has.
…无论如何，您必须使用DLL所拥有的任何东西。
3) Once both the .xla and dll exist (it is best if they are in the same Dir), Excel must be "told" about the Add-In. The easiest approach is Excel/Tools/Addins, but there are various strategies for "registering" DLL functions.
3).xla和dll都存在(如果它们在同一个目录中是最好的)，那么Excel就必须“告知”外接程序。最简单的方法是Excel/Tools/Addins，但是“注册”DLL函数有各种策略。
4) CRUCIALLY, the argument list properties/declarations MUST BE CONGRUENT with BOTH those in your DLL and the Calling Convention. Three (of many possible) examples of "issues" are,
关键的是，参数列表属性/声明必须与DLL中的属性/声明以及调用约定一致。“问题”的三个(可能的)例子是，
(i) A Boolean on the VBA-side is two-bytes, if the Bool/Logical on your DLL side is 1-byte, then the Debug will fail, since the two sides "cannot connect" properly.
(i) vba端的布尔值为2字节，如果DLL端的Bool/Logical值为1字节，那么调试将失败，因为双方“无法正确连接”。
(ii) Strings ... this can be a very long story. It depends if sent ByVal or ByRef, it depends if the DLL side has "hidden length" Args, etc. etc. Remember, VBA uses COM/OLE VBStrings, which have their own can of worms.
(2)字符串…这可能是一个很长的故事。它取决于发送的是ByVal还是ByRef，它取决于DLL端是否有“隐藏长度”Args等。
(iii) Variants/Objects: these require a tome onto themselves
(iii)变异体/对象:它们需要一个大部头
5) If ALL (and likely more) of the above have gone well, then in VS set your break points, if required, and "Go" or "Start" the debug (depending on VS ver, etc.). This should launch Excel, and if you also set the target xls, it will launch too. Go to the cell(s) where you addin function (e.g. =add2_pho_XX(A1, B1) ) resides, and execute the cell (sometimes using the "fx" menu item is useful, but there are other ways also).
5)如果以上所有(以及可能更多)都进行得很好，那么在VS设置断点(如果需要)和“Go”或“启动”调试(取决于VS . ver等)。这应该会启动Excel，如果你也设置了目标xls，它也会启动。转到您添加函数所在的单元格(例如=add2_pho_XX(A1, B1))，并执行单元格(有时使用“fx”菜单项是有用的，但是还有其他方法)。
Some hints:
一些提示:
a) if the func execution crashes/hangs etc Excel and does not even arrive back to the VS side, then likely there is a Arg list conflict (e.g. you are passing a Double to an Int or a million other possibilities), or Calling Convention conflict, etc.
a)如果func执行崩溃/挂起等Excel，甚至没有返回到VS端，那么很可能存在Arg列表冲突(例如，您正在传递一个Double到Int或其他上百万种可能性)，或者调用约定冲突等。
b) In general, you may (while in the VS debug session) simultaneously perform a VBA debug session. That is, after starting the VS bebug, entre the VBA IDE, and set break points in VBA UDF's, if a "spinner" UDF's have been created. For example, a VBA UDF that relies also on the DLL's function.
通常，您可以(在VS调试会话中)同时执行VBA调试会话。也就是说，在启动VS bebug之后，进入VBA IDE，并在VBA UDF中设置断点，如果已经创建了“spinner”UDF。例如，VBA UDF也依赖于DLL的函数。
Private Function Add2_Pho( FirstNum as Double, SecondNum as Double, Optional lMakeRed as Variant) As Variant
'
'
Add2_Pho = Add2_Pho_XX( FirstNum, SecondNum ) ' this is the actual DLL func accessed via Delcare above
'
If( IsMissing(lMakeRed) ) Then
Else
If( cBool(lMakeRed) ) Then
If( Add2_Pho < 0 ) Then
'
' ' make the result Red, or something
'
End If
End If
End If
'
'
End Function
... here, setting a break point at the first line can be helpful to see if the UDF is even entered on the VBA side. If it is, click Continue in VBA, and see if it makes it to the VS side, if not, check Args, Calling Convention, etc again, etc etc
…在这里，在第一行设置一个断点可以帮助查看UDF是否在VBA端输入。如果是，在VBA中单击Continue，查看它是否到达VS端，如果不是，检查Args，再次调用约定，等等
c) If the cell's content are #Value or some other unexpected result, then at least the UDF is "recognised" but not functioning correctly, either due to sheet->VBA issues, or VBA-> DLL issues, or after the return DLL-> VBA
c)如果单元格的内容是#Value或其他一些意料之外的结果，那么至少UDF是“认可”的，但由于sheet->VBA问题，或VBA-> DLL问题，或返回DLL-> VBA之后，UDF不能正常工作
d) Save often! and Use the VBA IDE's Debug/ Compile VBA Project before running anything just make sure VBA internal consistency.
d)拯救经常!在运行任何东西之前，使用VBA IDE的调试/编译VBA项目，确保VBA内部一致性。
e) Also, if you are using VBA/XLA's, then get a copy of CleanProject (VBA can mess up its internals sometimes, and this Tool will be a life saver)
e)此外，如果您正在使用VBA/XLA的工具，那么您将获得一个CleanProject的副本(VBA有时可能会打乱其内部结构，这个工具将是一个救生圈)
#2
3
Please make sure that Debug mode is the active mode.
请确保调试模式是活动模式。
How to debug your DLL with Excel/VBA
如何使用Excel/VBA调试DLL