CODEWITHSUNDEEP
excelvbacontrols
Kris Van den Bergh
Kris Van den Bergh

Reputation: 1152

Calling a sub and passing an argument in VBA

I'm trying to call a sub (OnAction) and passing an argument from a toolbar control, but I cannot seem to call it as I keep getting "Cannot run the macro. The macro may not be available".

The control is in a workbook and the function in a module.

Option Explicit

Sub somefunction(Optional ServerType As String) End Sub

Here's the code for the Control:

With ToolsMenuControl

    .Caption = "some caption"
    .OnAction = "'" & ThisWorkbook.Name & "'!someFunction ""Wintel""'"
    .Tag = C_TAG

End With

Please advice.

Upvotes: 0

Views: 1191

Answers (2)

Atif Hussain
Atif Hussain

Reputation: 422

  1. Excel uses Path!Reference to refer either Cells or Macros. Path may be FileName or FullPath, (and SheetName for cell referencing). Reference may refer a cell address, or, Macro with arguments.
  2. For both Path and Reference, enclose within apostrophes(') if it contains special characters.
    • Paths e.g.: Book1.xlsx or 'C:\Users\Me\My Book.xlsx'. @KazJaw, file naming and paths will show differences when referring unSaved/Open/Closed files.
    • References e.g.: mySub or 'someFunction "Wintel" '. Extra spaces left for readability, truncated by interpreter.
  3. Strings in VB are enclosed in quotes (" "). Recommended way to embed quotes/apostrophes within strings is to double up on the quote used. e.g.
    • Use 'He''s good' to represent He's good
    • "Am ""I"" fine?" translates to Am "I" fine?
  4. Writing in VB, .OnAction = "'" & ThisWorkbook.Name & "'!'someFunction ""Wintel"" ' ".

Upvotes: 0

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19087

It seems that correct .OnAction property assignment depends on whether file is saved or not. Therefore there are to possible syntax presented below:

    If ThisWorkbook.Saved Then
        'when your file is saved
        .OnAction = "'" & ThisWorkbook.Name & "'!'somefunction ""Saved""'"
    Else
        'when file is not saved
        .OnAction = ThisWorkbook.Name & "!'somefunction ""Not Saved""'"
    End If

Upvotes: 1

Related Questions