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

is it what you need Call Sort1 (Name)?

Dmitry Pavliv
– Dmitry Pavliv

2014-02-17 22:20:30 +00:00
Commented Feb 17, 2014 at 22:20 — Dmitry Pavliv
– Dmitry Pavliv, Commented Feb 17, 2014 at 22:20

WGS · Accepted Answer · 2014-02-17 22:33:52Z

3

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.

answered Feb 17, 2014 at 22:33

WGS

14.2k5 gold badges50 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

The Benetrator Over a year ago

Its people like you and @wumpz that are the reason i love this site. Ive been stressing for hours over this lol and you lot save the day yet again with informed and clear advice. +1s all round, worked perfectly. Thanks so much.

wumpz · Accepted Answer · 2014-02-17 22:27:24Z

1

You hat to give your sort1 procedure the parameter name.

call sort1(name)

or call sort1(CommandButton4.Caption)

answered Feb 17, 2014 at 22:27

wumpz

9,3213 gold badges33 silver badges27 bronze badges

Collectives™ on Stack Overflow

Excel VBA - Use an existing string in called sub

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related