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@rocketmail.com>
>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@yahoo.com>
>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@gmail.com>
>>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@yahoo.com>
>>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]
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
----------------------------------
Tidak ada komentar:
Posting Komentar