How to Call (utilize) a Sub() in a Class DLL?

[EagleOne]

XL 2003, 2007


Using VB Express 2008, I have created, "Signed," GAC and registered a ComVisible DLL called
ExcelTrans.Dll. The Dll "Built" fine meaning there were no warnings or errors.

In VBE, I used Tools>References and added ExcelTrans.Tools

Then I attempted to use one of the embedded (in ExcelTrans.Tools) the Subroutines:
myFormat() and
myUnformat() with the code as follows:

Sub TestDLL()

Dim MF As ExcelTrans.Tools ' this line works
Set MF = New ExcelTrans.Tools ' the "SET" works fine
Call MF.myFormat ' Intellisense "sees" myFormat
and myUnformat meaning that
I can select either in a dropdown
End Sub


Thanks to Chip Pearson, I made alterations to his code on www.cpearson.com

Bottom line, I am not sure how to use the the Subroutines in the DLL with Excel VBA. The following
code is provided as background only.

TIA EagleOne


The code used in VB 2008 Express creates one "Class :"Tools" and two Public Subs() called:
myFormat() and
myUnformat()
'*******************************************************************************************************
'****************************** VB 2008 Express Code *******************************************
'*******************************************************************************************************
Option Explicit On
Option Compare Text
Option Strict Off
Imports XL = Microsoft.Office.Interop.Excel
Imports OFC = Microsoft.Office.Interop
Imports System
Imports System.Runtime.InteropServices
Imports Microsoft.Win32
Imports Microsoft.VisualBasic
Imports Microsoft.Vbe.Interop.vbext_ProcKind


<ClassInterface(ClassInterfaceType.AutoDual), ComVisible(True)> Public Class Tools

Private Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal strClass As String, _
ByVal lpWindow As String) As Long

Public Sub myFormat()
'
Dim myCode As String
Dim modName As String
Dim AppPath As String
Dim XLPath As String
Dim i As Long
Dim j As Long
Dim str As String
Dim blnStringMode As Boolean
Dim blnLineContinue As Boolean
Dim lCount As Long
Dim xlsApp As Microsoft.Office.Interop.Excel.Application
Dim xlsWB As Microsoft.Office.Interop.Excel.Workbook
Dim xlsSheet As Microsoft.Office.Interop.Excel.Worksheet
'Dim xlsCell As Microsoft.Office.Interop.Excel.Range

'***************** Code to access current instance of Excel **************************
Dim nWnd As IntPtr
Dim ceroIntPtr As New IntPtr(0)
Dim Wnd_name As String

Wnd_name = "Excel"
nWnd = FindWindow(Nothing, Wnd_name)
'show the info
If nWnd.Equals(ceroIntPtr) Then
MsgBox("Creating New session of Excel")
xlsApp = New Microsoft.Office.Interop.Excel.Application
Else
MsgBox("Connecting to Current session of Excel")
xlsApp = GetObject(, "Excel.Application")
End If

xlsApp.Visible = True
xlsWB = xlsApp.ActiveSheet
xlsSheet = xlsWB.Worksheets(1)
'***************** End Code to access current instance of Excel ************************
modName = "Tools"

' ........... VBA code to Remove indentations
' ........... VBA code to Remove Removes comments
' ........... VBA code to Removes blank lines

End Sub

Public Sub myUnformat()
'
Dim myCode As String
Dim modName As String
Dim AppPath As String
Dim XLPath As String
Dim xlsApp As Microsoft.Office.Interop.Excel.Application
Dim xlsWB As Microsoft.Office.Interop.Excel.Workbook
Dim xlsSheet As Microsoft.Office.Interop.Excel.Worksheet

Dim nWnd As IntPtr
Dim ceroIntPtr As New IntPtr(0)
Dim Wnd_name As String

Wnd_name = "Excel"
nWnd = FindWindow(Nothing, Wnd_name)
'show the info
If nWnd.Equals(ceroIntPtr) Then
MsgBox("Creating New session of Excel")
xlsApp = New Microsoft.Office.Interop.Excel.Application
Else
MsgBox("Connecting to Current session of Excel")
xlsApp = GetObject(, "Excel.Application")
End If

xlsApp.Visible = True
xlsWB = xlsApp.ActiveSheet
xlsSheet = xlsWB.Worksheets(1)

modName = "myUnformat"
' ........... VBA code to Undo Remove indentations above
' ........... VBA code to Undo Remove Removes comments above
' ........... VBA code to Undo Removes blank lines above

End Sub

