Two questions ............

1. Can VBA be used to create the procedures in a dll file?


2. Can anyone give me recommendations for free downlaodable tools for
compiling a dll file?


Thanks!!

VB/VBA can be used to create ActiveX DLL files, not Win32 DLL files (those
accessed with the Declare statement in VBA). The typical DLLs created by
VB/VBA are COM Add-Ins or Automation Add-Ins. These are special cases of a
generic ActiveX DLL.

See http://www.cpearson.com/excel/CreatingCOMAddIn.aspx for details about
writing a COM Add-In for Excel (and other Office applications) and see
http://www.cpearson.com/excel/AutomationAddIns.aspx for details about
writing Automation Add-Ins.

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

"OfficeUser" wrote in message
...
Two questions ............

1. Can VBA be used to create the procedures in a dll file?


2. Can anyone give me recommendations for free downlaodable tools for
compiling a dll file?


Thanks!!

Chip,

Thanks for the quick response!

I want to create an Add-In for Excel that will work in all versions of Excel
from 97 to 2007. Can a COM Add-in be used? Weren't Com-Add-ins first
introduced in OfficeXP?

Thanks!


"Chip Pearson" wrote in message
...
VB/VBA can be used to create ActiveX DLL files, not Win32 DLL files (those
accessed with the Declare statement in VBA). The typical DLLs created by
VB/VBA are COM Add-Ins or Automation Add-Ins. These are special cases of a
generic ActiveX DLL.

See http://www.cpearson.com/excel/CreatingCOMAddIn.aspx for details about
writing a COM Add-In for Excel (and other Office applications) and see
http://www.cpearson.com/excel/AutomationAddIns.aspx for details about
writing Automation Add-Ins.

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

"OfficeUser" wrote in message
...
Two questions ............

1. Can VBA be used to create the procedures in a dll file?


2. Can anyone give me recommendations for free downlaodable tools for
compiling a dll file?


Thanks!!

Weren't Com-Add-ins first
introduced in OfficeXP?

Com-Add-ins were first introduced in Excel 2000. Automation Add-ins were
introduced in Excel 2002, which allowed UDF's to be called directly from the
sheet, instead of via a helper helper UDF in VBA.

You can make your ActiveX dll, ie the Com-addin, available for use in
Excel97 by adding an additional Public class to the dll. You also need a VBA
'wrapper' to call functions or other procedures in the public class.

Regards,
Peter T

"OfficeUser" wrote in message
...
Chip,

Thanks for the quick response!

I want to create an Add-In for Excel that will work in all versions of
Excel
from 97 to 2007. Can a COM Add-in be used? Weren't Com-Add-ins first
introduced in OfficeXP?

Thanks!


"Chip Pearson" wrote in message
...
VB/VBA can be used to create ActiveX DLL files, not Win32 DLL files
(those
accessed with the Declare statement in VBA). The typical DLLs created by
VB/VBA are COM Add-Ins or Automation Add-Ins. These are special cases of
a
generic ActiveX DLL.

See http://www.cpearson.com/excel/CreatingCOMAddIn.aspx for details
about
writing a COM Add-In for Excel (and other Office applications) and see
http://www.cpearson.com/excel/AutomationAddIns.aspx for details about
writing Automation Add-Ins.

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

"OfficeUser" wrote in message
...
Two questions ............

1. Can VBA be used to create the procedures in a dll file?


2. Can anyone give me recommendations for free downlaodable tools for
compiling a dll file?


Thanks!!

Thanks, Peter!

<<You can make your ActiveX dll, ie the Com-addin, available for use in
Excel97 by adding an additional Public class to the dll. You also need a VBA
'wrapper' to call functions or other procedures in the public class.

Can you give me a simple code example of all this where the Com-addin brigs
up the message "Thanks, Peter T".



"Peter T" <peter_t@discussions wrote in message
...
Weren't Com-Add-ins first
introduced in OfficeXP?

Com-Add-ins were first introduced in Excel 2000. Automation Add-ins were
introduced in Excel 2002, which allowed UDF's to be called directly from
the
sheet, instead of via a helper helper UDF in VBA.

You can make your ActiveX dll, ie the Com-addin, available for use in
Excel97 by adding an additional Public class to the dll. You also need a
VBA
'wrapper' to call functions or other procedures in the public class.

Regards,
Peter T

"OfficeUser" wrote in message
...
Chip,

Thanks for the quick response!

I want to create an Add-In for Excel that will work in all versions of
Excel
from 97 to 2007. Can a COM Add-in be used? Weren't Com-Add-ins first
introduced in OfficeXP?

Thanks!


"Chip Pearson" wrote in message
...
VB/VBA can be used to create ActiveX DLL files, not Win32 DLL files
(those
accessed with the Declare statement in VBA). The typical DLLs created
by
VB/VBA are COM Add-Ins or Automation Add-Ins. These are special cases
of
a
generic ActiveX DLL.

See http://www.cpearson.com/excel/CreatingCOMAddIn.aspx for details
about
writing a COM Add-In for Excel (and other Office applications) and see
http://www.cpearson.com/excel/AutomationAddIns.aspx for details about
writing Automation Add-Ins.

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

"OfficeUser" wrote in message
...
Two questions ............

1. Can VBA be used to create the procedures in a dll file?


2. Can anyone give me recommendations for free downlaodable tools for
compiling a dll file?


Thanks!!

Following assumes a VB6 ActiveX dll

In your project set the Excel reference to Excel 8.0(97) and the Office ref
to Office 9.0(2000). You might expect the later Office ref to fail when used
with Excel97 but it's always worked OK for me like that, but you need the
Office9.0 ref with the Com Addin, it won't work with Office8.

Add a MultiUse class to your project (the Com addin). Put DllTest in this
class paste DllTest

Public Function DllTest(xl As Excel.Application, s As String) As Long
' might want to set a module/global ref to XL for use throughout the dll,
' eg set it to the same ref you would have got from the Com connection
' If you need xl's window, eg to attach your forms to, get it now
' (don't forget you need API's to get xl's hWin in XL97/2K).

Dim cell As Excel.Range
Dim ws As Excel.Worksheet

On Error GoTo errH

Set ws = xl.ActiveSheet
Set cell = ws.Range("A1")

If Len(s) = 0 Then
s = "Hi there world"
End If

cell = s
MsgBox s & vbCr & ws.Name, vbMsgBoxSetForeground, App.EXEName
s = "and hello to you too..."
Exit Function
errH:

DllTest = Err.Number

End Function


In your VBA project, eventually your xla wrapper, set a ref in Tools to your
Dll. If running in VB's IDE (eg you had pressed Ctrl-F5), the ref would be
to the Project's .vbp

Sub CheckDll()
Dim x As Long
Dim sMsg As String
' change Project1 to the name of your dll (without extension)
' and Class1 to that of your MultiUse Class

Dim cls As Project1.Class1

Set cls = New Project1.Class1

sMsg = "Hello World"

x = cls.DllTest(Application, sMsg)

' if running in the IDE's might need to switch to Excel
' to see this msg
If x = 0 Then
MsgBox sMsg
Else
MsgBox x, , "error"
End If
Debug.Print x

End Sub

If you've got it all set up right, the correct ref's and you pressed F5 in
VB's IDE, you should be able to step through from VBA into VB and back again
with F8.

Later, depending on what you are doing overall, you might be able to remove
the ref in VBA and use Late Binding

Dim cls As Object
on error resume next
Set cls = createobject("dllName.className")
If not cls is nothing then
x = cls.DllTest(Application, sMsg)
Else
' the dll is not registered in user's system
' code to do Regsvr32 the dll
End if

There are many other things that will need to be right !

Regards,
Peter T


"OfficeUser" wrote in message
...
Thanks, Peter!

<<You can make your ActiveX dll, ie the Com-addin, available for use in
Excel97 by adding an additional Public class to the dll. You also need a
VBA
'wrapper' to call functions or other procedures in the public class.

Can you give me a simple code example of all this where the Com-addin
brigs
up the message "Thanks, Peter T".



"Peter T" <peter_t@discussions wrote in message
...
Weren't Com-Add-ins first
introduced in OfficeXP?

Com-Add-ins were first introduced in Excel 2000. Automation Add-ins were
introduced in Excel 2002, which allowed UDF's to be called directly from
the
sheet, instead of via a helper helper UDF in VBA.

You can make your ActiveX dll, ie the Com-addin, available for use in
Excel97 by adding an additional Public class to the dll. You also need a
VBA
'wrapper' to call functions or other procedures in the public class.

Regards,
Peter T
<snip

I thought you could make Win32 dll's in VB with
the VB Advance add-in, which I understand is now free.

RBS


"Chip Pearson" wrote in message
...
VB/VBA can be used to create ActiveX DLL files, not Win32 DLL files (those
accessed with the Declare statement in VBA). The typical DLLs created by
VB/VBA are COM Add-Ins or Automation Add-Ins. These are special cases of a
generic ActiveX DLL.

See http://www.cpearson.com/excel/CreatingCOMAddIn.aspx for details about
writing a COM Add-In for Excel (and other Office applications) and see
http://www.cpearson.com/excel/AutomationAddIns.aspx for details about
writing Automation Add-Ins.

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

"OfficeUser" wrote in message
...
Two questions ............

1. Can VBA be used to create the procedures in a dll file?


2. Can anyone give me recommendations for free downlaodable tools for
compiling a dll file?


Thanks!!

I thought you could make Win32 dll's in VB with
the VB Advance add-in, which I understand is now free.

I was not aware of that, but it sounds good to me.


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


"RB Smissaert" wrote in message
...
I thought you could make Win32 dll's in VB with
the VB Advance add-in, which I understand is now free.

RBS


"Chip Pearson" wrote in message
...
VB/VBA can be used to create ActiveX DLL files, not Win32 DLL files
(those accessed with the Declare statement in VBA). The typical DLLs
created by VB/VBA are COM Add-Ins or Automation Add-Ins. These are
special cases of a generic ActiveX DLL.

See http://www.cpearson.com/excel/CreatingCOMAddIn.aspx for details about
writing a COM Add-In for Excel (and other Office applications) and see
http://www.cpearson.com/excel/AutomationAddIns.aspx for details about
writing Automation Add-Ins.

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

"OfficeUser" wrote in message
...
Two questions ............

1. Can VBA be used to create the procedures in a dll file?


2. Can anyone give me recommendations for free downlaodable tools for
compiling a dll file?


Thanks!!