Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Excel custom function

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    UK - London
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Guest

    Default

    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

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    UK - London
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •