User input variables not passing values to new procedure in Excel VBA

[Mattyb33]

I have set up several variables where the user inputs the value via input boxes. The problem I have arises when the macro runs onto the next procedure. For example, one of the variables I have is:-

Dim message6, title6, default6, day
message6 = "Please enter the day you are reporting on in the format of ddd (e.g. Monday would be Mon)"
title6 = "Inputbox"
default6 = "Mon"
day = InputBox(message6, title6, default6)

The user inputs the day (e.g. Wed) and this is stored in the variable called day. When the macro runs onto the next procedure, I want the value stored in day (Wed) to be carryed over to this next procedure. But what actually happens is I get the following error

Compile error: Argument not optional

Would anyone be able to tell me why this happening and any way I can resolve it?

Thanks.

 

[PHV]

Day is a VBA function ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

'Day' is a function name in vba, you can overwrite it by using it as a variable name, but outside the scope vba will treat is as a function and ask for argument. In this case:
- as a good practice, avoid variable names same as used by vba/host application,
- use declared variables within declaration scope.

combo

 

[Mattyb33]

Sorry - I should have realised that, my mistake!!!! I've corrected that. I enter a value for this variable when prompted. The macro then runs to the next procedure, and when I try to use the value I entered in this variable the code says it's empty. Have you got any idea why it would empty the variable between procedures? Not sure if there's a way I can make tha variable a global variable and still have it as a user input variable??

 

[osx99]

either define your variable as Public outside the procedure

eg

Public day As String

Sub test()

Dim message6, title6, default6
    message6 = "Please enter the day you are reporting on in the format of ddd (e.g. Monday would be Mon)"
    title6 = "Inputbox"
    default6 = "Mon"
    day = InputBox(message6, title6, default6)
    test2
End Sub

Sub test2()
MsgBox (day)
End Sub

or pass the variable to the other proc

Sub test()

Dim message6 As String, title6 As String, default6 As String, day As String
    message6 = "Please enter the day you are reporting on in the format of ddd (e.g. Monday would be Mon)"
    title6 = "Inputbox"
    default6 = "Mon"
    day = InputBox(message6, title6, default6)
    test2 day
End Sub

Sub test2(ByVal day As String)
MsgBox (day)

End Sub

 

[Andrzejek]

You should be aware and read about scope of the variables.

Also, your line:
[tt]Dim message6, title6, default6, day[/tt]
All your variables are Variants, I think you just need them to be Strings, don't you?

[tt]Dim strMessage6 As String
Dim strTitle6 as String
Dim strDefault6 As String
Dim strDay As String[/tt]



Have fun.

---- Andy

 

[Mattyb33]

Thanks for your help guys!! One final question I have - I have declared a date:-

Public daterep As Date

The value held in this variable is an input box which the user inputs (e.g. 05/01/2011). When I hover over this variable in my first procedure when stepping through it then it displays the correct value (i.e. 05/01/2011). However, when I hover over the same variable in my public declaration it displays 00:00:00. It also displys 00:00:00 when stepping through to the second procedure in my code. Any ideas why it wouldn't display the actual value in these other 2 areas?

 

[Andrzejek]

So are saying that you have something like:

Option Explicit
Public daterep As Date

Private Sub ABC()
... 
daterep - InputBox...
...
End Sub

Private Sub XYZ()
...
daterep
...
End Sub

If that's the case, check if you have declared [tt]daterep[/tt] somewhere else as local variable.

Have fun.

---- Andy

 

[Mattyb33]

That's great - I had indeed declared it somewhere else!!! Thank you very much to all of you for your help with this!!

 

[Andrzejek]

Am I psychic or what? I need to pick the lotto numbers now…..

Have fun.

---- Andy

 

[osx99]

If your psychic you can pick some numbers for me too

Andrzejek has given by far the best advice on this thread - understand the scope of your variables. My example works but only gives a few possible examples.

Also note that:

Dim message6, title6, default6

is different to

Dim message6, title6, default6 as string

is different to

Dim message6 As String, title6 As String, default6 As String