Solved Help With VBA Function (1 Viewer)

[jo15765]

have a user form where a user must input a text value. I am wanting to check if the input string is 2 words ore more, and if the input string is one word.

What I need is if the input string is 2 words or more, then I need to take the first character from the 1st word and 2nd word. If the input string is one word, I need to take the first two characters from the first word.

I google ninjad this code below but it seems that MachineAbbrev is always null?

Option Compare Database
Option Explicit

Private Sub cmdAddMachine_Click()
    Dim MachineId As String
    Dim MachineName As String
    Dim MachineAbbrev As String
    Dim str As String
    Dim NumWords As Variant
    Dim c As Long
    Dim Sp As Variant, s As Variant
    If Not IsNull(Me.txtAddNewMachine.Value) Then
        MachineName = Me.txtAddNewMachine.Value
        
        NumWords = UBound(Split(Me.txtAddNewMachine.Value, " ")) + 1
        If NumWords >= 2 Then
            MachineAbbrev = GetMachineName(MachineName, NumWords)
        End If
        If NumWords = 1 Then
            MachineAbbrev = GetMachineName(MachineName, NumWords)
        End If
        MachineId = ""
        Debug.Print MachineAbbrev
    End If
End Sub

Private Function GetMachineName(MachineName As String, NumWords As Variant)
    Dim Sp, ct, s As Variant
    Dim formatMachineName As String
    
    Sp = Split(MachineName, " ")
    ct = 1
    
    For Each s In Sp
        If ct >= 1 And ct <= 2 And NumWords >= 2 Then
            formatMachineName = s + s
        End If
        If ct >= 1 And NumWords = 1 Then
            formatMachineName = s
        End If
    Next s
End Function

 

[GPGeorge]

If NumWords >= 2 Then
MachineAbbrev = GetMachineName(MachineName, NumWords)
End If
If NumWords = 1 Then
MachineAbbrev = GetMachineName(MachineName, NumWords)
End If

It looks to me like there is no difference in the two conditionals; i.e. you call GetMachineName regardless of he number of words in the name. What's the intent?

Please the entire first part of that function seems redundant anyway, as the actual parsing happens in GetMachineName anyway.

However, to address your actual question, nothing in the function called GetMachineName assigns a value to it, so there nothing to pass back to the first function to assign to MachineAbbrev anyway.

Try this instead:
Private Function GetMachineName(MachineName As String, NumWords As Variant) As String
....code
GetMachineName = formatMachineName
End Function

But I think you can eliminate most of that first function....

 

[jo15765]

@GPGeorge - oh that has MachineAbbrev with a value, however it's not functioning as expected. Meaning, it's appending the entire second word twice, so if I input Jason Jones, MachineAbbrev = JonesJones instead of JJ.

What would you do to clean this up/make it work as expcted?

 

[arnelgp]

Option Compare Database
Option Explicit

Private Sub cmdAddMachine_Click()
    Dim MachineId As String
    Dim MachineName As String
    Dim MachineAbbrev As String
    Dim str As String
    Dim NumWords As Variant
    Dim c As Long
    Dim Sp As Variant, s As Variant
    If Not IsNull(Me.txtAddNewMachine.Value) Then
        MachineName = Trim$(Me.txtAddNewMachine.Value)
        'arnelgp
        'remove extra space
        Do While InStr(1, MachineName, "  ") <> 0
            MachineName = Replace$(MachineName, "  ", " ")
        Loop
        'check if there is space on MachineName
        If InStr(1, MachineName, " ") <> 0 Then
            MachineAbbrev = GetMachineName(MachineName)
        Else
            MachineAbbrev = GetMachineName(MachineName, 1)
        End If
        MachineId = ""
        Debug.Print MachineAbbrev
    End If
End Sub

Private Function GetMachineName(MachineName As String, Optional ByVal NumWords As Integer = 0)
    Dim var
    Select Case NumWords
    Case 0
        GetMachineName = Left$(MachineName, 2)
    Case Else
        var = Split(MachineName, " ")
        GetMachineName = Left$(Trim$(var(0)), 1) & Left$(Trim$(var(1)), 1)
    End Select
End Function

 

[jo15765]

@arnelgp - that is so very close. For example, if I input "Galactic Empire" into the text box, the code returns Ga instead of GE

Since there are two words I want the first letter of the first two words.

Does that make sense?

 

[arnelgp]

sorry, you change this portion:

...
'check if there is space on MachineName
If InStr(1, MachineName, " ") <> 0 Then
MachineAbbrev = GetMachineName(MachineName)
Else
MachineAbbrev = GetMachineName(MachineName, 1)
End If

TO:

'check if there is space on MachineName
If InStr(1, MachineName, " ") <> 0 Then
MachineAbbrev = GetMachineName(MachineName, 1)
Else
MachineAbbrev = GetMachineName(MachineName)
End If

 

[jo15765]

@arnelgp - that is PERFECT!!!

If I can, is there a way to Force both characters to be upper case?

 

[arnelgp]

change the function to:

Private Function GetMachineName(MachineName As String, Optional ByVal NumWords As Integer = 0)
    Dim var
    Select Case NumWords
    Case 0
        GetMachineName = UCase(Left$(MachineName, 2))
    Case Else
        var = Split(MachineName, " ")
        GetMachineName = UCase(Left$(Trim$(var(0)), 1) & Left$(Trim$(var(1)), 1))
    End Select
End Function

 

[jo15765]

Genius! Thank you @arnelgp