Custom formula to change to small caps

**rasherb** · 05-21-2009, 04:40 PM

I've seen macros for this but I need a formula that pulls from a different worksheet.
Can someone help please?

Here's what I've got so far:


Function SmallCaps(myString As String)

Dim CellLength%, i%, DefaultSize
Dim OutString As String

OutString = myString

CellLength = Len(myString)
DefaultSize = 12

For i = 1 To CellLength
 With OutString.Characters(i, 1)
   If .Text = UCase(.Text) Then
      .Font.Size = DefaultSize
   Else
      .Text = UCase(.Text)
      .Font.Size = DefaultSize - 2
   End If
 End With
Next i

SmallCaps = CellLength

End Function

**shg** · 05-21-2009, 04:50 PM

A string doesn't have a font size. You need to do it after the data is copied.

Sub SmallCaps()
    Const dSize As Double = 12
    Dim cell    As Range
    Dim i       As Long

    For Each cell In Selection
        For i = 1 To Len(cell.Text)
            With cell.Characters(i, 1)
                If .Text = UCase(.Text) Then
                    .Font.Size = dSize
                Else
                    .Text = UCase(.Text)
                    .Font.Size = dSize - 2
                End If
            End With
        Next i
    Next cell
End Sub

**rasherb** · 05-22-2009, 11:09 AM

Okay. Thanks!

I want to make a formula though.

So I can type text into one cell and the formula will pull the text and format small caps into another cell.

Any ideas for making this a formula, rather than a macro?

**shg** · 05-22-2009, 11:17 AM

It can't be done with a formula for the reason I explained in the first post -- string variables don't have fonts, they are just strings of characters. A cell can contain rich text, so you can format individual characters with varying font, font size, bold, italic ...