SOLVED

Excel/VBA Function

%3CLINGO-SUB%20id%3D%22lingo-sub-1590805%22%20slang%3D%22en-US%22%3EExcel%2FVBA%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590805%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%20I%20have%20an%20issue%20with%20a%20VBA%20function%20created%20which%20doesn't%20save%20along%20with%20the%20excel%20document%20(%20it%20is%20a%20.xlsm%20file).%20When%20I%20re-open%20the%20excel%2C%20in%20the%20cell%20where%20the%20formula%20is%2C%20now%20comes%20up%20as%20an%20%3CU%3EInvalid%20Name%20Error%3C%2FU%3E.%20I%20do%20need%20to%20mention%20that%20I%20have%20followed%20all%20steps%20suggested%20by%20this%20error.%20Has%20anyone%20faced%20this%20problem%20before%3F%20Can%20I%20please%20get%20a%20bit%20of%20advice.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1590805%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1590819%22%20slang%3D%22de-DE%22%3ESubject%3A%20Excel%2FVBA%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590819%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F760658%22%20target%3D%22_blank%22%3E%40CatalinaDragomir%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EIt%20would%20be%20of%20great%20advantage%20if%20you%20insert%20a%20file%20(without%20sensitive%20data)%20so%20that%20we%20can%20see%20and%20understand%20the%20problem%20better.%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3ESo%20you%20can%20be%20helped%20faster%20and%20more%20precisely.%3C%2FSPAN%3E%3CBR%20%2F%3E%20%3CSPAN%3ESo%20i%20can%20only%20guess%20and%20share%20with%20you%20the%20first%20thought%20that%20goes%20through%20my%20head.%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3EWould%20conditional%20formatting%20help%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%20%3CSPAN%3EIf%20the%20error%20doesn't%20bother%20somewhere%20else%2C%20you%20could%20make%20it%20disappear%20with%20the%20conditional%20formatting%20(without%20actually%20being%20gone).%3C%2FSPAN%3E%20%3CSPAN%3ENow%20a%20thought%20...%20preferably%20a%20file.%20%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSPAN%3ENikolino%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20know%20that%20I%20don't%20know%20(Socrates)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1590834%22%20slang%3D%22en-US%22%3EBetreff%3A%20Excel%2FVBA%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590834%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3BThanks%20for%20your%20reply.%3C%2FP%3E%3CP%3EI%20have%20attached%20the%20.xlsm%20file%20I%20was%20referring%20to.%20The%20cell%20where%20the%20custom%20function%20is%20E6.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20mention%20that%20I%20am%20not%20an%20Excel%20pro%2C%20as%20you%20can%20tell%2C%20and%20all%20I%20did%20was%20to%20follow%20step%20by%20step%20tutorials.%20I%20might%20have%20missed%20something%20along%20the%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20need%20this%20formula%20to%20work%20when%20I%20re-open%20the%20file.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1590866%22%20slang%3D%22de-DE%22%3ESubject%3A%20Excel%2FVBA%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590866%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F760658%22%20target%3D%22_blank%22%3E%40CatalinaDragomir%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EMake%20it%20disappear%20on%20the%20quick%20with%20Conditional%20Formatting.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EHave%20the%20whole%20column%20in%20the%20conditional%20formatting%20so%20that%20all%20letters%20are%20white%20in%20the%20event%20of%20an%20error.%20Thus%20not%20visible%20to%20the%20eye%20of%20the%20beholder.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EEnclosed%20your%20file%3C%2FSPAN%3E.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20find%20this%20helpful%2C%20please%20mark%20it%20as%20%22Best%20Answer%22%20and%20as%20Like%20(click%20thumbs%20up)%2C%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1590898%22%20slang%3D%22en-US%22%3EBetreff%3A%20Excel%2FVBA%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590898%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F760658%22%20target%3D%22_blank%22%3E%40CatalinaDragomir%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20works%20fine%20for%20me.%20Perhaps%20double%20check%20your%20macro%20settings%20are%20enabled%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Efile%5Coptions%5Ctrust%20center%5Ctrust%20center%20settings%5Cmacro%20settings%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20I%20noticed%20the%20function%20is%20in%20two%20modules%20(Sheet1%20and%20Module%201)%3F%20Only%20user%20defined%20functions%20in%20standard%20modules%20can%20be%20used%20in%20a%20worksheet%20formula.%20I%20would%20delete%20the%20copy%20in%20Sheet1.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1591317%22%20slang%3D%22en-US%22%3EBetreff%3A%20Excel%2FVBA%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1591317%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot%20for%20trying%20to%20help%20me.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20looked%20to%20the%20macro%20setting%20and%20enabled%20them%2C%20apparently%20that%20was%20the%20issue.%20Now%20it%20is%20working%20fine.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20great%20day%2C%20and%20thanks%20again%20for%20your%20time.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1591513%22%20slang%3D%22en-US%22%3EBetreff%3A%20Excel%2FVBA%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1591513%22%20slang%3D%22en-US%22%3EYou're%20welcome.%20Have%20a%20good%20day!%3C%2FLINGO-BODY%3E
New Contributor

Hello everyone, I have an issue with a VBA function created which doesn't save along with the excel document ( it is a .xlsm file). When I re-open the excel, in the cell where the formula is, now comes up as an Invalid Name Error. I do need to mention that I have followed all steps suggested by this error. Has anyone faced this problem before? Can I please get a bit of advice.

 

Many thanks in advance

6 Replies

@CatalinaDragomir 

It would be of great advantage if you insert a file (without sensitive data) so that we can see and understand the problem better. thus you can also be helped faster and more precisely.
So i can only guess and share with you the first thought that goes through my head.

 

Would conditional formatting help?
If the error doesn't bother somewhere else, you could make it disappear with the conditional formatting (without actually being gone). Now a thought ... preferably a file.

Nikolino
I know that I don't know (Socrates)

@Nikolino Thanks for your reply.

I have attached the .xlsm file I was referring to. The cell where the custom function is E6.

 

I need to mention that I am not an Excel pro, as you can tell, and all I did was to follow step by step tutorials. I might have missed something along the way.

 

But I need this formula to work when I re-open the file. 

@CatalinaDragomir 

 

Make it disappear on the quick with Conditional Formatting.

Have the whole column in the conditional formatting so that all letters are white in the event of an error. Thus not visible to the eye of the beholder.

Enclosed your file.

 

I would be happy to know if I could help.

 

If you find this helpful, please mark it as "Best Answer" and as Like (click thumbs up), it will be beneficial to more Community members reading here.

 

Nikolino

I know I don't know anything (Socrates)

Best Response confirmed by CatalinaDragomir (New Contributor)
Solution

@CatalinaDragomir 

 

It works fine for me. Perhaps double check your macro settings are enabled?

 

file\options\trust center\trust center settings\macro settings

 

Also, I noticed the function is in two modules (Sheet1 and Module 1)? Only user defined functions in standard modules can be used in a worksheet formula. I would delete the copy in Sheet1.

@JMB17 

 

Thanks a lot for trying to help me. 

 

I have looked to the macro setting and enabled them, apparently that was the issue. Now it is working fine. 

 

Have a great day, and thanks again for your time. 

You're welcome. Have a good day!