Custom functions – VBA

Spread the love

VBA – Custom functions

it simplifies a job a complex function is being repeated  and taking lenghthy process to develop it every time on excel sheet or may be not possible to create by excel function. VBA solves the problem and helps to develop and incorporate your own thought through function creation.

We just create two simple through VBA which can help to merge the cells in one cell and replace the number/text from value.

Snapshot of UDF formulas 

CUSTOM FUNCTIONS VBA

 

MergeCells – merge cells in one cell with input string(optional)

Below is the code copy and paste on VBA page

Function MergeCells(myString As Variant, rng As Range) As String            ‘ this is a inpurt range within parantheses and output ‘defined as String

Dim myrng As Range
Dim myval As Variant
‘passed a single range from input range
Set myrng = rng
‘ for each loop will read every cell in selected range and added string between the values

For Each myrng In rng
If myString <> “” Then
myval = myval & myString & myrng.Value
Else
myval = myval & myrng.Value
End If
Next myrng

MergeCells = Trim(myval)
End Function

 

 

Replace Text Number  – Create function to replace number and text basis on input parameter

Below is the code copy and paste on VBA page

Function ReplaceTextNumber(myString As Variant, rng As Variant) As String

Dim Sch, Lch, I, J, XJ As Integer

            If UCase(myString) = “NUMBER” Then
Sch = 48: Lch = 57
ElseIf myString = “USPELL” Then
Sch = 65: Lch = 90
ElseIf myString = “LSPELL” Then
Sch = 97: Lch = 122
ElseIf myString = “BOTHSPELL” Then
Sch = 97: Lch = 122
xSch = 65: xLch = 90
GoTo DoubleLoop
End If

‘ SINGLE OPERATIONS
For I = Sch To Lch
rng = WorksheetFunction.Substitute(rng, Chr(I), “”)
Next I
ReplaceTextNumber = rng
GoTo SingleLoop
DoubleLoop:

For J = Sch To Lch
rng = WorksheetFunction.Substitute(rng, Chr(J), “”)
Next J

For XJ = xSch To xLch
rng = WorksheetFunction.Substitute(rng, Chr(XJ), “”)
Next XJ
ReplaceTextNumber = rng
SingleLoop:
End Function