Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

My spreadsheet does not recognize a VBA function I have created

Posted on 2014-10-09
4
Medium Priority
?
274 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 2000 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
Choosing the right mix of apps is very much necessary for CPAs for making the most of the latest technology through which they can boost their growth.
Using Adobe Premiere Pro, the viewer will learn how to set up a sequence with proper settings, importing pictures, rendering, and exporting the finished product.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

577 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