Type mismatch Excel/Outlook vba

Question

What I want is the macro to put: first name | last name | username

I am getting an error when calling the userNameBuilder function even though it is returning a string and expecting a string

Dim firstName, lastName As String
Set sh = ActiveSheet
Dim counter = 0
For Each entry In exUser.GetDirectReports() 'exUser is an exchangeUser
    counter = counter + 1
    firstName = entry.GetExchangeUser.firstName
    lastName = entry.getExchangeUser.lastName
    sh.Cells(counter, 1).value = firstName 
    sh.Cells(counter, 2).value = lastName
    sh.Cells(counter, 3).value = userNameBuilder(firstName, lastName) 
Next

Here is my userNameBuilder function that the code is complaining.

When I comment out "sh.Cells(counter, 3).value = userNameBuilder(firstName, lastName)" then the code runs fine, just doesn't make the username.

The username is the first 5 letters of last name plus first letter of the first name. If the last name is too short, it is just filled with characters of the first name starting at the beginning until the username is 6 characters long.

Public Function userNameBuilder(ByVal firstName As String, ByVal lastName As String) As String
    Dim newLastName As String
    Dim newUserName As String
    If (lastName >= 5) Then
        newLastName = Left(lastName, Len(lastName) - 5)
        userNameBuilder = (newLastName & Left(firstName, 1))
    ElseIf (lastName < 5 && lastName > 0) Then
        userNameBuilder = lastName & Left(firstName, 5 - (Len(lastName)))
    Else
        userNameBuilder = vbNullString
    End If
End Function

this may not be related to your problem but "the first 5 letters of last name" would be newLastName = Left(lastName,5) — user3598756

user3598756 user3598756 · Accepted Answer · 2016-10-11T18:15:45

instead of

If (lastName >= 5) Then

you must use

If (Len(lastName) >= 5) Then

and the likes

Public Function userNameBuilder(ByVal firstName As String, ByVal lastName As String) As String
    Dim newLastName As String
    Dim newUserName As String
    If (Len(lastName) >= 5) Then
        newLastName = Left(lastName, 5)
        userNameBuilder = (newLastName & Left(firstName, 1))
    ElseIf (Len(lastName) < 5 && Len(lastName) > 0) Then
        userNameBuilder = lastName & Left(firstName, 5 - (Len(lastName)))
    Else
        userNameBuilder = vbNullString
    End If
End Function

Type mismatch Excel/Outlook vba

1 Answers