Search code examples
excelvbavariableskey

VBA OnKey And Pass a Variable

I am trying to have an OnKey event trigger a sub and pass a variable to that sub. Triggering the sub with a button works just fine (these are both in Module1):

Sub button77()
scanner (1)
End Sub

Sub scanner(keyscan As Integer)
Debug.Print keyscan
End Sub

What also works is triggering the sub without a variable (first part is on a worksheet, the second is still in Module1):

Private Sub Worksheet_Activate()
Application.OnKey "1", "scanner"
End Sub

Sub scanner()
Debug.Print "TEST"
End Sub

But I cannot get the following to work:

Private Sub Worksheet_Activate()
Application.OnKey "1", "scanner(1)"
End Sub

Sub scanner(keyscan As Integer)
Debug.Print keyscan
End Sub

It gives me "Argument not Optional". I've tried to format the procedure of OnKey to various iterations - for example

Application.OnKey "1", "'scanner" & 1 & "'"

and others, tried with a string but it does not work no matter what I try. Anyone knows what is going on?

Solution

  • You're missing a space before the argument:

    Application.OnKey "1", "'scanner " & 1 & "'"

    so that it reads as 'scanner 1' not 'scanner1' which would make the code look for a routine called scanner1.