Excel 2007 - Decyphering Code - Please explain 1

[ladyck3]

Hi, its me again
I'm using John Walkenbach's book "Excel 2007 VBA Programming for Dummies" as was recommended. Well the non 2007 version was recommended and I already had the book but since I"m using 2007 and somehow its supposed to be a lot different (except the VBE is the same) there are new commands or something.

I just typed my first code and it should throw up a small dialog box asking me if my name is (my name) and yes or no.

I get a compile error "Variable not defined"
Then the Sub GuessName() line turns yellow and the cursor location is over the first "MSG"

Have I got an error in my typing, or how can I tell what variable is wrong? I checked to see and my name is in the Help > About area showing I'm the user... I'm very confused at this point as to why this is not working.

Option Explicit

Sub GuessName()
    Msg = "Is your name " & Application.UserName & "?"
    Ans = MsgBox(Msg, vbYesNo)
    If Ans = vbNo Then MsgBox "Oh, never mind."
    If Ans = vbYes Then MsgBox "I must be clairvoyant!"
    
End Sub

I'm trying here but unless I understand why this is not working I'm stuck... help me get a handle on this please.

Thanks

Laurie




LadyCK3
aka: Laurie

 

[SkipVought]

Hi,

Option Explicit is good to have.

But it means that every single variable needs to be declared.

Variables can be declared at the MODULE level at the PROCEDURE level. I'd assume the latter in your case.

So you must declare the Msg & Ans variables.

Option Explicit

Sub GuessName()
   Dim Msg as string, Ans as variant

    Msg = "Is your name " & Application.UserName & "?"
    Ans = MsgBox(Msg, vbYesNo)
    If Ans = vbNo Then MsgBox "Oh, never mind."
    If Ans = vbYes Then MsgBox "I must be clairvoyant!"
    
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

A followup - I found out what was broken.
When I opened the VB Window to write the code the OPTION EXPLICIT tag was added automatically.

I removed it and it did not change the functionality. I had a coworker look at it (when I wrote this post he was unavailable and I was not sure he would be later), he knows SQL and other languages but not VBA, he did the same thing but backspaced the empty lines out of there so Sub GuessName() was at the top of the window and then it worked.



Baby Steps

Laurie


LadyCK3
aka: Laurie

 

[SkipVought]

Lorie,

Removing this statement is like shooting up with pain killer.

Pain is an indication of a problem.

DON’T REMOVE Option Explicit!

Skip


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mscallisto]

I agree strongly with Skip

Option Explicit essentially says:

"keep me from being a sloppy programmer...make sure I define all variables the way I want them to be defined"

Without Option Explicit, undefined variables may be given a default type that you may not want or even be aware of until something doesn't work.

sam

 

[ladyck3]

Oh goodness, fair enough... its a bandage but not a cure. It doesn't fix anything only masks a definite error.

Thanks for the tip. I'm understanding that Option Explicit means you must be an accurate code and it offers an optimum check of your work.. keeps you honest. I like that

I know everyone does things different and no one way is "THE" right way, as there are 50 ways to do one thing depending on preference... but since this book is written specifically for the newness of 2007 I wonder why his version worked for him but not for me.

I'm going to have to write him an email and see what's up

Not being picky but heck if this is my resource to learn and that's the reason for the book, if there is something which a beginner does not understand or cannot get to work right out of the shoot, .. if I was the author, I'd want to know... don't you think?

I will be nice

I'd want to know, that's for sure

Thanks folks!
Laurie


LadyCK3
aka: Laurie

 

[mscallisto]

His version worked because he probably didn't have Option Explicit and the variables were given default types of "Variant" which was sufficient for this simple program to work but can cause problems for other programs.

In Skip's post he added:

Dim Msg as string, Ans as variant

which is correct for this particular example.

You need to study variable types and their usage, use option explicit and choose the proper variable type for each program you author.

BTW there is a setting where you can turn on/off the option to always use option explicit, I'll let you explore as to where it is.

Good Luck and have fun

sam

 

[ladyck3]

Thanks Sam... that's under Tools > Options > Editor tab and a check in Require Variable Declaration... RIGHT?

See...not as dumb as I look, know enough to understand what the code is doing but dangerous enough not to know why things are told what to do and when to do them.

I don't know the language or the syntax or that the things in a code are named what they are named because you can name it whatever just make sure that 'whatever' is properly called and how to call it.... make sense?

Well I'm learning from step one and by hook or by crook I "AM" going to get this.... I"m an old woman and the disk is full but I'm going to delete some files and fit 'r in

I swear I want to know this SO BAD! I walk around daily like the disk is formatted... my memory is short but I'm goign to do my level best to get this... I mean GET THIS!

Thanks everyone for your patience and hand holding...

So why DIM? What does DIM mean? WHY are we using it? that was part of what confused me on the older posts of a bazillion responses

Please help me to understand that. I understand the line

DIM Msg as string, Ans as variant

We're telling the following code that hey... when you see Msg, that's the STRING we are concerned about and the ANS is a variant, there is more than one possible response (in this case yes or no). But why DIM? What does dim do? Or what does it stand for? OR what is its function?

