?
Solved

My spreadsheet does not recognize a VBA function I have created

Posted on 2014-10-09
4
Medium Priority
?
272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Skype is a P2P (Peer to Peer) instant messaging and VOIP (Voice over IP) service – as well as a whole lot more.
Healthcare organizations in the United States must adhere to the guidance of both the HIPAA (Health Insurance Portability and Accountability Act) and HITECH (Health Information Technology for Economic and Clinical Health Act) for securing and protec…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

770 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