Excel and cross-workbook code

[alkatran]

I have an excel project that has modules and class modules which need to be in almost every workbook. It's a hassle to have to make sure every workbook has the most up to date code, and I was just wondering if there was some way to make code available to the entire excel application? DLLs maybe? (which I would go about making, how?)

[RobDog888]

I see three ways to do it.

1. Create an Excel Add-In.
2. Create a dll.
3. Use code to modify the macros in all workbooks

I havent done it myself for VBA but I would assume that if you create a VB Dll to do it
it would be the same. otherwise you would need the Developer version of
Office which can compile.

I think it may be easiest to use my CodeBank code to modify VBA code from VB (#3)!
You can check it out here.

HTH

[alkatran]

I see three ways to do it.

1. Create an Excel Add-In.
2. Create a dll.
3. Use code to modify the macros in all workbooks

I havent done it myself for VBA but I would assume that if you create a VB Dll to do it
it would be the same. otherwise you would need the Developer version of
Office which can compile.

I think it may be easiest to use my CodeBank code to modify VBA code from VB (#3)!
You can check it out here.

HTH

All of the code is protected so code modifying code won't work. Apparently the user needs to add the addin, and this is supposed to have as little to do with excel (according to the user) as possible.

How would I go about the DLL? I've never used a non-windows API one before.

[RobDog888]

I am not 100% sure, but I believe you can just create the dll in VB. Start VB
and select ActiveX Dll as the project. Then add your code and compile. In
the vba project properties of an Excel workbook, you can add the reference
to the dll. Then the wb should act like the code is all behind the wb.

I am only not sure of this because they do have a Developer version for
Office XP only and they havent put anything out for any other version. SoI
think it should work. Although if for 2003, I know they have VSTO which does
create dlls but is used with .NET

HTH

[alkatran]

I am not 100% sure, but I believe you can just create the dll in VB. Start VB
and select ActiveX Dll as the project. Then add your code and compile. In
the vba project properties of an Excel workbook, you can add the reference
to the dll. Then the wb should act like the code is all behind the wb.

I am only not sure of this because they do have a Developer version for
Office XP only and they havent put anything out for any other version. SoI
think it should work. Although if for 2003, I know they have VSTO which does
create dlls but is used with .NET

HTH

Alright, so I go in vb and make a dll then:
Write the code:
public function test as single
test = rnd*50
end function

Compile
Add it as a reference
Then in excel I write
msgbox test
?

[alkatran]

nevermind, i got it. I actually have to create an instance of the class...

Wow, that's cool!

Now, can I use code with excel commands (worksheet) in these dlls somehow?

[RobDog888]

Yes, add a reference to Excel but it will be removed before you compile. Then
program in the reference object you need. Then when done change your
Excel Object declarations to late bound and remove the reference. This will
make it easier to program and also allow you to use the dll accross multiple
versions of Excel.

[alkatran]

For example, I want to do this:

application.enableevents = false

But i get a compile error (duh) because application isn't defined. Is there anyway to get around this?

[alkatran]

Alright, next:

How do I get at a public function inside a module in the dll? Classes is easy, but I can't seem to access the modules.

[RobDog888]

In the VBA Editor with the reference to your dll, use the Object Browser to see
if you can see your function in the module. If its declared as Public then it
should be visible in the OB.

[alkatran]

In the VBA Editor with the reference to your dll, use the Object Browser to see
if you can see your function in the module. If its declared as Public then it
should be visible in the OB.

They don't show up, and they are all declared public.

[RobDog888]

I'm not 100% fluent at dlls, but it should be visible. What if you placed it in the class?

[alkatran]

I'm not 100% fluent at dlls, but it should be visible. What if you placed it in the class?

I tried placing it in the class, but I would have to rewrite all the functions calls to include the "class.", and the classes called by the dll would be different instances than the ones I called (inside one class another is called, so it must be declared with, etc.) I just decided that this type fo thing didn't belong inside a DLL.

[RobDog888]

If a dll is no longer considered the way to go then what about the Add-In?

[alkatran]

If a dll is no longer considered the way to go then what about the Add-In?

I just realized I can include a form in the dll, and use control arrays in excel!

I'm going to stay the path of 'copy-paste' for now.