Solved

My spreadsheet does not recognize a VBA function I have created

Posted on 2014-10-09
4
271 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 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Let’s list some of the technologies that enable smooth teleworking. 
All of the resources available today make learning a new digital media easier than ever-- if you know where to begin. This is a clear, simple guide to a few of the basic digital art mediums and how to begin learning them on your own.
This video demonstrates basic masking and how to edit the mask to reveal the desired image.
This video shows how use content aware, what it’s used for, and when to use it over other tools.

717 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