Code and callbacks

Where your VBA ends up, how it is called, and what you do not need to know.

Every control can carry VBA. You write it on the right of the editor, in the same field the snippet library inserts into.

What gets generated

Your project produces two modules:

  • Callbacks holds Ribbon_OnLoad and the dispatchers Excel calls.
  • UserCode holds your code, unchanged, one block per control.

The dispatcher looks at the id of the clicked control and calls your procedure. You never have to deal with IRibbonControl unless you want to.

Your code starts with a procedure:

Sub StartExport()
    MsgBox "Export running"
End Sub

Anything below it is kept. The first declared procedure is the one that runs, unless you name another under entry point.

Why the procedure names carry a suffix

In the VBE you will see names like Ribbon_OnLoad_40YQU2. That is deliberate.

Excel does not resolve a ribbon callback inside your add-in. It resolves it across every loaded VBA project, and the first match wins. Two add-ins with identically named dispatchers would intercept each other: the click in add-in A lands in B's dispatcher, which does not know the id, and nothing happens. With no error message.

The suffix is derived from the project name and its structure. It stays the same as long as the project does, and differs between two projects.

Your own procedure names are left alone.

Your own modules

Under modules you write full VBA modules that end up in the project. Anything Public there is callable from any control. Rename one with the pencil next to its name, in the list and in the editor header. Letters A–Z, digits and _ are allowed, no spaces or accented letters; if a name does not fit, the reason appears below it.

Two rules validation enforces: module names must be unique, including against form names, and a Public Sub or Public Const may exist only once in the whole project. Otherwise VBA refuses to compile, and its message points somewhere else entirely.

A third rule concerns control ids: the generated VBA names a button's Sub after its id. If a button is named like a Public Sub in a module, say because you named it after its macro, there would be two subs of the same name, and Excel reports "Ambiguous name detected" at the first call. The validation names both places up front. The ids the editor assigns itself (btn_…) do not collide with anything.

Completing calls

As you type, the editor suggests the procedures callable from exactly this spot. It is backed by the same parser the validation uses to find duplicate names, so what is offered really does exist.

After Call the list opens by itself. By hand: Ctrl+Shift+Space, on a Mac Cmd+Shift+Space. Monaco's own Ctrl+Space works too — on a Mac only when the input-source switcher is not sitting on it, which it is by default as soon as you have more than one.

Tab takes the suggestion, Enter does not — as in the VBE. Enter always ends the line. The other way round costs a keystroke whenever the list happens to be open while you finish a line: the first Enter then accepts a suggestion that usually changes nothing visible, and only the second breaks the line.

On the header of a procedure nothing is suggested. There you are giving a new name rather than calling an existing one — and the name you are typing is itself in the index as soon as it parses.

What else the editor takes from the VBE

  • Keywords are capitalised the moment you leave the line: dim x as long becomes Dim x As Long. That doubles as a check — a word that does not get capitalised is not the keyword you took it for. Your own names are left alone, as is anything inside strings and comments.

  • Blocks close themselves and indent. Enter after Sub Foo adds the missing parentheses to make it Sub Foo(), writes End Sub below it and leaves the caret indented in between, and the same for If … Then, For, Do, With, Select Case, Function, Property, Type and Enum — only when the closer is actually missing, so nothing happens inside a finished procedure. A single-line If x Then Exit Sub closes itself in VBA and gets no End If. End If, Else, Case and Next dedent themselves as you type them.

  • Parameter hints: while you type the arguments the signature is shown, with the parameter you are on highlighted.

  • Go to declaration with F12 or Ctrl-click, and rename with F2 — both for procedures declared in this module. When the procedure lives elsewhere, hovering shows its signature and where it comes from.

  • Option Explicit is offered as a hint above the code in your own modules when the line is missing, with a button that inserts it. Deliberately not a marker in the code: it is advice, not a defect, the module compiles without it, and a marker would have to point at some line. It landed on line 1 and coloured whatever stood there — usually a perfectly correct Sub header. The reason behind the advice is unpleasant all the same: without it, a typo in a variable name makes VBA silently create a new, empty variable instead of reporting an error. gesmat = gesamt * 1.19 then writes into a variable nobody reads, and the result is quietly wrong. With Option Explicit, Excel reports "variable not defined" at compile time. The modules addin.studio generates already carry the line. Into your code – button code and form code – the export only puts it when you write it at the top yourself. Code that ran in your workbook without the line runs the same way in the add-in.

  • Undeclared names are marked — highlighted in yellow, with the name in the message. The same check Excel performs when compiling, only immediately. A background rather than an underline: a squiggle under six letters at 12px is something nobody sees.

    The two levels therefore look different in kind, not merely in colour: a warning is a highlighted name, an error is a red underline. Both at once would be saying the same thing twice — and amber on amber reads as an angry red at 12px. For it the editor knows your declarations, the project's procedures, its public constants and variables, the module names, and the whole Excel object model including its constants.

    It always sees the entire generated module, not just the slice in front of you: the code of all controls lands in one module, a form's events in its class module — what you declare in one place counts in the other.

