What Are VB Projects, Objects, Forms, Modules and Procedures?

In the Excel Visual Basic Editor (Alt+F11), display the Project Explorer (Ctrl+R). Usually (and in this picture), it appears at the top left, under the toolbars: In Project Explorer, you will see a VBAProject for every Excel file that is open.  The list will include the hidden Personal Macro workbook, Personal.xlsb, if you have personal macros….

|

How to Get Help in the Excel Visual Basic Editor

At this point, you might be wondering, “What if I get stuck?”  Fear not, help is at hand!  Microsoft offers extensive online documentation of the VB language.  To be fair and warn you, it’s often not that useful.  You might find more assistance if you Google for your topic.  There is a lot of programming…

|

How to Record a Macro in Excel (or Microsoft Word)

The easiest way to create a macro is: The instructions below are for Excel. Word is similar, but has its own quirks (and no “Absolute or Relative”). If you want to look at programming basics first, see this previous post. 1.     Recording a Macro Absolute or Relative What does that mean?  When you record Absolute,…

|

FORMULATEXT to Display the Formula in a Cell

Here is a function that displays the formula in another cell: FORMULATEXT(reference). The argument, “reference”, is the cell address that contains the formula you want to display. This is most useful for documentation purposes.  For example, when you want to print out a spreadsheet with its formulas. If you have spent much time on this…

|

Should I use a VBA Macro Function or Call a Worksheet Function?

If the function you want to use exists in Excel VBA, use that.  It will be faster and cleaner, and work in all Microsoft Office programs, not only in Excel.  Here is a full list of Excel VBA Functions.  If the function you want is not there, but exists in the Worksheet, you may be…

| |

How to Use a Worksheet Function in a Macro: Proper Case Names

In some cases, VBA does not have a specific function, but the Excel Worksheet does. This Excel Visual Basic (VBA) code example demonstrates: How to call that Worksheet Function from a Macro. In this particular example, how to call the Worksheet Function =PROPER() from VBA. The function to downcase parts of surnames that normally appear…