3

I've gotten stuck with this VBA code. Any help would be greatly appreciated. I'm trying to change the first letters of 2 words from lower case to upper case. Also, how should I take the space in between these two words into consideration in the code?

I haven't been able to execute the code as I keep getting this compile error: "Argument not optional".

Function Properword(Text)
Dim rText
rText = Len(rText)
If rText(Mid(1, 1)) = LCase(Str) Then
    rText = UCase(Str)
    If rText(Mid(6, 1)) = LCase(Str) Then
    rText = UCase
End If

End Function

Cheers!

Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
K. L.
  • 71
  • 2
  • 4

2 Answers2

5

First of all, you don't have to use UDF. Simply use inbuilt WorksheetFunction.Proper function to achieve ProperCase.

If you still want to create UDF, an example would be

Function Properword(strTxt)
    Dim arrTxt

    arrTxt = Split(strTxt, " ")
    For i = LBound(arrTxt) To UBound(arrTxt)
        arrTxt(i) = UCase(Left(arrTxt(i), 1)) & Mid(arrTxt(i), 2)
    Next

    Properword = Join(arrTxt, " ")
End Function

Finally, issues with your code

rText = Len(rText) ~~ this means rText will contain a numeric value because Len returns the lenght of the string

If rText(Mid(1, 1)) = LCase(Str) Then ~~ Mid takes the string as first argument followed by start point and then end point (optional).

not sure what you were trying to do in the following lines.

rText = UCase(Str)
If rText(Mid(6, 1)) = LCase(Str) Then
rText = UCase
Pankaj Jaju
  • 5,371
  • 2
  • 25
  • 41
1

In addition to the Excel function PROPER

str = WorksheetFunction.Proper("UPPER lower")   ' "Upper Lower"

There is also the VBA.StrConv function:

str = StrConv("UPPER lower", vbProperCase)      ' "Upper Lower"

To convert only parts of the string to uppercase, you can use RegEx, or the Mid statement:

Mid(str, 1, 1) = UCase(Mid(str, 1, 1))          ' makes the first letter uppercase
Community
  • 1
  • 1
Slai
  • 22,144
  • 5
  • 45
  • 53