Assignment to constant not permitted

G

Grace

I just found an old sample I was trying to adapt for a message box. But, in my new spreadsheet, the macro crashes when I try to run it. It stops on the Default command and says "assignment to constant not permitted". What am I forgetting? Here is the macro:

Message = "ENTER LONG DISPLAY NAME OF WRAP MANAGER"
Title = ""
Default = ""
MGR_LONG_NAME = InputBox(Message, Title, Default)
Sheets("INPUTS").Range("C11").Value = MGR_LONG_NAME

Thanks,
Grace
 
N

Norman Jones

Hi Grace,

Change the line: Default = ""
to: strDefault = ""
as this is a VBA reserved name.

It is also highly advisable to head your module with : Option Explicit

and appropriately dim your variables.

If you were to do this, your code might look like this:

Sub Test()
Dim strMessage As String, StrTitle As String, _
StrDefault As String, StrMGR_LONG_NAME As String

strMessage = "ENTER LONG DISPLAY NAME OF WRAP MANAGER"
StrTitle = ""
StrDefault = ""
StrMGR_LONG_NAME = InputBox(strMessage, StrTitle, StrDefault)
Sheets("INPUTS").Range("C11").Value = StrMGR_LONG_NAME

End Sub

---
Regards,
Norman


I just found an old sample I was trying to adapt for a message box. But, in
my new spreadsheet, the macro crashes when I try to run it. It stops on the
Default command and says "assignment to constant not permitted". What am I
forgetting? Here is the macro:

Message = "ENTER LONG DISPLAY NAME OF WRAP MANAGER"
Title = ""
Default = ""
MGR_LONG_NAME = InputBox(Message, Title, Default)
Sheets("INPUTS").Range("C11").Value = MGR_LONG_NAME

Thanks,
Grace
 
G

Grace

I'm not sure what you mean by head your module. I do not see it in your
sample code. Should it be in there?

Thanks,
Grace
 
F

Frank Kabel

Hi Grace
he meant that you may insert the line
Option Explicit
as the first line in your module. This will force the VBA compiler to
check if all variables are defined. Good way to prevent typos in your
variable names.
 
G

Grace

Sorry for being thick. What does head of a module mean? Are you saying I
should just put it at the very beginning of each subroutine, i.e., before
dimensioning? I just tried it and immediately got a "compile error ;
invalid inside procedure", which probably means I put it in the wrong place.
Please be more specific with some of the more technical terms.

Thanks
Grace
 
D

Doug Glancy

Grace,

You only do it once per module.

A module contains one or more subroutines. Modules include the ThisWorkbook
module and the modules for each of the sheets in the workbook. You can also
create a module. To see the process of creating a module, first make sure
the Project Explorer is opened. Open Project Explorer from the View menu in
the Visual Basic Editor (VBE). Then create a module by choosing Module
from the Insert menu. There will now be a Modules folder in the Project
Explorer, with the module you just created and maybe some others. You
should be looking at the blank code window for the module, a lot of white
space. Type Option Explicit at the top of this space and then don't type it
again in that module. You will now get compile errors for any undimensioned
or misspelled variables.

To have each module automatically be created with Option Explicit at the
top, from the VBE Tools menu choose Options and then on the Editor tab
choose "Require Variable Declaration."

hth,

Doug Glancy
 
G

Grace

I do see that it does seem to break things up into modules, though not sure
why it groups certain macros together. I guess it goes at the very top of
the page, before any subroutines.

Thanks. This will surely help typos and dimensioning.

Grace
 
F

Frank Kabel

Hi
not before each subroutine but just at the very top of your module (the
first line). See the VBA help for this keyword
 
G

Grace

I assume the strDefault and strTitle is for strings and that there are
analogs for things dimensioned as other types. What do you use for
workbook, long, range, date, etc?

Thanks,
Grace
 
N

Norman Jones

Hi Grace,

The choice of variable names is primarily a matter of choice and personal
preferance.

For me, the main considerations are convenience and readability - readabilty
by me and (possibly) by others.

I (non-uniquely!) tend to prefix a string variable with str as this
immediately informs me (as indeed it informed you!) of the variable type,
thus rendering my code more immediately comprehensible.
As for range variables I tend,inconsistently, to use a rng prefix or
suffix, e.g, Rng, Rng1 or myRng bigRng, copyRng, DestRng etc accoding to
seems most immediately descriptive.
I could continue in similar fashion for other variable types but more
informative (and useful) would be to direct you to look at the approaches of
others who regularly contribute to the Excel newsgroups. In particular,
however, I would direct your attention to Chip Pearson's web
site:http://www.cpearson.com/excel/topic.htm
which is not only replete with useful and informative code but which uses
an idiosyncratic variable naming style which is (imho) a model of clarity.
 
G

Grace

I'm a bit confused. Originally, you said:

Change the line: Default = ""
to: strDefault = ""
as this is a VBA reserved name.

I thought this was because I was having macro crashing problems. But did
that have nothing to do with my macro crashing? And, are you saying, it
shouldn't be just "default", but other than that, it could be anything at
all, like =InputBox(Joe, Harry, Jerry)?

Thanks,
Grace
 
N

Norman Jones

Hi Grace,

We are back at the start!

Originally, you did not dim your variables (or use Option explicit at the
top of your module). In these circumstances, VBA objected to the Default
variable because this is a reserved word having a special significance in
VBA.

Had you, however, dimmed you the 'Default' variable, VBA would not have have
thrown up the 'assignment to constant is not permitted error message.

Nonetheless, I repeat my original advice: use Option Explicit, dim all your
variables and avoid reserved words!
 
G

Grace

Got it!
Thx

Norman Jones said:
Hi Grace,

We are back at the start!

Originally, you did not dim your variables (or use Option explicit at the
top of your module). In these circumstances, VBA objected to the Default
variable because this is a reserved word having a special significance in
VBA.

Had you, however, dimmed you the 'Default' variable, VBA would not have have
thrown up the 'assignment to constant is not permitted error message.

Nonetheless, I repeat my original advice: use Option Explicit, dim all your
variables and avoid reserved words!
Click to expand...
 
Top