Solved

Trying to understand why a certain function name is creating an error in Excel 2011 when altering name works

Posted on 2015-02-08
18
92 Views
Last Modified: 2016-02-11
Using:  Excel 2011 ver 14.4.7

If I run this function:

Function SMHST(num1 As Currency)
'calculates the TAX
SMHST = num1 * 0.05
End Function

and use:

=SMHST(1)

in a worksheet cell, it works, I get 0.05 in the cell.

If I use a very similar function name (for example, inverting the first two letters of the function name), i get a #NAME error.

For example if I edit the same function like so:

Function MSHST(num1 As Currency)
'calculates the TAX
MSHST = num1 * 0.05
End Function

and use:

=MSHST(1)

in the same worksheet cell, it doesn't work!?  I get error #NAME instead of 0.05

I can reproduce this error back and forth in the workbook (see attached illustrative image).

Any idea why?

Function-Name-Error.jpg
0
Comment
Question by:qeng
  • 9
  • 6
  • 3
18 Comments
 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept earned 250 total points
ID: 40597452
Both work for me. I have Excel 2011 on Max OSX. I copied and pasted both to a module named module1.
Have you tried re-compiling the modules in the workbook?
0
 
LVL 11

Accepted Solution

by:
Dany Balian earned 250 total points
ID: 40597473
1. You don't need to name the module the same name as the function! Try to name the module "functions" for instance and try again!
2. Do u have another variable/function named MSHST? Maybe in the other module (helloworld)?
3. Does this only happen with MSHST or u have other combinations that reproduce the same error?
0
 

Author Comment

by:qeng
ID: 40597475
Hey Simon, thanks for stopping in on this question too (I was inviting you to do so while closing out one of my other questions you'd just solved but you were already here before I'd re-posted!).

I understand the concept of 're-compiling' but not sure of the commands.  I'll dig and give it a shot.

In reproducing the error though, I was never re-compiling.  I would make the changes you saw in the attached image, and just go back to the spreadsheet.  I could keep making the changes back and forth without re-compiling (knowingly anyhow; unless I was inadvertently using a different sequence to get back to the worksheet) and I would get the same results:  SMHST would work and MSHST wouldn't.

How I came to this was trying to troubleshoot why I was getting a #NAME error when I'd named the function 'HST'.  I found that if I changed the name of the function (adding matching change on the effective line of code and just for safety , changing the module name to match) the function would work.  I started working my way through various name changes trying to isolate the root of the problem.

I'll try re-compiling as you suggest to see if that helps.
0
 

Author Comment

by:qeng
ID: 40597498
Dany,

Per your point 1, I've found that the problem occurs when the Module is named the same as the Function (but oddly that doesn't occur with SMHST).  If I name function 'MSHST' and rename the module 'FUNCTIONS', it works.

To answer your other questions (for the record):  

2 ... no, I didn't have any other variable/function named MSHST
3 ... it's not predictable (yet!), if I call the function as well as the module 'ALBERT' i get the #NAME error, and same thing if I go back and rename both MSHST, same error.  But if I rename both SMHST, it works!?

Though I seem to have a fix with the re-naming of the Module to a different name than the function, I'll try also the re-compiling idea which Simon suggested to see if there is an underlying issue around that.
0
 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept earned 250 total points
ID: 40597786
>I understand the concept of 're-compiling'
The menu item for this is Tools/Re-Compile [Name of your project]
0
 

Author Comment

by:qeng
ID: 40598115
Thx Simon,

On my Excel version, it looks like the Re-Compile command sits under Debug > Compile [Name of my project].

When I execute that, it doesn't matter whether I have the module named identically to the function or whether I use two different names, compiling, in all cases, throws the following error:

Compile error:
Expected variable or procedure, not module
0
 

Author Comment

by:qeng
ID: 40598116
The above compile error is thrown even when the function is working in the worksheet.
0
 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept earned 250 total points
ID: 40598151
Ok, as per Dany's suggestion (and the way I tested it) I'd suggest that you don't name functions the same as modules.
It is important to overcome any compile issues even if some of the code appears to be working, because sometimes you have to go through a few iterations of fixing one issue that it reports to get to the next. Only when the project compiles successfully can you be sure that RUNTIME errors in code will be reported correctly.
0
 
LVL 11

Assisted Solution

by:Dany Balian
Dany Balian earned 250 total points
ID: 40598551
ok, you don't need to create one module per function.. so just create a module and name it calculations and put everything related  to calculations, another module called accounting ... this will make your code organized..

as you said, weirdly enough i don't know why it's working in the first case.. maybe there's a difference in the case.

because normally when you type any name in the code example: abcdef the compiler first searches for any objects named abcdef then for functions and so on...

so if u have a function named abcdef in a module named abcdef the real name of the function is abcdef.abcdef and not simply abcdef (i hope you get what i mean)
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:qeng
ID: 40602853
Simon/Dany,

