Excel custom function

[Douglas]

I've created a custom function in VBA and made it available for use on my PC by saving it as an add in. Unfortunately, when I e-mail a report which uses the function, the recipient gets error messages where-ever the formula appears.

Other than sending valued versions of the report can anyone suggest how to overcome this easily.

Thanks
Douglas

[ This Message was edited by: Douglas on 2002-02-20 03:04 ]

[dk]

If the custom function resides in an add-in on your machine then the function will return a #NAME? error on any machine which doesn't also have the add-in available. I can think of 3 options:-

1. As you suggest, use values.
2. Place the UDF code in the workbook you're sending, rather than in the add-in.
3. Send the add-in to the recipient and ask them to install it on their machine. Your functions will then work as normal.

HTH,
D

Thanks for your reply.

I've been thinking that it'd be useful to have a button on my toolbar which would insert a module on the activework and add the bit of code I need.

I've tried recording something as a start but whenever I leave Excel to VVA the macro doesn't record ....

Can you suggest

Cheers

[dk]

Again, there are various ways you could do this:-

You could write the code 'on-the-fly' i.e. have a macro which writes another macro in the activeworkbook. This can be complicated and involves gaining an understanding of the VBA Extensibility objects.

An easy way would be to export your module to somewhere on your hard drive (just right click the module and choose Export File).

Then in your add-in use this code to add the module to the current workbook:-

Sub AddModule()
ActiveWorkbook.VBProject.VBComponents.Import "C:tempbasyourmodule.bas"
End Sub

HTH,
D

[Douglas]

Thank you - this works a treat.
I exported it to a shared drive so the rest of the team can use it!!