Excel VBA - Use an existing string in called sub

Question

I'm pretty new to this so apologies in advance

I'm half way through a userform in Excel and I'm trying to cut some fat off my code by using Call - I have 12 buttons that all do the same thing, the only difference is that each buttons sub is dependant on the buttons caption. My problem is that I can't figure out a way to use a String I've already declared in the Buttons Sub, then use it in the called Sub. I know you can do it, but my googling skills have failed me :(

Please could someone show me how to do this? Hope that all makes sense...

Here is a very small snippet of my code, but you get the jist:

    Public Sub CommandButton4_Click()
    Dim Name As String

    Name = CommandButton4.Caption

    Call Sort1

    End Sub`

And the other one (Also tried this as function for the sake of trial and error)

    Public Sub Sort1(Name As String)

    Label11.Caption = Name
    Sheets(Name).Select

    End Sub

Answer 1

What you're referring to is passing an argument to another subroutine or function. Let's say you want to use a function a lot of times to get the first letter of a string. A sample of this is:

Function LeftOne(StrSample As String) As String
    LeftOne = Left(StrSample, 1)
End Function

The above function can be used inside another function or subroutine provided you meet its requirement: StrSample. By declaring StrSample As String in the arguments field of the function, you are basically requiring that any calls to this should require a string to be passed to it. Anything else would throw an error.

The full line LeftOne(StrSample As String) As String can be read as: "I am function LeftOne. Pass me a string and I'll return to you a string after doing something with it." Note that the name StrSample is an arbitrary name.

Anyway, calling the above is as simple as:

Sub MsgInABox()
    Dim StrToFeed As String
    StrToFeed = "BK201"
    MsgBox LeftOne(StrToFeed) 'Returns B.
End Sub

In your example, if you want to pass Name to Sort1, your attempt is absolutely correct.

Let us know if this helps.

Answer 2

You hat to give your sort1 procedure the parameter name.

call sort1(name)

or call sort1(CommandButton4.Caption)