quick and easy one - creating usernames [SOLVED]

djjb · Apr 22nd 2003

Hi

I need to create usernames from a provided list, in the following format

1. column A- First Name
2. column B - Middle Initial
3. Column C - Last Name

THe user name has to be in the following format:
firstname.middle initial.lastname

here's the catch: the username cannot contain any characters outside the 26-letter alphabet set. (i.e. Pat O'Riley would be Pat.Oriley)

Also, if No middle initial exists, it must be left unentered.
i.e. John S. Andrews would be John.S.Andrews
where as Jane Smith would be Jane.Smith
(notice only 1 "." was used in the 2nd case)

I have already done this with formulas, but its 7MB large, and very inefficeint. It checks letter by letter to see what it is, and it can do so for up to 51 letters

I want to do this in a macro, but i'm not sure how........any ideas?

**Andy Pope** · Apr 22nd 2003

Hi djjb,

Here's one way. using a user defined function to check and construct the username.

It still checks each character.

I'm not sure there is a quicker way. Testing all non valid characters could mean testing 255-(26*2) = 203 characters!

Code

Public Function udfUsername(Firstname As Variant, Middlename As Variant, Lastname As Variant) As Variant
'
    If Len(Middlename) &gt; 0 Then
        udfUsername = AToZ(Firstname) & "." & AToZ(Middlename) & "." & AToZ(Lastname)
    Else
        udfUsername = AToZ(Firstname) & "." & AToZ(Lastname)
    End If
    
End Function
Public Function AToZ(Text As Variant) As Variant
    Dim intIndex As Integer
    intIndex = 1
    Do While intIndex <= Len(Text)
        Select Case Mid(Text, intIndex, 1)
        Case "a" To "z"
            intIndex = intIndex + 1
        Case "A" To "Z"
            intIndex = intIndex + 1
        Case Else
            Text = Left(Text, intIndex - 1) & Mid(Text, intIndex + 1)
        End Select
    Loop
    AToZ = Text
End Function

Display More

You should be able to modify the function if you want it to be just a macro.

Cheers
Andy

Ted · Apr 22nd 2003

This might help for part of what you want:

Code

Dim myGoodUserName As String


Private Sub CommandButton1_Click()
    RemoveUnwantedCharacters Cells(1, 1).Value
    MsgBox myGoodUserName
End Sub


Private Sub RemoveUnwantedCharacters(ByVal strWhat As String)
    myGoodUserName = ""
    Dim i As Integer
    
    For i = 1 To Len(strWhat)
        If Mid$(LCase$(strWhat), i, 1) Like "[a-z]" Then
            myGoodUserName = myGoodUserName & Mid$(LCase$(strWhat), i, 1)
        End If
    Next i


End Sub

Display More

djjb · Apr 23rd 2003

Thanks for the input guys,

Little help, I'm quite new to macros, and I am not sure how User Defined Functions work. I tried Andy's code, not sure how to test it tho:(

MAybe this isn't so quick and easy......

Will Riley · Apr 23rd 2003

see the attached, using Andy Pope's code (nice work Andy :thumbcoo: ) - the code is in a module attached to the workbook

user defined function - you use it just like any of XL's built in functions once defined in the VBE - just take note that ALOT of UDFs will slow your spreadsheet calculation time.

Hope this helps

djjb · Apr 23rd 2003

^^^
Great work Andy!!

Code is stellar! Works really well, and sure is more effective then 7 MB.

Thank you. and thank you WIll for the Clarification.

quick and easy one - creating usernames [SOLVED]

Participate now!

Share