CODEWITHSUNDEEP
excelvba
amris
amris

Reputation: 23

Referencing and VBA applications

I have a simple question concerning references in excel formulas and VBA.

I am currently taking a course in asset pricing with applications in VBA. As I am very new to VBA, I have become very frustrated about a certain problem concerning solution sheets from my lecturer with reference to directories on his computer.

In the course we're gradually expanding a set pricing library called analytics.xlsm and as I understand this is a macro-enabled workbook. Within the project, there is a module containing pricing functions.

So, when I download my lecturer solution sheets all of the formulas containing pricing functions are referenced to a directory on his computer. As I understand, this is a 'link'. When I open the workbook, I have the option to change the link to my own analytics.xlsm.

I have tried this, among other things, but all I get is the #NAME? value in every cell referenced from or containing a function from analytics.xlsm.

I've tried the following in different orders and combinations:

I hope that someone in here can help me understand how this works. Ideally, I would just want to download his solution workbook with formulas containing only the function written in the pricing module in which case I could just load analytics.xlsm as an add-in. This works when I solve the assignments myself.

Example:

I want

=swaprate(..,..,.....)

instead of

='machintosh......analytics.xlsm!swaprate(..,..,.....)

I'm using excel for MAC and so is my lecturer. Thank you in advance!

Upvotes: 2

Views: 69

Answers (1)

Matteo NNZ
Matteo NNZ

Reputation: 12655

The reason why this happens is that the add-in file containing the function swaprate() is local. In other words:

  1. Your lecturer opens the file analytics.xlsm with his Excel; his Excel is using an add-in, let's call it functions.xlam, and this file is stored on his local machine: for example, C:\Lectures\Financials\functions.xlam
  2. Your lecturer inserts the external function in a cell of the analytics.xlsm, for example he writes in A1 the function =swaprate(1,2,3).
  3. Then he uploads the file on the web to let you download it. When this happens, add-ins are lost so the file analytics.xlsm (which contains the function swaprate() referencing to his local add-in) gets the originary source path 'C:\Lectures\Financials\functions.xlam' before the name of the function.

So, technically (to test), the solution to update the link should be:

  • After downloading the file with the now broken links, go to "Data", then "Edit Links" and update the source of the link to where your local add-in lies;
  • Hence, you will now have the result as wished. Please note that if you're still seeing your local path before the function name (e.g. 'C:\Student\functions.xlam!'swaprate()) it means that this particular add-in (lying on C:\Student\) is not active. You can activate it by Options/Add-Ins menu of Excel.

Upvotes: 1

Related Questions