2

I'm trying to create my first Excel Add-In for Excel 2010. Most of it is working, it's running from the ribbon buttons, but I'm having trouble addressing the Add-In's subroutines from my workbook's VBA code.

According to this answer on SO, it should be possible to simply use the syntax:

mySub

or

Call mySub

But this causes the error "Sub or Function not defined". I've only managed to run them this way:

Run "mySub"

or

Application.Run("myAddIn.xlam!mySub")

Is there a way to include the Add-In so I can address it the easy way?

The Add-In is already checked in the Tools->Add-Ins list, and has a unique name (CalcFunctions) which is different from its file name (CalculationFunctions.xlam). The Add-In file is on a different disk and I'm working on a server, but I don't expect that that matters.

Improve this question
2
  • 2
    You need to set a reference in the VBProject of your workbook to the add-in. You can only do that if you have changed the project name of the add-in to something other than the default VBAProject. Commented Jul 19, 2016 at 8:54
  • Thanks! I thought I did that already but I'm dumb. I edited the question with details. Commented Jul 19, 2016 at 9:09

1 Answer 1

Reset to default
1

(Posted on behalf of the OP).

Turns out I'm just dumb. I only set a reference to the Add-In in the regular Excel window. I assumed that was what people meant by Tools->References->Add-Ins (I use Excel in Dutch and there's no "Tools" menu). Anyway, the solution was setting a reference in that menu in the VBA editor window.

Here's how to add a reference in VBA:

ThisWorkbook.VBProject.References.AddFromFile refPath

With refPath being the full path to the file.

To be able to add references you need to have permission to edit the VBA project. You can enable this in excel settings->trust center->macro settings. If you're on a company pc it's likely the administrator has to set these settings for you.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.