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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.

RBS


"Chip Pearson" <(E-Mail Removed)> wrote in message
news:OrX$4%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
news:OrX$4%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:OrX$4%(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:OrX$4%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:OrX$4%(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> >> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> news:(E-Mail Removed)...
> >> 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>