[ExcelVBA] Re: Case Without Case Select

 

--- In ExcelVBA@yahoogroups.com, Derek Turner <g4swy@...> wrote:
>
> Dear AJ
>
> I am sorry my previous post was unclear.
>
> The following piece of code might help the understanding of this point :-
>
> Sub Test()
> Dim sVbYes As String
> sVbYes = "6"
> Debug.Print TypeName(vbYes), TypeName(sVbYes)
> Debug.Print vbYes = "6", vbYes = sVbYes
> End Sub
>
> The debug window will look like this :-
> Long          String
> True          True
>
> There is no Type Mismatch error even though vbYes is a Long and sVbYes is a String.
>
> Notice that vbYes is a built-in VB predefined constant. 
> It is bad practice to use magic numbers like 6 in your code when there is an equivalent built-in constant which has a meaningful and self documenting name.
>
> Which of these makes more sense ? :-
> If Response = 6 then ....
> If Response = vbYes Then ....
>
> Regards
>
> Derek Turner +++
>
>
>
> >________________________________
> > From: A.J. Morales <ajmorales@...>
> >To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
> >Sent: Sunday, 8 April 2012, 3:35
> >Subject: Re: [ExcelVBA] Case Without Case Select
> >
> >
> > 
> >the return integer value for the messagebox is;
> >6=yes
> >7=no
> >
> >AJ
> >
> >________________________________
> >From: Derek Turner <g4swy@...>
> >To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
> >Sent: Friday, April 6, 2012 4:48 PM
> >Subject: Re: [ExcelVBA] Case Without Case Select
> >
> >
> > 
> >Hi Tariq
> >
> >You did not specify what errors you are getting but there is an unmatched With in the "Case vbNo" code block.
> >
> >It might help you to understand your code better if you try to rationalize the repeated/identical code blocks. I suspect that this repetition caused the typo. If you repeat blocks of code you have to debug them individually as many times as you have repeated them.
> >
> >David is quite right that your variables to hold the MsgBox results should be declared as Integer. 
> >However both of the following evaluate to True in VB :-
> >1=1
> >1="1"
> >The latter in other languages would give a type mismatch exception. 
> >Your If and Case comparisons actually work as you expected in spite of the mismatch.
> >
> >Regards
> >
> >Derek Turner
> >
> >+++
> >
> >>________________________________
> >> From: David Smart <smartware.consulting@...>
> >>To: ExcelVBA@yahoogroups.com
> >>Sent: Friday, 6 April 2012, 13:23
> >>Subject: Re: [ExcelVBA] Case Without Case Select
> >>
> >>
> >> 
> >>You are putting the results of the two MsgBox calls into variables called
> >>Answers and Answer.
> >>
> >>However, you have defined these variables as String.
> >>
> >>MsgBox returns an integer. Your variables should be defined as integer.
> >>
> >>It seems to work OK for the IF statement, where it is presumably converting
> >>vbYes to a string and then comparing it to Answers.
> >>
> >>However, a Case statement is very different, and I suspect that it isn't
> >>doing a conversion. Hence, none of the cases match. As you don't have a
> >>default case to report an error, absolutely nothing seems to happen.
> >>
> >>Change your variables to integer and see if it helps.
> >>
> >>Regards, Dave S
> >>
> >>----- Original Message -----
> >>From: "tariq_rahmalan" <tariq_rahmalan@...>
> >>To: <ExcelVBA@yahoogroups.com>
> >>Sent: Friday, April 06, 2012 10:59 AM
> >>Subject: [ExcelVBA] Case Without Case Select
> >>
> >>> Hi there. I'm new in VBA. Please give me some guidance.
> >>>
> >>> I facing this problem. Can anyone spot where the error are? Much
> >>> appreciated.
> >>>
> >>> Private Sub CommandButton7_Click()
> >>> Dim Answers As String
> >>> Dim Mynote As String
> >>> Dim Answer As String
> >>> Dim lRow As Long
> >>> Set ws = Worksheets("Rekod")
> >>>
> >>>
> >>> 'Place your text here
> >>> Mynote = "Adakah anda telah klik butang 'Tambah Rekod'?"
> >>>
> >>> 'Display Message Box
> >>> Answers = MsgBox(Mynote, vbQuestion + vbYesNo, "Anda Pasti?")
> >>>
> >>> If Answers = vbYes Then
> >>>
> >>> Answer = MsgBox("Adakah anda ingin save file ini?",
> >>> vbQuestion + vbYesNoCancel, "Save File?")
> >>>
> >>> Select Case Answer
> >>>
> >>> Case vbYes
> >>>
> >>> 'find the last row
> >>> lRow = ws.Cells(Rows.Count, 1) _
> >>> .End(xlUp).Row
> >>>
> >>> 'Copy data to the database
> >>> With ws
> >>> .Cells(lRow + 1, 1).Value = .Cells(lRow,
> >>> 1).Value
> >>> .Cells(lRow + 1, 2).Value = .Cells(lRow,
> >>> 2).Value
> >>> End With
> >>>
> >>> 'save the file
> >>> ThisWorkbook.Save
> >>>
> >>> 'code to proceed to next form
> >>> Unload Me
> >>> Rekod.Show
> >>>
> >>> Exit Sub
> >>>
> >>> Case vbNo 'not save the file
> >>>
> >>> 'find the last row
> >>> lRow = ws.Cells(Rows.Count, 1) _
> >>> .End(xlUp).Row
> >>>
> >>> 'Copy data to the database
> >>> With ws
> >>> .Cells(lRow + 1, 1).Value = .Cells(lRow,
> >>> 1).Value
> >>> .Cells(lRow + 1, 2).Value = .Cells(lRow,
> >>> 2).Value
> >>>
> >>> 'code to proceed to next form
> >>> Unload Me
> >>> Rekod.Show
> >>>
> >>> Exit Sub
> >>>
> >>> Case vbCancel
> >>> Unload Me
> >>> Rekod.Show
> >>> Exit Sub
> >>>
> >>> End Select
> >>>
> >>>
> >>> Else
> >>> 'code for click back butang Tambah rekod
> >>> Exit Sub
> >>> End If
> >>>
> >>>
> >>> End Sub
> >>>
> >>>
> >>>
> >>> ------------------------------------
> >>>
> >>> ----------------------------------
> >>> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> >>> here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> >>> the ARCHIVES for EXCEL VBA.
> >>>
> >>> ----------------------------------
> >>> Visit our ExcelVBA group home page for more info and support files:
> >>> http://groups.yahoo.com/group/ExcelVBA
> >>>
> >>> ----------------------------------
> >>> More free tutorials and resources available at:
> >>> http://www.mousetrax.com
> >>>
> >>> ----------------------------------Yahoo! Groups Links
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> -----
> >>> No virus found in this message.
> >>> Checked by AVG - www.avg.com
> >>> Version: 10.0.1424 / Virus Database: 2113/4917 - Release Date: 04/05/12
> >>>
> >>
> >>
> >>
> >>
> >>
> >
> >[Non-text portions of this message have been removed]
> >
> >[Non-text portions of this message have been removed]
> >
> >
> >
> >
> >
>
> [Non-text portions of this message have been removed]
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (8)

Recent Activity:

• New Members 6

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___