0

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

GCC
  • 285
  • 6
  • 23

2 Answers2

4

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.

QHarr
  • 83,427
  • 12
  • 54
  • 101
  • Thanks so much! I spent a lot of man hours trying to figure this one out. Didn't know it was this easy. – GCC Feb 03 '18 at 17:43
  • From Help *If the user clicks Cancel, the function returns a zero-length string ("").* A null string has a memory address of 0. An empty string will be somewhere in memory (but not the first 64K) with a length of 0. **Help** determines the contract. – ACatInLove Feb 03 '18 at 21:36
  • @ACatInLove thanks for the info. Is there an amendment i should make? – QHarr Feb 03 '18 at 21:38
  • In VB using VB functions always test for an empty string. When calling API calls is when you use vbnullstring as API calls expect memory address of 0 to know its a null string. The purpose of doing this is *if* there is an upgrade old code will still work if it does what help says to do (that's the contract if you do what help says MS will make sure code works in updates). – ACatInLove Feb 03 '18 at 21:46
  • Could you explain the purpose of the with statement? It seems unnecessary, but I may be overlooking something. – JG7 Feb 03 '18 at 22:06
  • @JoeyGrant It was in OP's original code. I hadn't noticed it tbh. It serves no purpose in the code as shown. They may have edited something out. I will comment out in my answer. Thanks for spotting – QHarr Feb 03 '18 at 22:08
  • @ACatInLove @QHarr, it's not quicker to test for `if Len(Link)<>0`? Asking, not challenging. – chillin Feb 04 '18 at 09:56
  • 1
    No and the reason is you are calling a function. How much slower, not much. But avoid it in a loop. Functions require placing the return address, parameters, return value on the stack the jumping to a memory address (in the function local variables are then created on the stack). Calling functions is a CPU thing. – ACatInLove Feb 04 '18 at 10:02
  • OP is calling `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 - but IMO invoking `Application.InputBox` is working around the problem rather than fixing it. – Mathieu Guindon Feb 04 '18 at 15:58
  • @ACatInLove exactly. it's a CPU thing. and CPU's do these things millions of times every single second. Function calls aren't a realistic performance bottleneck in any way. Sorry but that's not an argument - by that logic we should all be writing 10K-liner monolithic "God procedures", because hey it's "faster". – Mathieu Guindon Feb 04 '18 at 16:02
  • @Mat'sMug Do you mind if i quote you and update my answer re workaround rather than fix and what actual answer should be? – QHarr Feb 04 '18 at 16:38
1
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.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235