I do get what you guys are pointing out.  For the record, the case of the function and module names (upper/lower case) was identical in function and module in all instances, e.g. when it would run with SMHST/SMHST and not run with MSHST/MSHST.

The part I don't get is why, with a function named differently from a module, I get the above noted compile error (it doesn't flag any offending line of code).

I'll try doing a bit of debugging to see if I can isolate the statement causing the compile error.

Is the a 'compile one line at a time' debug feature? (I believe there is a step-through feature but my guess is that is a run-time feature).
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40603353
Debug line-by-line (step-thru) is a run-time feature. The compile checks the hierarchy one line at a time and stops at the first error it finds (so you need to fix one to see the next one).

>The part I don't get is why, with a function named differently from a module, I get the above noted compile error (it doesn't flag any offending line of code).
If your module is still named the same as the function call on the spreadsheet formula
= MSHST()
You will get that error.

Safest to rename the modules to something completely different and try compiling again.
0
 

Author Comment

by:qeng
ID: 40610013
My modules are named something completely different from my function names now, but I get a compile error, even though the code seems to run 'ok' in run-time mode.
0
 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept earned 250 total points
ID: 40610109
Hi, depending on how much work you've done in that workbook it may be best to start again in a new workbook with a new VB project in it, and copy and paste your latest code across to it.

Is the compile error still this one?
>Expected variable or procedure, not module

This is the MSDN link for that error. I'm wondering if you have a worksheet cell formula somewhere in the workbook that is using one of the functions.
0
 

Author Comment

by:qeng
ID: 40610125
Hi Simon,

There is a ton (weeks :) ) of work into that workbook but so be ...

Yes the compile error is "Expected variable or procedure, not module".  But the error was being thrown in the different subroutine than the above functions we've been discussing (I just noticed it; perhaps because I'd fixed the prior issue by using different module and function names).

If I go by the block of code which highlighted after the Compile Error is thrown, the error seems to be with the statement which starts with "Message ...." (highlighted below).

In this case, my Module name was 'Message' and it seemed to be conflicting with the undeclared variable 'Message' which was in my subroutine.

Changing the name of my Module to 'aMessage' allows the Compile action to proceed without error (I'm not sure what to expect when I compile, I select Debug > Compile [Name of my VBA Project], and nothing happens, which perhaps means it compiled OK, though I would have assumed that Excel would have presented some sort of compile completion message or an option to save the compiled code or something).

I'm thinking that perhaps declaring my variable 'Message' within my Sub below might have also fixed the error even if the Module Name was left to be the same as the declared variable name.  I'm going to try that next just to check it out.


Sub DisplayMessage()
' displays a message
'
MsgBox ("Hello World.")
End Sub

Sub DisplayAnotherMessage()
' displays a message using a variable
'
Message = "This is a message I displayed"
MsgBox (Message)
End Sub
0
 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept earned 250 total points
ID: 40610661
I'm not sure what to expect when I compile, I select Debug > Compile [Name of my VBA Project], and nothing happens, which perhaps means it compiled OK

You're right - if nothing happens, the compile was successful. The way to check is to look at the Debug menu again immediately afterwards. I notice that on Windows versions, the Compile option will be disabled until you make further changes to your code, whereas in Excel 2011 on Mac the Compile option is never disabled.

I'm glad you've now got your functions working and would just suggest that you organise all similar subs/functions in one module. I usually name my modules with 'mod' prefix (e.g. modTextFunctions, modGeneral, modFileSystem).

Another good practice is to use OPTION EXPLICIT as the first line of each module, which forces you to declare all the variables used in the module.

I think we've resolved your question.
0
 
LVL 11

Assisted Solution

by:Dany Balian
Dany Balian earned 250 total points
ID: 40610691
when you compile, the compiler just checks if you have a function named abcdef and takes for instance one argument.. if that is correct, then the compile succeeds!
however, during runtime, whenever abcdef is called, the compiler searches in the stack for anything named abcdef and then tries to see its nature, arguments...
that's why, it's giving you the error on same cases.. (again weirldy enough, it should happen nomatter what the name)

again my advise to you, organize your code, it will help with the debugging process.. also as simon noted.. always use Option Explicit directive on the top of all VB code modules.. that way, you will catch any typos, or missing undeclared variables.

cheers,
dan
0
 

Author Comment

by:qeng
ID: 40610701
Great stuff Simon and Dan.

I learned a bunch on this one.  Thx for clarifying the lack of feedback from the Compile process.
0
 

Author Closing Comment

by:qeng
ID: 40610721
First rate guys.  Thx for the help and coaching.  Root of the problem was having a Function with the same name as a Module (though why it works in some cases and not in others remained a mystery ... wondering if it had to do with the order in which they were created; not important for me to find out at this point since the practice is perilous).

A related problem which was throwing the compile error in a different procedure (in the same Workbook) was to have one of my undeclared variables with the same name as one of my Modules.

I split the points;  both of you were on track (as usual!).  Hope you guys are ok with that.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

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

23 Experts available now in Live!

Get 1:1 Help Now