How should I properly ask this question? What is the proper question I'm asking here? (See my confusion) <whoosh whoosh whoosh> <---- notta variant, its a true STRING ::dizzy:: hehehehehe




LadyCK3
aka: Laurie

 

[SkipVought]

Laurie,

One of the first things that the program (code compiler) does is allocate memory space for each variable. If the variable is not declared in a DIM statement, the compiler searches thru the program, assembling all the undeclared variables and assumes them to be Variant. Check VBA Help on Variant Data Type. Makes the program less efficient.

Bottom line: it is best and accepted codeing practice to declare all variables with appropriate data types, rather then letting the compiler make assumptions about your data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mscallisto]

Laurie

Again Skip gives sound advice and by the way I believe you have the right stuff to "GET THIS".

The only way you can successfully get good at programming is practice, learning from mistakes and asking questions.

Many programmers are quite sloppy in variable definitions while others use a variety of "good practice" rules like naming variables with identifiers e.g.
strMsg
varAns

This can come in handy when doing a comparison between variables (if strDay = "Sat") where both variables are expected to be the same type to get the correct results.

DIM is an old programming term probably from a language called FORTRAN (from FORmula TRANslation) way back in the 1950's and was used to declares the size of an array (define it's DIMension) and was also used in many other languages like Basic and Pascal to name a few.

Again Good Luck

sam

 

[combo]

Not very important, but somtimes this can be useful:
If you look at the MsgBox description in the object browser you can see, that it is a method (function) that returns one of values of enumerated VbMsgBoxResult. So it is possible to declare:
[tt]Dim ans As vbMsgBoxResult[/tt]
As an advantage you can see that after If Ans = the vba intellisense will display available constants to choose.

combo

 

[ladyck3]

OOOooo Combo, Sam, Skip... all great info and ya know what? I think I get it

I appreciate the help.... gonna go read and practice some more.. after I fix my Option Expilcit statement and and the DIM line

I was at "end of the day" yesterday when I wrote

Thankeeee

LadyCK3
aka: Laurie

 

[mscallisto]

Nice point combo

 

[ladyck3]

I'm back .... caught up I'm back at the book again and I've run into the very same thing. I applied the variant thing like previously but I"m sure I did some improper naming. This is what we were asked to enter

Sub CubeRoot()
Num = InputBox(“Enter a positive number”)
MsgBox Num ^ (1/3) & “ is the cube root.”
End Sub

Of course because the book suggests (as well as the group here) the options are set such that, when I open the window to enter the code "Option Explicit" automatically populates the page. GOOD THING...

SO I enter what is instructed and then we are attempting to excecute and of course as I suspected I got an error message about the variant being unexpected. I figured as much from the previous test.

I guess my question is, if you are going to recommend this setting then why don't you include this as part of the equation when providing instructions, right? That's to the author.

SO I took the information from the last encounter and because it was text before, I figure I"m going to get it wrong, because this is number input but I added a line as follows (I know its broken, and figured so)

Sub CubeRoot()
    Dim Num As String, MsgBox As Variant
    
    Num = InputBox("Enter a positive number")
    MsgBox Num ^ (1 / 3) & " is the cube root."
End Sub

I'm assuming a number cannot be a string so the first part would be incorrect and MsgBox is probably not a variant. so how to I correct this? I'm asking so I learn how to handle the different data types.

Thanks!


LadyCK3
aka: Laurie

 

[Gavona]

