Require kind suggestion

[VBABEGINER]

Public Function JumpCase(CellText As String) As String
Static REX As Object '<-- RegExp
Static REX2 As Object '<-- RegExp
Dim rexMatch As Object '<-- Match
Dim rexSubMatch As Variant
Dim strTemp As String

If REX Is Nothing Then
Set REX = CreateObject("VBScript.RegExp")
With REX
.Global = True
.IgnoreCase = False '???
.Pattern = "([A-Za-z0-9 ]*)(kV|to|NaOH|Na2CO3)([A-Za-z0-9 ]*)"
End With

Set REX2 = CreateObject("VBScript.RegExp")
With REX2
.Global = False
.IgnoreCase = False '???
.Pattern = "kV|to|NaOH|Na2CO3"
End With
End If

If REX.Test(CellText) Then
Set rexMatch = REX.Execute(CellText)(0)

For Each rexSubMatch In rexMatch.SubMatches
If Not REX2.Test(rexSubMatch) Then
strTemp = strTemp & UCase(rexSubMatch)
Else
strTemp = strTemp & rexSubMatch
End If
Next

Else
strTemp = CellText
End If

JumpCase = strTemp
End Function

I got this code from one of the Experts.
i called this function in -

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call JumpCase
End Sub

But i got this error,
"Argument not optional" and the yellow color strip comes on above i coloured.

Can any one suggest, How this can be called, for test the code is suitable 4 my solution or not?

 

[Hermanito]

not sure what the JumpCase function does exactly (I seem to see some chemical jargon there), but you need to call it with an argument, a string to be exact, and it returns a string as result.

You can use it like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim Result As String
    Result = JumpCase("Some input text, not sure what is needed here...")
    MsgBox Result
End Sub

 

[VBABEGINER]

I can exlpain u wht requirement.

suppose cells any. e.g-

A1 = paris to india
A2 = paris kW
A3 = brazil Na2Co3

"to", "kW", "Na2Co3" and there more 3 to 4 words.

I tryed for output -

A1 = PARIS to INDIA
A2 = PARIS kW
A3 = BRAZIL Na2Co3

Understood some?

Right. I want to be read first, cells content, if cell content contain the following values say,

"to", "kW", "Na2Co3" and there more 3 to 4 words.

then skip this content and the other should make in CAPITAL.

I WAS TRYING THIS FROM LAST 2 DAYS. BUT I CANT.
THE PREV CODE I GOT FROM ONE OF THE EXPERTS LIKE YOU.
BUT THAT CODE I UNBLE TO RUN, COZ AS I SAID EARLIER.

NOW, can you have any idea?