CODEWITHSUNDEEP
vbaexcel
GCC
GCC

Reputation: 295

Input box getting a compile error in VBA

I am learning how to create input boxes and I keep getting the same error. I have tried two different computers and have received the same error. The error I get is a "Compile Error: Wrong number of arguments or invalid property assignment"

Here is my code:

Option Explicit

Sub InputBox()


Dim ss As Worksheet
Dim Link As String

Set ss = Worksheets("ss")
Link = InputBox("give me some input")
ss.Range("A1").Value = Link


With ss

    If Link <> "" Then
        MsgBox Link
    End If

End With

End Sub

When I run the code, it highlights the word "inputbox"

And help would be greatly appreciated.

Thanks,

G

Upvotes: 0

Views: 1695

Answers (2)

QHarr
QHarr

Reputation: 84465

Three things

1) Call your sub something other than the reserved word InputBox as this may confuse things. *Edit... and this alone will resolve your error. See quote from @Mat's Mug.

2) A̶p̶p̶l̶i̶c̶a̶t̶i̶o̶n̶.̶I̶n̶p̶u̶t̶B̶o̶x̶(̶"̶g̶i̶v̶e̶ ̶m̶e̶ ̶s̶o̶m̶e̶ ̶i̶n̶p̶u̶t̶"̶)̶ Use VBA.Interaction.InputBox("give me some input"). You can do this in addition to the first point. Documentation here.

3) Compare with vbNullString rather than "" . See here. Essentially, you will generally want to do this as vbNullString is, as described in that link, faster to assign and process and it takes less memory.

Sub GetInput()

Dim ss As Worksheet
Dim Link As String

Set ss = Worksheets("ss")
Link = VBA.Interaction.InputBox("give me some input")
ss.Range("A1").Value = Link

'  With ss ''commented out as not sure how this was being used. It currently serves no purpose.

If Link <> vbNullString Then
    MsgBox Link
End If

'  End With

End Sub

EDIT: To quote @Mat's Mug:

[In the OP's code, what is actually being called is] VBA.Interaction.InputBox, but the call is shadowed by the procedure's identifier "InputBox", which is causing the error. Changing it to Application.InputBox "fixes" the problem, but doesn't invoke the same function at all. The solution is to either fully-qualify the call (i.e. VBA.Interaction.InputBox), or to rename the procedure (e.g. Sub DoSomething(), or both.

Upvotes: 4

Mathieu Guindon
Mathieu Guindon

Reputation: 71167

Sub InputBox()

That procedure is implicitly Public. Presumably being written in a standard module, that makes it globally scoped.

Link = InputBox("give me some input")

This means to invoke the VBA.Interaction.InputBox function, and would normally succeed. Except by naming your procedure InputBox, you've changed how VBA resolves this identifier: it no longer resolves to the global-scope VBA.Interaction.InputBox function; it resolves to your InputBox procedure, because VBAProject1.Module1.InputBox (assuming your VBA project and module name are respectively VBAProject1 and Module1) are always going to have priority over any other function defined in any other referenced type library - including the VBA standard library.

When VBA resolves member calls, it only looks at the identifier. If the parameters mismatch, it's not going to say "hmm ok then, not that one" and continue searching the global scope for more matches with a different signature - instead it blows up and says "I've found the procedure you're looking for, but I don't know what to do with these parameters".

If you change your signature to accept a String parameter, you get a recursive call:

Sub InputBox(ByVal msg As String)

That would compile and run... and soon blow up the call stack, because there's a hard limit on how deep the runtime call stack can go.

So one solution could be to properly qualify the InputBox call, so that the compiler knows exactly where to look for that member:

Link = VBA.Interaction.InputBox("give me some input")

Another solution could be to properly name your procedure so that its name starts with a verb, roughly describes what's going on, and doesn't collide with anything else in global scope:

Sub TestInputBox()

Another solution/work-around could be to use a similar function that happens to be available in the Excel object model, as QHarr suggested:

Link = Application.InputBox("give me some input")

This isn't the function you were calling before though, and that will only work in a VBA host that has an InputBox member on its Application class, whereas the VBA.Interaction.InputBox global function is defined in the VBA standard library and works in any VBA host.

A note about this:

If Link <> "" Then

This condition will be False, regardless of whether the user clicked OK or cancelled the dialog by "X-ing out". The InputBox function returns a null string pointer when it's cancelled, and an actual empty string when it's okayed with, well, an empty string input.

So if an empty string needs to be considered a valid input and you need to be able to tell it apart from a cancelled inputbox, you need to compare the string pointers:

If StrPtr(Link) <> 0 Then

This condition will only be False when the user explicitly cancelled, and will still evaluate to True if the user provided a legit empty string.

Upvotes: 1

Related Questions