CODEWITHSUNDEEP
vbaexcel
Samuel Leanza
Samuel Leanza

Reputation: 63

error run-time '9' in split function in VBA

In my excel I have create the following combo box:

Private Sub Workbook_Open()
    With ActiveSheet.ComboBox1
        .AddItem "CHIAV<-->MACC"
        .AddItem "CHIAV<-->TRISC"
        .AddItem "CHIAV<-->PITT"
    End With
End Sub

Its Change sub is:

Private Sub ComboBox1_Change()
    Dim operatore As String
    Dim Op1 As String
    Dim Op2 As String
    Dim trovato As Integer
    Dim rng As Range
    Set rng = Selection
    operatore = ActiveSheet.OLEObjects("ComboBox1").Object
    Op1 = Split(operatore, "<")(0)
    Op2 = Split(operatore, ">")(1)
    Cells(1, 2) = Op1
    Cells(2, 2) = Op2
    trovato = 0
    If ComboBox1.Value = "SELEZIONA OPERATORI" Then
        Exit Sub
    End If

    If rng Is Nothing Then
        MsgBox "Non hai selezionato nessun range di celle!"
        Exit Sub
    End If

    For Each cell In rng
        If (trovato = 2) Then
            Exit For

        ElseIf StrComp(cell.Value, Op1) = 0 Then
            trovato = trovato + 1
        End If
    Next cell

    If (trovato < 2) Then
        MsgBox "Operatori non trovati nella selezione!"
        cbReset
        Exit Sub
    Else
        Select Case operatore
            Case "CHIAV<-->MACC"
                For Each cell In Selection
                    If cell.Value = "CHIAV" Then
                        cell.Value = "MACC"
                    ElseIf cell.Value = "MACC" Then
                        cell.Value = "CHIAV"
                    End If
                Next cell
                MsgBox "Scambiato CHIAV con MACC"
            Case "CHIAV<-->TRISC"
                For Each cell In Selection
                    If cell.Value = "CHIAV" Then
                        cell.Value = "TRISC"
                    ElseIf cell.Value = "TRISC" Then
                        cell.Value = "CHIAV"
                    End If
                Next cell
                MsgBox "Scambiato CHIAV con TRISC"
            Case "CHIAV<-->PITT"
                For Each cell In Selection
                    If cell.Value = "CHIAV" Then
                        cell.Value = "PITT"
                    ElseIf cell.Value = "PITT" Then
                        cell.Value = "CHIAV"
                    End If
                Next cell
                MsgBox "Scambiato CHIAV con PITT" 
        End Select
    End If
    cbReset
End Sub

and cbReset is the macro:

Sub cbReset()
 'Reset ComboBox1 Values
      ActiveSheet.ComboBox1.Value = "SELEZIONA OPERATORI"
      ActiveSheet.Cells(1, 1).Select
End Sub

The program does what I wrote but after I select an item, the error appear in the statement "Op2 = Split(operatore, ">")(1)". I can't understand what I'm wrong. The split function shouldn't prompt error because the symbol ">" is in the string "operatore". Can somebody help me to understand?

Upvotes: 1

Views: 463

Answers (1)

YowE3K
YowE3K

Reputation: 23974

Your issue is caused by trying to find the portion of the ComboBox value after the > when there is no such character in the string "SELEZIONA OPERATORI".

You should move the test for the "default"(?) value prior to any operation being performed:

Private Sub ComboBox1_Change()
    Dim operatore As String
    Dim Op1 As String
    Dim Op2 As String
    Dim trovato As Integer
    Dim rng As Range

    If ComboBox1.Value = "SELEZIONA OPERATORI" Then
        Exit Sub
    End If

    Set rng = Selection
    operatore = ActiveSheet.OLEObjects("ComboBox1").Object
    Op1 = Split(operatore, "<")(0)
    Op2 = Split(operatore, ">")(1)
    Cells(1, 2) = Op1
    Cells(2, 2) = Op2
    trovato = 0

    '...

Alternatively, as mentioned by K.Dᴀᴠɪs, a more generic way of ensuring you don't create an error is to test for the existence of the delimiter prior to trying to access the portion of the string after the delimiter. (Obtaining the portion before the delimiter is safe to do, even when the delimiter doesn't exist.)

Private Sub ComboBox1_Change()
    Dim operatore As String
    Dim Op1 As String
    Dim Op2 As String
    Dim trovato As Integer
    Dim rng As Range

    Set rng = Selection
    operatore = ActiveSheet.OLEObjects("ComboBox1").Object
    'Note: It's usually better to use the full delimiter rather than just a part of it
    Op1 = Split(operatore, "<-->")(0)
    If Instr(operatore, "<-->") > 0 Then
        Op2 = Split(operatore, "<-->")(1)
    Else
        Op2 = ""
    End If
    Cells(1, 2) = Op1
    Cells(2, 2) = Op2
    trovato = 0

    If ComboBox1.Value = "SELEZIONA OPERATORI" Then
        Exit Sub
    End If

    '...

Upvotes: 2

Related Questions