If I type in the VBE
Dim num as (with a space after the "as"
A list of options I can select from comes up. Scroll down to find a suitable one.

If I type Dim in the "Type a question for help" area in the top right of the VBE I get some help which is more than enough to answer your question.

Now I only use 2003 but surely 2007 is similar?

Gavin

 

[SkipVought]

You either have to declare Num as String or Variant, because the value returned from the InputBox is a STRING. So you cannot assign a STRING to an NUMERIC variable and since an Input Box returns TEXT, the variable assigned, must be able to recieve TEXT.

Once recieved, when you do an arithmetic operation using that variable, the text value is converted (by coersion) to a NUMBER in order to evaluate the expression. So perhaps a more correct way of coding this might be...

Sub CubeRoot()
    Dim Num As String
    
    Num = InputBox("Enter a positive number")
    
    If IsNumeric(Num) Then
        If Num > 0 Then
            MsgBox CDbl(Num) ^ (1 / 3) & " is the cube root."
        Else
            MsgBox "Hey, Dummy, you failed to enter a POSITIVE number!"
        End If
    Else
        MsgBox "Hey, Dummy, you failed to enter a number!"
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

Laurie, here is another suggestion.

Do not use built-in terms for variables.

MsgBox is an internal thing in VBA. Do not use it as a variable.

Just to possibly confuse you more:

Sub CubeRoot()
MsgBox InputBox("Enter a positive number") ^ (1 / 3) & _
   " is the cube root."
End Sub

Notice there are NO variables declared here. It does not need any. Try it, it works. VBA displays the InputBox first, and gets the return value (by default a string). IF - and only if - the string can be converted to a number, VBA will happily accept a string (and internally convert it) and use it in the rest of the instruction. Once that processes, that value is appended to the string " is the cube root.", and passed to the MsgBox function...and displayed.

This does the exact same thing WITH a variable.

Sub CubeRoot2()
Dim Num As String
    Num = InputBox("Enter a positive number")
    MsgBox Num ^ (1 / 3) & " is the cube root."
End Sub

It does work, but if Option Explicit is set (and please DO this!!), then Num must be declared. If it is declared, then it works, and you do not need the Variant.

But a variable - in this case - is NOT needed.

I'm assuming a number cannot be a string

Ummm, true. A number can not be a string...but a string may be a number. As stated, IF - and only if - a string is passed to a function that requires a number, and that string can be converted TO a number, then VBA is polite enough to do so. For example, the Sqr function (a built-in VBA function) returns the square root of a number. That number, technically speaking, is required to be a Double. But let us pass it a string.

Sub tryMe()
Dim strWhatever As String
strWhatever = InputBox("a number please")
MsgBox Sqr(strWhatever)
End Sub

As long as the returned value of the InputBox can be considered a number, it will be. Obviously if you entered:


25blah

there would be the dreaded Type 13 error, as "25blah" can NOT be converted into a number. It is a string, and what VBA is looking for is a number, and it can NOT convert it.

"25" can be. Just to reiterate though, the above does NOT need any variables.

Sub TryMeAgain()
    MsgBox Sqr(InputBox("a number please")) & _
         " is the square root."
End Sub

It may seem difficult and confusing now, but please do persist. It will get better. Keep asking questions here. DO use Option Explicit! It will save you a lot of pain in the long term.


Hmmmm. I am wondering if I should explain why you do not a variable for those messageboxes.

MsgBox Sqr(InputBox("a number please")) & " is the square root."

Let me parse it out, the way VBA does.

It is ONE instruction, with multiple parts. What VBA parses is:

Display the string (MsgBox) made up of
the square root (Sqr) of
the returned value of the InputBox (InputBox)
plus the string " is the square root."


As you can see, phrased like this, NO variable is actually needed.

TECHNICALLY, VBA does not parse it out as I wrote there. It does it like this. It is because of the order VBA considers important.

Get the value of InputBox (inside the inner most brackets)
Get the value of Sqr (using the value above)
Append THAT with " is the square root."
Display the full result (MsgBox)

Can you follow that no variable is needed? Each step, each value stands on its own.

Gerry

 

[ladyck3]

Why did the error message state there was no variant then?

I completely understand the relevance of using OPTION EXPLICIT. If for no other reason than to keep one's code honest. If you have it REQUIRE that it be written 'unsloppily' then its a good thing.

I'm just preplexed as to why the book is written in such a way as to NOT include it and me being a dummy (yet, thus the book for DUMMIES) would not include it but have you write code that, if using it because its the best way to go, causes the code to fail all the time, I just don't get it.

He goes over the settings and states it SHOULD be used but when introducing code, he fails to utilize it. You can't answer for the author and I'm not asking you to but how can I follow along if the very text I'm adopting as my training manual is kind of... well its not proper to the point of writing a script that works with the proper features.

Know what I mean? I suppose I should be writing to the author but.... well I'm too new at it to send a formal complaint... when I get further in and if it continues to be such a stumbling block ... as a newbie... I will explain how this is very confusing.

Thanks so much for the explanations. Gosh Skip, that simply 2 lines of code ends up to be so much more because of this one OPTION EXPLICIT thing.... and

fumei, thanks for the time you spent in responding as well, that too is rattling around in my head and making sense. And I totally get the concept that it depends on the writer of the code as to how dotting and "I" can end up with a bazillion different code clippets to do the same thing... its all in how one is in a habit of writing code. Example... the differences between the Author, Skip and your code... nuff said on that.


Gavona, thanks for the tip on using the Help file in the VBE, I know that it is there and I did use it but I'm SO TOO NEW that I don't know what I'm looking at and don't know what to look for.. I'm still understanding that A is for APPLICATION and B is for (something else) hehehehe whatever B stands for in this context

Ok, have work to do, see you later today I'm sure




LadyCK3
aka: Laurie

 

[SkipVought]

that simply 2 lines of code ends up to be so much more because of this one OPTION EXPLICIT thing

The 'OPTION EXPLICIT thing' is NOT the reason for the 'so much more.'

The 'OPTION EXPLICIT thing' just notifies your when you have not declared a variable.

The 'so much more' is the kind of LOGIC that ought to be used to keep your code from OTHER errors that come when users enter bad data. Take your 2 lines of code and run a couple of tests:
[tt]
Test 1: Enter a NEGATIVE NUMBER

Test 2: Enter a STRING
[/tt]
The reason for the 'so much more' is that users do not always follow your expectations. Good programming anticipates the errors that the user might cause and TRAPS them in some way, so the the program can continue without error under each of these ABNORMAL conditions.

The CDbl function to convert the NUMERIC STRING to a NUMBER, VB does for you, if you forget to do it.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}