Not included is anything that needs a running interpreter: breakpoints, the immediate window, locals and watches. Excel would have to run in the browser for that.

Only what VBA would find here is suggested:

  • Everything Public in your own modules, anywhere in the project. Public is the normal case: without a keyword, a Sub in a module is public.
  • Private only inside its own module. That is all Private means: the procedure exists for its module and for nobody else — not hidden, simply not findable from outside.
  • The code of all controls lives together in one generated module. A Private Sub written under one button is therefore callable from every other button — but not from one of your own modules.
  • A form is a class module. What lives there applies only inside that form, even when it is Public. The other way round, the form sees everything public in the project.

After Call the insert uses parentheses (Call Foo(a, b)), without Call it does not (Foo a, b) — both are valid VBA, but they are not interchangeable.

Completing Excel itself

After a dot the editor suggests the members of the object you are on — ws. shows a Worksheet's properties and methods, with the description from Microsoft's own documentation.

For that to be right, the editor reads what you declared: Dim ws As Worksheet is enough. Without a declaration only the name itself helps — the global names are known (Application, ActiveSheet, ThisWorkbook, Selection, Range, Cells and a few more).

Chains work too, as long as the documentation names the return type:

Dim ws As Worksheet
ws.Range("A1").Interior.

shows the Interior members, because Range returns a Range and Interior returns an Interior. Where the documentation names no type the chain stops and nothing is offered — better no suggestions than the wrong object's.

Plus the constants: from two typed characters on, xlUp, xlValues and the other 2244 appear in the list, with their enumeration and their value.

Covered are 46 objects with around 2200 members — the ones that actually turn up in add-in code, not the full model with its 344 objects.

Library

Save a recurring routine once as a snippet and insert it anywhere. Snippets are yours alone; nobody else can see them.

No passwords in your code

Don't put passwords, API keys or credentials directly into the VBA. Two reasons:

  • The finished add-in contains the code in plain text. Anyone who gets the .xlam file can open the VBA editor and read along. A VBA project password doesn't stop them; freely available tools remove it.
  • Your project is stored in our database. There it is bound to your account and invisible to other users, but it is not end-to-end encrypted. What we do and don't do with that access is described in the privacy policy.

Better: ask for the value at run time (InputBox, a sign-in dialog), or read it from a file or environment variable that only exists on the user's machine. Server paths and drive letters are less critical, but they too reveal something about the environment the add-in runs in.

Code tester

The control palette has the code tester as a ready-made building block: a large button that opens a window in the add-in. Type or paste VBA there and start it with Run or Ctrl+Enter. The first Sub without parameters is started.

  • Code runs in a hidden helper workbook that is closed without saving afterwards, so your workbook gets no module. ActiveSheet and Selection still mean your sheet and your selection.
  • After running, the window opens again with your code and a status line, so you can change it and run it again.
  • Excel has to allow access to the VBA project object model: File › Options › Trust Center › Trust Center Settings › Macro Settings. If it is off, the tester says exactly that.

The code tester belongs to the plans with your own VBA (Free and Pro). It lets anyone who uses the add-in run code, so only put it in an add-in for colleagues if that is what you want.