3 thoughts on “Building an Excel Add-in”

1. Awesome, Jan Karel.

2. This post has inspired me to resurrect some old VB6 (!) code I used to comment my procedures, classes, and forms (a copy of the comments it would place at the top of the code segment is below). I probably have a few APIs to upgrade! I’m writing enough VBA code that I can’t remember what modules reference each other, and this code was certainly useful back in the day.

   I’d like to convert it to Excel 2013 (64-bit for the added degree of difficulty), but I have no idea how to create an add-in for the VB Editor thw way it worked in VB6.
   While my cursor is within a particular module I would like to click an icon on the VB Editor toolbar that calls up a userform where I can input my info (the section starting with “Author” below) and have the program add the rest.
   I’m happy to email the original code if you think this is worthwhile to have done by a true professional .

   ‘¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
   ‘ Component Name: frmCodeRemarks
   ‘ Component Type: Standard Form
   ‘ Component File: C:\Program Files\Microsoft Visual Studio\VB98\AddIns\RemBuilder\frmStatus.frm
   ‘ Project Name: RemBuilder
   ‘ Project Type: ActiveX DLL
   ‘ VB Version: 6.00
   ‘<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
   ‘ Author: Your Name Here
   ‘ Copyright: ©2000 Your Company All Rights Reserved
   ‘ Created: 4/17/1998
   ‘ Purpose: Allow additional comments to be entered in header
   ‘
   ‘ Modified:
   ‘ [04.12.00] Fixed the SendMessage API problem (forgetting ByVal
   ‘ qualifier on last parameter of strong typing)
   ‘ Added multiline text box for lengthier Purpose notes
   ‘ [04.26.02] Added ability to save and retrieve author and
   ‘ copyright defaults, adding command buttons
   ‘————————————————————
   ‘ Project References:
   ‘ Visual Basic For Applications C:\WINNT\System32\msvbvm60.dll
   ‘ Visual Basic runtime objects and procedures C:\WINNT\System32\msvbvm60.dll\3
   ‘ Visual Basic objects and procedures C:\Program Files\Microsoft Visual Studio\VB98\VB6.OLB
   ‘ Microsoft Visual Basic 6.0 Extensibility C:\Program Files\Microsoft Visual Studio\VB98\VB6EXT.OLB
   ‘ Microsoft Office 10.0 Object Library C:\Program Files\Common Files\Microsoft Shared\Office10\MSO.DLL
   ‘ Microsoft Windows Common Controls 6.0 (SP4) C:\WINNT\System32\mscomctl.oca
   ‘
   ‘ Controls: Class Name
   ‘ —– —-
   ‘ CheckBox chkOption
   ‘ CommandButton cmdDefault(0)
   ‘ CommandButton cmdDefault(1)
   ‘ CommandButton cmdStatus(0)
   ‘ CommandButton cmdStatus(1)
   ‘ Label lblStatus(0)
   ‘ Label lblStatus(1)
   ‘ Label lblStatus(2)
   ‘ Label lblStatus(3)
   ‘ Label lblStatus(4)
   ‘ Label lblStatus(5)
   ‘ ProgressBar pbrRem
   ‘ StatusBar sbrRem
   ‘ TextBox txtMods
   ‘ TextBox txtNotes
   ‘ TextBox txtStatus(0)
   ‘ TextBox txtStatus(1)
   ‘ TextBox txtStatus(2)
   ‘ TextBox txtStatus(3)
   ‘————————————————————
   ‘ Notes: Needs to be upgraded to the 21st century!
   ‘¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

3. P.S.
   The routine automatically determines the type of procedure or class your cursor is in and adjusts the searches it does accordingly. Here’s the comment it automatically generates for a function.

   ‘¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
   ‘ Name: BoxGetLine
   ‘ VB Version: 6.00
   ‘ Author: You
   ‘ Copyright: ©2000 Your company All Rights Reserved
   ‘ Generated: Wednesday, April 12, 2000
   ‘ Purpose: Returns the text from the selected line of a multiline textbox
   ‘
   ‘ Inputs: Param Name Type Meaning
   ‘ —– —- —- ——-
   ‘ txtSource TextBox
   ‘ iLine Long
   ‘
   ‘ Returns: String – line of text
   ‘
   ‘ Global Used:
   ‘ Module used:
   ‘
   ‘ Called by: Method Component
   ‘ —— ———
   ‘ Header_Proc basRemBld
   ‘ Header_Declaration basRemBld
   ‘
   ‘ Calls: Method Component
   ‘ —— ———
   ‘ SendMessageString basRemBld
   ‘
   ‘ API calls: Function DLL/Lib Declared
   ‘ ——– ——- ——–
   ‘ SendMessageString user32 basRemBld
   ‘
   ‘ Modified:
   ‘ [04.11.00] Remember that with strong typing in the
   ‘ SendMessage API all parameters must be explicitly
   ‘ ByVal
   ‘————————————————————
   ‘ Notes:
   ‘ Size the Modifications and Notes text boxes on frmStatus
   ‘ according to how wide you want the comments to be. By using
   ‘ this method the lines are automatically sized correctly, rather than
   ‘ parsing the text word by word.
   ‘
   ‘ Don’t forget the SendMessageLong API declaration!
   ‘¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.