VBA : already-declared string variable turns into integer

[Chris Davison]

Happy New Year all,

I have an already-declared string variable that is turning itself into an integer variable and causing me problems....

I'm not sure why this is happening.

The delete_exit variable has been declared as a string - I was expecting it to be "vbYes" or "vbNo", dependant upon the result of the user's reaction to various message boxes during the macro.

I added a line : msgbox(delete_exit) to show the value of this variable which alerted me to it suddenly becoming an integer, 6 or 7, during the macro.

Here's the code :

Sub delete_specific_contract()
    Dim delete_exit As String
    Dim cell As Range
    'clear old info
    Range("delete_number").ClearContents
    UF_warning.Show
    UF_delete_specific_contract.Show
    'exit if "none - exit" was selected
    If Range("delete_number").Value = "none" Then
        Exit Sub
    End If
    'check contract end date
    If Worksheets("c_" & Range("delete_number")).Range("F5").Value > Date Then
        delete_exit = "vbYes"
        Worksheets("c_" & Range("delete_number")).Select
        Worksheets("c_" & Range("delete_number")).Range("F5").Select
        delete_exit = MsgBox("Contract number " & Range("delete_number").Value & "'s finish date is in the future." & Chr(10) & Chr(10) & "Do you want to CANCEL deleting this contract ?", vbYesNo)
        If delete_exit = "vbYes" Then
            Exit Sub
        End If
    End If
    'check contract's details to see if any plant are not yet returned
    With Worksheets("c_" & Range("delete_number"))
        For Each cell In Range(.Range("F8"), .Range("F65536").End(xlUp))
            If IsEmpty(cell.Offset(0, 1)) = True Then
                delete_exit = "vbYes"
                Worksheets("c_" & Range("delete_number")).Select
                cell.Offset(0, 1).Select
                delete_exit = MsgBox("Contract number " & Range("delete_number").Value & " still has plant that looks like it has yet to be returned." & Chr(10) & Chr(10) & "Do you want to CANCEL deleting this contract ?", vbYesNo)
                If delete_exit = "vbYes" Then
                    Exit Sub
                End If
            End If
        Next cell
    End With
    'last chance confirm message
    With Worksheets("c_" & Range("delete_number"))
        delete_exit = "vbYes"
        Worksheets("c_" & Range("delete_number")).Select
        Worksheets("c_" & Range("delete_number")).Range("D5").Select
        delete_exit = MsgBox("Contract number " & Range("delete_number").Value & Chr(10) & Chr(10) & "Do you want to CANCEL deleting this contract ?", vbYesNo)

        'this is where it's changing

        MsgBox (delete_exit)


        If delete_exit = "vbYes" Then
            Exit Sub
        End If
        If delete_exit = "vbNo" Then
            Application.DisplayAlerts = False
            'delete the sheet
            .Delete
            Application.DisplayAlerts = True
            'delete the record from the contract database
            For Each cell In Range("contract_number_range")
                If cell.Value = Range("delete_number").Value Then
                    Range(cell, cell.Offset(0, 3)).Delete Shift:=xlUp
                End If
            Next cell
            MsgBox ("Contract number " & Range("delete_number").Value & " deleted")
        End If
    End With
End Sub

any ideas why the variable is changing from a string to an integer ?

cheers

Chris

 

[Richard Schollar]

Chris

I may be viewing this too simplistically, and hence missing something, but vbYes and vbNo are excel constants for int values 6 and 7 aren't they? Hence you will be getting a return of "6" and "7" won't you? Do you mean to say you are actually getting 6 and 7??

Best regards

Richard

PS: Happy New Year!!

 

[Chris Davison]

Evening Basingstoke !

no no, I think you're reading it okay.....

okie dokie - I popped the msgbox(delete_exit) line into the macro after each line where the user's answer was required and they all came back as 6 or 7 integers.

This is worrying - I'm sure I've always used "vbYes" or "vbNo" as return values in just about every other macro I've ever written to decide the next direction of the macro.

Should I therefore replace any "if delete_exit = "vbYes""s with "if delete_exit = 6" and replace any "if delete_exit = "vbNo""s with "if delete_exit = 7" ?

cheers
Chris

(had a management course in Basingstoke years and years ago - it pissed down all week and we ended up throwing eggs off the pier to see who could design an unbreakable egg with sellotape, 2 sheets of A4 paper and a balloon. hee hee. mine cracked.)

 

[Richard Schollar]

Chris

You could use vbYes or vbNo in place of 6 or 7 and then you do need to Dim your variable as an integer or long.

I am unsurprised to find it was raining when you were in B'Stoke: it is the dullest grey miserable sh*hole in Hampshire

Fortunately, I only work there though

Richard

 

[just_jon]

<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> StrVar <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
StrVar = vbYes
MsgBox StrVar = vbYes <SPAN style="color:#007F00">' will produce True</SPAN>
MsgBox StrVar = "vbYes"  <SPAN style="color:#007F00">' will produce False</SPAN></FONT>

 

[Chris Davison]

cheers guys,