Excel Is Our Friend – Part II

Blog Post created by rkoonce on Nov 11, 2013

Excel Is Our Friend – Part II


Before diving into our subject matter too deeply, I need to be true to my words (from my Welcome to Treasure Island”blog entry):

As I proceed, I hope that I remain mindful of the less experienced “islander”.

Below, I have included some background information, explanations of terminology, the “extra” information for the beginners and the perplexed. Recognizing each “island” as being different, this one needs a map. Some advanced readers may prefer to just skim this one and move on to Part III. Below, we will tweak our environment by changing the options available on the Excel ribbon and discuss controls we plan to use. Then, by adding a project reference, we will extend our functionality of our workbook.

Stepping out of the surf:

  • First things first: Create a new workbook.
  • Save it as a macro-enabled workbook. This will give it a filename extension of .xlsm, saved to “My Documents” (trusted location – more on that here).
Note: You should use a trusted location to store a file when you don't want that file to be checked by the Trust Center, or if you don't want it to open in Protected View.
  • How to: Show the Developer Tab on the Ribbon
  • http://msdn.microsoft.com/en-us/library/vstudio/bb608625.aspx
  • In Excel> On the File tab, choose the Options button.
  • Select the Customize Ribbon
  • Select the Developer Ribbon, so that it is displayed on the Toolbar Ribbon
  • How to: Using ActiveX controls
  • I prefer the ActiveX for a number of reasons - Access to properties (in the 2nd screenshot below) gives me visibility and greater control over the configuration. It provides a different user experience, overall (for the developer).
  • Clicking the Developer Ribbon > Insert > Choose the control > cursor changes to crosshairs > drag select the area for size and position on your sheet ExcelFig2.png
  • Clicking the Developer Ribbon >Design Mode > Right-click the control > Properties
  • Note the Name and Caption fields have different values, configurable.
  • It is best to name the button before adding the code.
    • If renamed, the code will lose its assignment to the button.
    • Double-clicking the renamed button takes you to a new procedure, abandoning the old procedure/
    • Renaming the old procedure should be avoided. Instead just move the code with cut/ paste.
  • Double-Click the ActiveX Commandbutton (in design mode) to create/ edit the code
    • And you will find yourself in the VB IDE, at the highlighted area, ready to add the code for the click event of your object (button)
    • At the top of the code pane, you will see 2 dropdown lists. 1 to select the object, the other selects the event.
    • If an event exists, it will show as bold
    • ExcelFig3.png
  • Back on the worksheet, You may notice the formula bar has “=EMBED("Forms.CommandButton.1","")”
    • This just Excel’s way of denoting the embedded object. Resembles a function, but it should be ignored.


· How to: Adding a reference to your project

Just as Excel is our friend, we can recruit “a friend of a friend” by adding a reference to our VBA project. Once we have this reference, we can utilize the functionality of the referenced project as well.

o In the Visual Basic Editor (VBE), add a reference for Microsoft Scripting Runtime (scrrun.dll) -> Tools menu> References (refer to screenshot below)

o We will utilize this reference in Part IV of the series



Note: Remember to exit Design Mode, by clicking it again, when finished.

So, with the preliminaries out of the way, we have established our base camp on the island. In Part III in this series, we will begin building upon the foundation we created above.