Solved

My spreadsheet does not recognize a VBA function I have created

Posted on 2014-10-09
4
247 Views
Last Modified: 2014-10-09
I have a spreadsheet that no longer recognizes a VBA function I have created. The function worked the last time I used the spreadsheet, but now it just returns #NAME?.

Let me know if you need me to attach the spreadsheet and any other needed files.

Thanks,

Jeff
0
Comment
Question by:Starbuck67
  • 2
  • 2
4 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40371787
Couple of possibilities come to mind:
1) The Function isn't saved in this workbook.
2) The function name isn't exactly the same as defined in VBA.

Of course, you can verify these by going into VBA and checking.  If your function is in your Personal Macro Workbook, you'll need to copy it to the one you wish to use it in.

-Glenn
0
 

Author Comment

by:Starbuck67
ID: 40371818
Hi Glenn,

I should have just posted here to begin with. Instead I spent a couple of hours searching the web for the same description I posted here.... :(

I had thought that the personal.xslb was available to any workbooks I was using. Personal. xslb loads every time I open a workbook. Also, I have created a number of macros that also show up in the VBA project area as modules and they are available to any workbook I am using.

I did copy the module with the function in it to the workbook that contains the use of that function and it works fine now.

Would be interested in hearing your thoughts, though.

Thanks,

Jeff
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40371861
VBA subroutines in the Personal Macro Workbook are available in an Excel instance, but not user-defined functions (UDFs).  Sad to say, but that's just a limitation we live with.  

However, there is resolution.  Create a blank workbook with the UDF(s) in the code and save it as an Excel Add-In file (.xlam).  Then, activate that add-in (Menu: File - Options - Add-Ins.  "Manage" section on bottom - "Excel Add-ins" - Go button).  Your function will be available.

-Glenn
0
 

Author Comment

by:Starbuck67
ID: 40371899
I saw some comments about .xlam files in my online research, but didn't look into them. Thanks for the explanation regarding those. I'll give it a try.

Jeff
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article shows how to convert a multi-page PDF file into multiple image files, with one image file created for each page of the PDF. It does this by utilizing an excellent, free software package called GraphicsMagick. The solution is amazingly s…
Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
The viewer will learn how to set up a document for the web and print and the recommended PPI for printing.
The viewer will learn how to successfully download and install the SARDU utility on Windows 8, without downloading adware.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now