<ComRegisterFunctionAttribute()> Public Shared Sub RegisterFunction(ByVal type As Type)
Registry.ClassesRoot.CreateSubKey(GetSubkeyName(type))
End Sub

<ComUnregisterFunctionAttribute()> Public Shared Sub UnregisterFunction(ByVal type As Type)
Registry.ClassesRoot.DeleteSubKey(GetSubkeyName(type), False)
End Sub

Private Shared Function GetSubkeyName(ByVal type As Type) As String
Dim S As New System.Text.StringBuilder()
S.Append("CLSID\{")
S.Append(type.GUID.ToString().ToUpper())
S.Append("}\Programmable")
Return S.ToString()
End Function

End Class

'*******************************************************************************************************
'****************************** End VB 2008 Express Code ***************************************
'*******************************************************************************************************

 

[Chip Pearson]

You code looks OK, but your call to FindWindow is wrong. The class
name of the main Excel application window is 'XLMAIN', not 'Excel'. I
have a list of application window class names for the Office programs
at http://www.cpearson.com/Excel/OfficeAppsWindowClasses.aspx.

In NET, Integers are 32-bits, so where the VBA/VB6 documentation says
As Long, that should be As Integer in the NET source code. Thus, your
declaration of FindWindow should be

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" (
_
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Integer

Then, to call FindWindow, use

Dim XLHWnd As Integer
XLHWnd = FindWindow("XLMAIN",vbNullString)
If XLHWnd > 0 Then
' Excel is running
Else
' Excel is not running
End If

As far as calling subs that reside in your NET class library, you call
them as you would a sub of any object or ActiveX. Your code,

Dim MF As ExcelTrans.Tools
Set MF = New ExcelTrans.Tools
MF.myFormat

should work fine. What happens when you run it? It could very well be
that the problems with the FindWindow is throwing an exception in the
NET module, terminating it before it returns back to VBA.

I'm sure you know this, but it is worth saying just for the archives.
Your code creates a DLL, not an EXE, so it can't run on its own. It
has to be loaded by an exe. If you are working within the realm of
Excel, you can get the HWnd from the Application object within VBA and
pass that into the NET module.

' In the NET Module
Friend XLHWnd As Integer
Public Sub SetXLHwnd(HWnd As Integer)
XLHWnd = HWnd
End Sub

Then, in the VBA code, use

MF.SetXLHWnd Application.HWnd

In the NET world, you don't need to use HWnds very often. Really, only
when you are working with Win32 APIs that do things that NET doesn't
do (a list that grows smaller with every release of the Framework).

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[EagleOne]

Chip,

Thank you for the time and knowledge.

When I run: (prior to the changes you suggested)

Dim MF As ExcelTrans.Tools
Set MF = New ExcelTrans.Tools
MF.myFormat

I get "Run-time error '-2146233066 (80131516)':
Arithmetic operation resulted in an overflow.

Where I am lost is where to place the above lines to accomplish my task.

OK, my VBProject(Tools.xls) has a VBA Module "Tools" (Thus Tools.Tools ugh!!)

I want myFormat() to process the actual code text procedures (SubRoutines) in the VBProject.

i.e. Workbooks("Tools.xls").VBProject.VBComponents("Tools")

Thus, where do I tie in to MF.myFormat to Workbooks("Tools.xls").VBProject.VBComponents("Tools")?

Sub TestToolsDLL()

Dim MF As ExcelTrans.Tools
Set MF = New ExcelTrans.Tools
MF.myFormat <-----How connect------> (Workbooks("Tools.xls").VBProject.VBComponents("Tools")??)

End Sub

Sorry for being so dense.

In the meantime I'm modifying the code as we read.

TIA EagleOne

 

[EagleOne]

Chip,

I'm not sure where in the NET module do I place the "Friend" code? Only once in NET or twice
for myFormat and then myUnFormat?

This is what I did:

******************************** Net Code Module ***************************************
....
....
....
<ClassInterface(ClassInterfaceType.AutoDual), ComVisible(True)> Public Class Tools

Private Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal IpClassName As String, _
ByVal lpWindowName As String) As Integer

Friend XLHWnd As Integer
Public Sub SetXLHwnd(ByVal HWnd As Integer)
XLHWnd = HWnd
End Sub
....
....
....
Public Sub myFormat()

End Sub
....
....
Public Sub myUnFormat()

End Sub
....
....
....
End Class

***************************** VBA Module *************************************
Also, where does "MF.SetXLHWnd Application.HWnd" go in VBA? i.e.
..************************************************************************************

Sub TestToolsDLL()

Dim MF As ExcelTrans.Tools
Set MF = New ExcelTrans.Tools

