CODEWITHSUNDEEP
vbaexcel
lemon
lemon

Reputation: 23

Excel VBA - Call application function (left, right) by name

I would like to be able to call application functions such as left() and right() using a string variable.

The reason is that my current code works fine, but has multiple instances of left() and right() that may need to change every time I run it. I'd like to be able to only change it once ("globally") every time.

After googling, I tried CallByName and Application.Run. It seems that they only work with a custom class/macro. Is this true? Is there anything else I should look into? I don't need specific code, thank you!

Upvotes: 2

Views: 315

Answers (3)

brettdj
brettdj

Reputation: 55682

You could also use SWITCH to implement Scott's idea (no error handling if string length is invalid):

Sub Test()
    Debug.Print LeftRight("L", "StackOverflow", 5)
    Debug.Print LeftRight("R", "StackOverflow", 8)
End Sub

Function LeftRight(sWhich As String, sValue As String, iLength As Integer) As String
     LeftRight = Switch(sWhich = "L", Left$(sValue, iLength), sWhich = "R", Right$(sValue, iLength))
End Function

Upvotes: 1

Scott Holtzman
Scott Holtzman

Reputation: 27249

You can build a custom function where you pass if you want Left or Right.

Option Explicit

Sub Test()

    Debug.Print LeftRight("L", "StackOverflow", 5)
    Debug.Print LeftRight("R", "StackOverflow", 8)

End Sub

Function LeftRight(sWhich As String, sValue As String, iLength As Integer) As String

    Select Case sWhich

        Case "L": LeftRight = Left(sValue, iLength)
        Case "R": LeftRight = Right(sValue, iLength)
    
    End Select

End Function

You just use "L" or "R" as needed. Change it once and pass as sWhich each time. You can even use a cell reference for this and update the cell before running code.

Results

Stack

Overflow

Upvotes: 3

YowE3K
YowE3K

Reputation: 23974

The easiest way around this is to replace all your Left and Right calls with a generic function, e.g. instead of

x = Left("abc", 2)

say

x = LeftOrRight("abc", 2)

and then have a function

Function LeftOrRight(str As Variant, len As Long) As Variant
    'Uncomment this line for Left
    LeftOrRight = Left(str, len)
    'Uncomment this line for Right
    LeftOrRight = Right(str, len)
End Function

Then you can just change the one function as required.

Upvotes: 1

Related Questions