Getting a message box program to run (Page 43 of 'BeginningProgramming with VBA' by Duane Birnham)

[musicalhacksaw]

Dear Programmers,

I seem to be running into difficulty running the following program in Excel.

I have made a Command Button and called it cmdBegin and given its caption as Begin in the properties window.

However upon pressing the button in the Excel Spreadsheet, it returns as an error (a yellow arrow) that the first line:

Private Sub cmd Begin_click()

is not defined.

I can't get the Book's website as it seems the UK branch is defunct, though they tout their website on the back cover and in the text a lot, they don't actually specify what or where it is, and how it might be accessed.

Anyhow I thought I might try Googlegroups for help.

This is the text of the program in question:-
----------------------------------------------------------------------------
Private Sub cmdBegin_Click( )
Dim userName As String
Dim firstName As String
Dim lastName As String
Dim strLength As Integer
Dim spaceLoc As Integer
'----------------------------
'Collect user name, find the space between
'first and last names, and separate the names.
'------------------------------------------------
userName = InputBox("Enter your first and last name.", "Name")
spaceLoc = InStr(1,userName," ")
firstName = Left(userName, spaceLoc - 1)
'-----------------------------------------------
'Output to the worksheet
'-----------------------------------------------
Range("C3").Value = firstName
strLength = Len(firstName)
Range("C4").Value = strLength 'length of first name
strLength = Len(userName)
lastName = Mid(userName, spacelLoc + 1, strLength - spaceLoc)
Range("C5").Value = lastName
strLength = Len(lastName)
Range("C6").Value = strLength
Range("C7").Value = UCase(userName)
Range("C8").Value = LCase(userName)
Range("C9").Value = StrConv(userName, vbProperCase)
Range("C10").Value = StrReverse(userName)
Range("C11").Value = lastName & ", " & firstName
End Sub

 

[Claus Busch]

Hi Simon,

Am Sun, 9 Nov 2014 07:06:08 -0800 (PST) schrieb
(e-mail address removed):

I seem to be running into difficulty running the following program in Excel.

I have made a Command Button and called it cmdBegin and given its caption as Begin in the properties window.

However upon pressing the button in the Excel Spreadsheet, it returns as an error (a yellow arrow) that the first line:

Private Sub cmd Begin_click()

there is a typo into the code.
Try:

Private Sub cmdBegin_Click()
Dim userName As String
Dim firstName As String
Dim lastName As String
Dim strLength As Integer
Dim spaceLoc As Integer
'----------------------------
'Collect user name, find the space between
'first and last names, and separate the names.
'------------------------------------------------
userName = InputBox("Enter your first and last name", "Name")
spaceLoc = InStr(1, userName, " ")
firstName = Left(userName, spaceLoc - 1)
'-----------------------------------------------
'Output to the worksheet
'-----------------------------------------------
Range("C3").Value = firstName
strLength = Len(firstName)
Range("C4").Value = strLength 'length of first name
strLength = Len(userName)
lastName = Mid(userName, spaceLoc + 1, strLength - spaceLoc)
Range("C5").Value = lastName
strLength = Len(lastName)
Range("C6").Value = strLength
Range("C7").Value = UCase(userName)
Range("C8").Value = LCase(userName)
Range("C9").Value = StrConv(userName, vbProperCase)
Range("C10").Value = StrReverse(userName)
Range("C11").Value = lastName & ", " & firstName
End Sub




Regards
Claus B.

 

[musicalhacksaw]

Dear Claus,
Thank you for your reply.
I cannot find where the typo you speak of, is.
I did not have the 'Click()' in the first line:

Private Sub cmd Begin_click()

in lower case, I had it in upper case in my program, ie:

Private Sub cmd Begin_click()

All the same I copied and pasted the code you gave into my Excell VBasic coding page, but upon pressing the button 'Begin' in the Excell Spreadsheet page, nothing happened.

Could you tell me exactly what the typo you refer to was ?

 

[Claus Busch]

Hi Simon,

Am Sun, 9 Nov 2014 08:46:38 -0800 (PST) schrieb
(e-mail address removed):

Could you tell me exactly what the typo you refer to was ?

lastName = Mid(userName, spacelLoc + 1, strLength - spaceLoc)
^^^^^^^
in the line above you have a l to much in spaceLoc


Regards
Claus B.

 

[musicalhacksaw]

Dear Claus Busch,
Thank you for your reply.
I do not see where the typo you refer to is.
I did copy and paste your code into my Visual Basic window, but it still doesn't run.
I would wish to say that I did not code the first line as:

Private Sub cmd Begin_click()

but did have it coded as:

Private Sub cmd Begin_Click()

although as said, the program still did not run.

 

[Claus Busch]

Hi Simon,

Am Sun, 9 Nov 2014 08:56:37 -0800 (PST) schrieb
(e-mail address removed):

Private Sub cmd Begin_click()

Private Sub cmdBegin_Click()

or look here:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "SimonTest"
You have to download the workbook because macros are disabled in
OneDrive



Regards
Claus B.

 

[musicalhacksaw]

re:-

lastName = Mid(userName, spacelLoc + 1, strLength - spaceLoc)

I changed the line of code above in 'Notepad' so there were no gaps, but putting it back into the Visual Basic window, it seems to automatically rearrange itself, with the gaps that it originally had.

 

[musicalhacksaw]

Hi Claus,
I have downloaded and run 'Simon Test' in Excel, it ran to some extent, butas you say, there is something wrong as it doesn't go through the entire program, and indeed it did pinpoint the line you suggested as having the error, with the yellow arrow, but I am not sure how to fix this, as when the spaces are gotten rid of, it still changes back to the original coding (see previous message).I think it is the author's fault (Duane Birnbaum, MichaelVine) for publishing shoddy code.
Thank you for your selfless help.
Yours Simon.

 

[musicalhacksaw]

Actually upon closer examination your 'Simon Test' works perfect - maybe you should be the one writing a book on Visual Basic instead of Mr Duane Birnbaum.
Thank you again.
Yours Simon

 

[Claus Busch]

Hi Simon,

Am Sun, 9 Nov 2014 09:23:46 -0800 (PST) schrieb
(e-mail address removed):

I have downloaded and run 'Simon Test' in Excel, it ran to some extent, but as you say, there is something wrong as it doesn't go through the entire program, and indeed it did pinpoint the line you suggested as having the error, with the yellow arrow, but I am not sure how to fix this, as when the spaces are gotten rid of, it still changes back to the original coding (see previous message).I think it is the author's fault (Duane Birnbaum, Michael Vine) for publishing shoddy code.

does my workbook run also into an error?
I tested it before I uploaded it and it worked fine.


Regards
Claus B.

 

[Claus Busch]

Hi Simon,

Am Sun, 9 Nov 2014 09:28:46 -0800 (PST) schrieb
(e-mail address removed):

Actually upon closer examination your 'Simon Test' works perfect - maybe you should be the one writing a book on Visual Basic instead of Mr Duane Birnbaum.

I prefer another way to write this code.
Please look again in OneDrive. There is the revised code.


Regards
Claus B.

 

[musicalhacksaw]

Hi Claus,
I finally found out what was wrong, it was my fault, not Mr Birnbaum's, in the line:

lastName = Mid(userName,spacelLoc +1,strLength - spaceLoc)
^
I had one too many 'L's', upon removing it, the program runs okay.
That was why the compiler kept pointing to that line.
Anyway, yes Simon(1) is a better program than the one in the book but I suppose the author wants to keep things simple at this early stage.
Again thank you for your help
Yours
Simon