MF.SetXLHWnd Application.HWnd ?????

MF.myFormat <-----How connect------> (Workbooks("Tools.xls").VBProject.VBComponents("Tools")??)

End Sub
************************************************************************************

Currently I'm debugging an issue with the code changes......

EagleOne

 

[EagleOne]

Chip,

The Build succeeded. I'll test the coding tomorrow.

EagleOne

 

[EagleOne]

Executing the DLL as:

......
......
Dim xlsApp As Microsoft.Office.Interop.Excel.Application
Dim xlsWB As Microsoft.Office.Interop.Excel.Workbook
....
....
If XLHWnd < 0 Then
MsgBox("Creating New session of Excel")
xlsApp = New Microsoft.Office.Interop.Excel.Application
Else
MsgBox("Connecting to Current session of Excel")
xlsApp = GetObject(, "Excel.Application")
End If

xlsApp.Visible = True
xlsWB = xlsApp.ActiveSheet
xlsSheet = xlsWB.Worksheets(1)

.....
.....
.....
The DLL executes to the point where I get a MsgBox message of
"Connecting to Current session of Excel" Then:

I get Error: Run-time error '-2147467262 (80004002)
Unable to cast COM object of type System.ComObject to interface type
'Microsoft.Office.Interop.Excel.Workbook' ... No such interface supported....

 

[Chip Pearson]

I get "Run-time error '-2146233066 (80131516)':
Arithmetic operation resulted in an overflow.

You'll have to track that down with standard debugging techniques.
With Excel closed, open your NET project in VS2008, open the MyProject
screen, go to Debug, and check "Start External Program" and enter the
full path to your version of Excel, e.g., "C:\Program Files\Microsoft
Office\Office12\Excel.exe". Build the solution and then press F5 to
run the program. Do whatever it is that you do in VBA. When the NET
module encounters the overflow, it will throw an exception and take
you in debug mode to the offending line in the NET module. There,
determine the cause of the error and fix it.

Where I am lost is where to place the above lines to accomplish my task.

The only connection between your VBProject and the NET module is via
the Reference you set to the DLL. via the References dialog in VBA.
The NET component is NOT a VBComponent. By setting the reference, you
tell the compiler that the NET component' DLL file is one of the
locations it should look when attempting to resolve variable
declarations and method calls.

I want myFormat() to process the actual code text procedures (SubRoutines) in the VBProject.

What do you mean by "process the actual code text procedures"?

i.e. Workbooks("Tools.xls").VBProject.VBComponents("Tools")
OK, my VBProject(Tools.xls) has a VBA Module "Tools" (Thus Tools.Tools ugh!!)

For clarity, I would get rid of all these "Tools" names. Even if the
compiler can sort them out properly, it is confusing. Name your
workbook "WBTools.xls", the VBProject of that workbook "projTools",
the module within the VBA project "modTools", the NET project
"ToolsNET" and the main class within that NET project
"ToolsFunctions". And for God's sake, don't name a VBA procedure or a
NET procedure "Tools". Even though it is technically legal, it is bad
programming practice to have different entities using the same name.
Also, I find it good practice to always use the library name when
declaring variables and calling procedures that are not native to
Excel or VBA. E.g., use "ToolsNET.ToolsFunctions.MySub" rather than
just MySub. This, too, make the code easier to maintain and debug.

Using good names will make it easier to keep track of exactly what it
is that you're derailing with.

The NET component, which I shall call ToolsNET, isn't part of any
workbook. A workbook can have a reference to ToolsNET set through the
References dialog in the VBA editor, but a reference isn't really much
more than an instruction to the compiler saying "look here for stuff".
There isn't any other connection between the workbook (and therefore
its VBProject) and the NET module. The NET module is its own entity --
it doesn't "belong" to any workbook. (This isn't true of VSTO
code-behind projects.)

Thus, where do I tie in to MF.myFormat to Workbooks("Tools.xls").VBProject.VBComponents("Tools")?

The Reference is the only thing that ties the VBProject to the NET
component. The ToolsNET component isn't a VBComponent so it has no
relevance to the VBComponents collection.

VBComponents("Tools")

This, again, is a problem. What VBComponent is referenced by "Tools".
A Module? A worksheet? Use better names.

MF.myFormat <-----How connect------> (Workbooks("Tools.xls").VBProject.VBComponents("Tools")??)

There is no connection between MF.myFormat and VBComponents("Tools").
The reference to the ToolsNET.ToolsFunctions NET component is global
in scope to the entire VBProject. VBComponents don't have their own
References, so once a reference is established between the VBProject
and the NET component, you're done. No such mechanism or entity exists
that can "connect" the NET component to a VBComponent.

