Pass a variable to another subroutine and get back the result

**anandvh** · 06-11-2013, 12:16 PM

Hi,

In my application, I read the contents of a cell which contains the full name. I need to split the full name into the three components - Surname, First Name and the Middle Name.

In my code I read the full name and pass this to another subroutine to split the full name into Surname, First Name and Middle Name. This portion seems to work fine. Where I am having a problem is getting back the Surname, First Name and Middle Name after the split. This comes back empty. The code is posted below. Would be really grateful if some one can point out the mistake I have made and give me correct code.

Private Sub StoreName
            Dim iRcptRow As Integer, wsRcpt As Worksheet
            Set wsRcpt = ThisWorkbook.Worksheets("RCPT")
            iRcptRow = xlLastRow("RCPT") + 1
            SplitMemName Me.FeeLblDes2.Caption
            wsRcpt.Range("D" & iRcptRow).Value = sMemSurN
            wsRcpt.Range("E" & iRcptRow).Value = sMemFirN
            wsRcpt.Range("F" & iRcptRow).Value = sMemMidN
End Sub

Private Sub SplitMemName(sMemName As String)

' This routine splits the full name into the Surname
' Middle name and First name using <space> as the split criterion

    Dim NameSplit As Variant
    avarSplit = Split(sMemName, " ")
    If NameSplit(0) = "" Then
        sMemSurN = ""
    Else
        sMemSurN = NameSplit(0)
    End If
    If NameSplit(1) = "" Then
        sMemFirN = ""
    Else
        sMemFirN = NameSplit(1)
    End If
    If NameSplit(2) = "" Then
        smemsmidn = ""
    Else
        sMemMidN = NameSplit(2)
    End If
    
End Sub

xlLastRow is a function written to get the first blank row in the worksheet. There is no problem with this routine.

Thanks a ton in advance

Anand

**ducky_yeng** · 06-12-2013, 04:55 PM

Hi anandvh

I copied your SplitMemName code and declared sMemSurN, sMemFirN, sMemMidN as global variables (this was an assumption). I got it to work by making one small change.
Replace

avarSplit = Split(sMemName, " ")

With

NameSplit = Split(sMemName, " ")

Test with this code

Public Sub Test()
    SplitMemName "Thomas John Woodward"
    Debug.Print "Surname: " & sMemSurN
    Debug.Print "Firstname: " & sMemFirN
    Debug.Print "Middlename: " & sMemMidN
End Sub

Hope this helps.