If you follow the example on the web page at
http://www.cpearson.com/excel/Creati...nctionLib.aspx , you'll
have a class lib of functions that can be called from both a worksheet
cell, and, by setting a reference to the typelib.tlb file, in VBA. In
the code you posted, the line

<ComVisibleAttribute(False) _

should be

<ClassInterface(ClassInterfaceType.AutoDual), ComVisible(True) _


After all the functions in the class, use following, right before the
End Class marker. No changes are required for this code.

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

<ComUnregisterFunctionAttribute() _
Public Shared Sub UnregisterFunction(ByVal type As Type)
Registry.ClassesRoot.DeleteSubKey(GetSubkeyName(ty pe), 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

You can't really "compile" VBA code into a DLL. You can, of course,
write the functionally equivalent code in one of the NET languages and
use that DLL, or you can have functions in VBA that do nothing beyond
calling functions in the NET dll.

There may be limitations on what can be done in the Express version of
VBNET. I'm don't they might be -- I use the Pro version of VS2008.

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





On Fri, 22 May 2009 14:08:58 -0400,
wrote:


Chip, I found the following but do not fully comprehend its impact.

Imports System
Imports System.Runtime.InteropServices

<Assembly: ComVisibleAttribute(False)
Namespace InteroperabilityLibrary

<ComVisibleAttribute(False) _
Public Class BaseClass

Sub SomeSub(valueOne As Integer)
End Sub

End Class

' This class violates the rule.
<ComVisibleAttribute(True) _
Public Class DerivedClass
Inherits BaseClass

Sub AnotherSub(valueOne As Integer, valueTwo As Integer)
End Sub

End Class

End Namespace


I learn best if I have a "Template" that can be modified.

One of my challenges is that I never had assess to VB6.

Do you have a template that I could review so that I can get perspective to the task?


I am aware of the following on your site:
http://www.cpearson.com/excel/workbooktimebomb.aspx
http://www.cpearson.com/excel/creatingcomaddin.aspx
http://www.cpearson.com/Excel/creati...nctionlib.aspx

Since I do not have any .NET nor VB6 (just VS 2008 Visual Basic Express), my mind can not make the
leap.

EagleOne


Chip Pearson wrote:



which call a DLL (Excel
VBA-compiled in VS 2008 Visual Basic)

What, exactly, does that mean? Did you create an Excel 2007
code-behind workbook or did you create Class Library DLL?

If you're referring to a DLL created as a class library, that assembly
must have been created as a COM visible assembly. That allows
COM-based applications (VB, VBA, VB6, etc) to call its functions. If
it isn't COM-visible, you won't be able to call its functions. You set
this property from the Assembly Information dialog on the Application
page of the My Project member of your project.

Once you have compiled the DLL as COM-visible, open the VBA editor, go
to the Tools menu, choose References and then Browse. Locate the
typelib file for the DLL (in the same folder as the DLL itself) and
load that typelib. Use the typelib file (assm_name.tlb not
assem_name.dll) rather than the DLL file.

Once you do that, you can call members of the one or more classes that
make up the DLL. For example, if your DLL is named MyExcelDLL.dll and
the class that contains the functions is called MyFunctionClass, you
can call a function, MyRealFunction with code like

Dim MF As MyExcelDLL.MyFunctionClass
Set MF = New MyExcelDLL.MyFunctionClass
Result = MF.MyRealFunction(1234)

With NET dlls, you don't use the Declare statement. That is used only
for Win32 DLLs, such as those created with __stdcall in VC++.

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



On Fri, 22 May 2009 09:58:38 -0400,
wrote:

2003, 2007

Possible for someone to provide a few examples written in Excel VBA, which call a DLL (Excel
VBA-compiled in VS 2008 Visual Basic)?

In short, I have the myExcelVBA.DLL but cannot find VBA examples to "call" it properly.

How/Where do I declare the libraries necessary? (Assume I need to use all libraries/references used
to run the VBA from the VBA editor window.

TIA EagleOne