You can declare a Public variable of the data type
ToolsNET.ToolsFunctions, before and outside of any procedure, and call
the methods of ToolsNET.ToolsFunctions from via that variable. E.g, in
VBA,

Option Explicit
Public MyToolsObject As New ToolsNET.ToolsFunctions

Sub ABC()
MyToolsObject.MyFormat()
' more code
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Chip Pearson]

I'm not sure where in the NET module do I place the "Friend" code? Only once in NET or twice

for myFormat and then myUnFormat?
Friend XLHWnd As Integer

This is a project-wide variable (declared within a module but before
and outside of any procedure), and therefore it can be written to and
read from by any procedure in the entire Project, any procedure in any
class or module. Once declared and assigned a value, that variable can
be accessed from any procedure in any module or class within the
project, so you want to declare it only once in the entire NET
project. This accessibility is called the "Scope" of a variable or
procedure. See http://www.cpearson.com/excel/Scope.aspx for more info
about the scope of variables and procedures.

"Friend" is a level of scope, akin to "Public" or "Private". "Private"
restricts access to a variable or procedure to the module or class in
which the variable or procedure is declared. "Public" makes it
available to any procedure in any module or class in the project,
*AND* to any other unrelated project that may reference and load that
assembly. "Friend" is the middle path. It allows access from any
procedure in any class or module within the project, but does not
expose itself, so to speak, outside of the project. In VBA, "Friend"
can be used only within object modules (classes, userform's,
ThisWorkbook, and the Sheet modules). In NET, you can use it almost
anywhere and with any type of object. (In VBA, the compiler directive
"Option Private Module" accomplishes the same things as a Friend
scoped variable in standard modules, in which the Friend scope
decelerator cannot be used.)

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[EagleOne]

Chip,

I knew I was in trouble when even I said ugh! to all of the "tools". Points well taken.

My concern re: "connecting" the DLL imbedded subroutine to the "objects" of my focus (the VBProject
Code Modules) was a non-issue. (The DLL actually contains code to Encode/Decode the code text for
security). myFormat, myUnformat were placeholder concepts for discussion purposes only.

The connection is established within the subroutine itself. Da! All I had to do was "kick-off" the
DLL to "act" upon the Code Modules.

BTW, you have a unique/excellent ability to bridge common-sense logic to very technical issues.

Your comments are loaded with substance which is "linkable" to those not-formally trained.
Why this is important, is because those-not-formally-trained may be owners of a business who have an
intrinsic need and heretofore have not been able to two-way-communicate with other techs.

I did not know how to actuate the VB Debugger. Thanks.

Last night I was able to start the DLL but it failed within the subroutine code itself. Therefore I
am in the front door. I'll use the debugger to run down the error.

The VBA code to trigger the DLL is:

Sub TestDLL 'I'm not sure if below was what is best.

Dim MF As ExcelTrans.CPATools
Set MF = New ExcelTrans.CPATools
MF.SetXLHWnd Application.hwnd
MF.CPAToolsDecode

End Sub

The DLL executes to the point where the MsgBox message of
"Connecting to Current session of Excel" is displayed.


Executing the DLL :

......
......
Dim xlsApp As Microsoft.Office.Interop.Excel.Application
Dim xlsWB As Microsoft.Office.Interop.Excel.Workbook
....
....
If XLHWnd < 0 Then
MsgBox("Creating New session of Excel")
xlsApp = New Microsoft.Office.Interop.Excel.Application
Else
MsgBox("Connecting to Current session of Excel")
xlsApp = GetObject(, "Excel.Application")
End If

xlsApp.Visible = True
xlsWB = xlsApp.ActiveSheet
xlsSheet = xlsWB.Worksheets(1)

.....
.....
.....

I get Error: Run-time error '-2147467262 (80004002)
Unable to cast COM object of type System._ComObject to interface type
'Microsoft.Office.Interop.Excel.Workbook' ... No such interface supported....

I believe I have an error either in xlsApp = GetObject(, "Excel.Application") or
in Dim xlsWB As Microsoft.Office.Interop.Excel.Workbook and/or with
xlsWB = xlsApp.ActiveSheet
xlsSheet = xlsWB.Worksheets(1)

TIA EagleOne

 

[EagleOne]

Well, I have big-time learned that there are reasons why Solution Providers are well compensated.

The process is clearly not "relatively-easy" coupled with good portion of "self-serving mystery" but
a real challenge of applying/communicating many interrelated sets of technology in an efficient
manner. I am truly in over my head.

EagleOne