Pass Input Box variable from one sub to another

Question

I was reading a post by another where the need for a Global declaration wasn't needed.

I have a Sub asking for the user to enter a value, in another Sub I use the entered value to Call other Subs based on their input.

Am I close in my example? Thank you

Option Explicit

Sub MonthTest()
Dim strMonth As String

strMonth = InputBox("enter Area here")

Call Test2

End Sub

Sub Test2(strMonth As String)

Select Case strMonth
    Case Is = "82"
        Call Area82
    Case Is = "80"
        Call Area80
    
End Select
End Sub

Replace 'Call Test2` with `Test2 strMonth`, there's no need to use `Call` . — Raymond Wu, Sep 21 '21 at 14:20
`where the need for a Global declaration wasn't needed` Also there is no need for `Test2` as well. The `Select Case` can be a part of `MonthTest()` — Siddharth Rout, Sep 21 '21 at 14:33
@RaymondWu that simple edit worked. But now I'm getting an error on a Sub where in the past I had never got an error. — Paxton, Sep 21 '21 at 15:03
@Paxton, you have to be more specific - what error, what line? Although it would be out of scope of this question..... — Raymond Wu, Sep 21 '21 at 15:08
@RaymondWu the error code coming back is Compile error: Variable not defined `Rows("4:4").Delete Shift:=x1up` — Paxton, Sep 21 '21 at 15:12
@RaymondWu I just deleted `Shift:=x1up` and it now works perfectly. Thank you. This was my first post. How do I vote/choose your solution as the one that helped? — Paxton, Sep 21 '21 at 15:20
@Paxton it seems whatever you "fixed" is not shown in the question (and looks weird too because it should give an error as there's no enum `x1up`). Your original question most likely is a duplicate so I won't post an answer. — Raymond Wu, Sep 21 '21 at 15:27
@RaymondWu I would like to choose your solution as the answer, but I only see it as a comment. "Replace 'Call Test2` with Test2 strMonth, there's no need to use Call ." — Paxton, Sep 21 '21 at 15:42
@Paxton since you insisted, i have posted an answer. Thanks! — Raymond Wu, Sep 21 '21 at 16:18

score 0 · Answer 1 · answered Sep 21 '21 at 14:39

0

you must feed in the variable to the calling function, else it does not know where to look for it.

change: Call Test2 to: Call Test2 strMonth

answered Sep 21 '21 at 14:39

Alfredo Holguin

41
2

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-ask). – Community Sep 21 '21 at 15:09
I did that I am now receiving a new error - Compile error: Variable not defined `Rows("4:4").Delete Shift:=x1up`. I hadn't had this error before. – Paxton Sep 21 '21 at 15:16

score 0 · Accepted Answer · answered Sep 21 '21 at 16:18

Replace 'Call Test2withTest2 strMonth, there's no need to use Call`

Variable declared in a sub/function stays in the sub/function itself only, since you have declare a parameter in Test2, you will need to provide the parameter everytime you call Test2 (unless its declare as Optional).

score 0 · Answer 3 · answered Sep 21 '21 at 19:30

x1up should be xlup (lower case "L", not the #1)

it is a constant defined in the EXCEL COM library. Hit F2 and look under the EXCEL library (which you should have in your references in VBA). You should see the xlup constant defined in the (general) section of the library, or under a particular ENum name.

Pass Input Box variable from one sub to another

3 Answers3