• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

I need a VBA event to call a function with parameters

What's the VBA syntax (in Excel 2007) to have an event call an internal VBA function I will write, passing in a few parameters. I want to resuse that function for  multiple events and to use the input parameter to differentiate calls to the same function from various events?

Thanks.
0
newbieweb
Asked:
newbieweb
  • 3
  • 3
3 Solutions
 
zorvek (Kevin Jones)ConsultantCommented:
Write the function like so:

Public Function Foo(ByVal RequestCode As Long, ByVal Variable1 As Variant, ByVal Variable2 As Variant) As Variant

    Select Case RequestCode
        Case 1:
            ' Do this
        Case 2:
            ' Do that
    End Select

End Function

Kevin
0
 
newbiewebSr. Software EngineerAuthor Commented:
How do I execute it?
0
 
newbiewebSr. Software EngineerAuthor Commented:
And what does "As Variant" do?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
zorvek (Kevin Jones)ConsultantCommented:
Like so:

Public Sub SomeEventHandler()

    Dim MyResult As Variant

    MyResult = Foo(1, "A", 100)

End Sub

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
"As Variant" defines the variable as a variant data type which means it can store any type of value.

You can also define a variable as Long, String, Date, an array, a collection, or any of many other types.

Kevin
0
 
newbiewebSr. Software EngineerAuthor Commented:
Thanks a lot!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now