~Help~ converting Excel equation to vba function

Hello,

I dont know if this can be done...

Say this is my equation in a cell:

=PROPER(LEFT(A1,FIND(":",A1,4)-1))&IF(CountOccurrences(A1,",,")>0," "&"("&CountOccurrences(A1,",,")+1&")",)

And say A1 is a text =gift:something free,, is good,, can be bad because it may explode

The equation returns: 'Gift (3)'<in A1> and the number 3 refers to items separated by ",,"



How do I put this into VBa programming so that I can just highlight any cells and apply this formula that will give the result? Um.... is this even simple to do?

"Countoccurences" is a user-defined equation that returns the number of times substring appears in str:

Function CountOccurrences(str, substring) As Long
Dim x As Variant
x = Split(str, substring)
CountOccurrences = UBound(x)

----

I thought this may be possible because I happened to look at this highlight-'n-apply-trimcleaning-macro:

Sub trimclean()
Dim CTRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction

Set Func = Application.WorksheetFunction

On Error Resume Next
Set CTRg = Selection.SpecialCells(xlCellTypeConstants, 2)
If Err Then MsgBox "No data to clean and Trim!": Exit Sub

For Each oCell In CTRg
oCell = Application.WorksheetFunction.Clean(Func.Trim(oCell))
Next


End Sub

But I'm so confused with all the functions listed here; dont know how to make my own function.

Please help!

Actually peeps, nevermind! :

What I'm probably asking for is a lot.
Anyways... I'll be posting a new probably-easier-to-answer-question on the next post instead.