VBA - Returning just upper case characters in a string

I am building a dynamic macro in Excel's VBA using our usernames using Environ("Username") and need to extract the upper case characters. So JohnSmith becomes JS

I can't get the macro to return just the initials. It just gives me a blank reply

'
' Macro1 Macro
'
Dim Username As String
Dim UserInits As String
Username = Environ("UserName")
Dim i As Integer
Dim spltStr() As String
spltStr = Split(Username)

UserInits = ""

i = 1
Do While i <= Len(Username)

    If UCase(spltStr(i)) = spltStr(i) Then
        UserInits = UserInits & spltStr(i)
    End If
    
i = i + 1
Loop

MsgBox UserInits

End Sub

Can anyone see what I'm doing wrong?

I doubt that your code runs at all. Reason is that the command Split(Username) is not doing what you expect. Split splits a string into words, using a delimiter character. If you omit the 2nd parameter (the delimiter), space is used. So split("JohnSmith") will return only one element (with index 0), Split(John Smith) will return two elements, `Split("A;B;C", ";") will return 3.

Following your code, spltStr(i) should throw an runtime error 9 Subscript out of Range as your Split command surely doesn't create an array with as many elements as characters in the name (plus it's index is always 0-based).

If you want to check for a single character, just use the mid-function with a third parameter = 1.

The following trivial function will extract the uppercase characters from a string.

Function GetInitials(s As String) As String
    Dim i As Long
    For i = 1 To Len(s)
        Dim c As String
        c = Mid(s, i, 1)
        If UCase(c) = c And Asc(c) > 32 Then
            GetInitials = GetInitials & c
        End If
    Next i
End Function

And the call could look like this:

Sub t1()
    Dim Username As String
    Username = Environ("UserName")
    
    MsgBox GetInitials(Username)
    ' or
    MsgBox GetInitials("User FunThomas gave me a good Answer on StackOverflow")
    
End Sub