Home 2013 Archives for February 2013

Archives for February 2013

February 12, 2013 Leave a Comment

How to Edit an Excel Macro that You Recorded

Edit VBA Code for MacroThis is Part 3 in my series, “How to Record, Run, and Edit Macros in Excel.” In this episode, I demonstrate how to open up the VBA (Visual Basic for Applications) Code window and then:

  • Step Into each line of the code – using the F8 Keyboard Shortcut – to examine how the Macro Behaves
  • Use the ‘ (apostrophe) to “remark out” one line of the code. This will quickly change the macro to use Relative Cell Referencing rather than Absolute Cell Referencing!
  • Edit one line of the VBA Code to change the “text” that you want your macro to enter.

I created this series of Excel Tutorials to assist my viewers to “get started on the right path” when they first start to experiment with the power of Macros in Excel. I welcome your feedback on this – or any – Excel tutorial that I have published.

Step Into a Macro

When you run a Macro, it is impossible to “trouble shoot” or examine the individual actions that occur. In this lesson, I demonstrate how to use “Step Into” mode – with the keyboard Shortcut F8 – to run each line of the VBA Code step-by-step. Using Step Mode with any macro is a great way to learn how top efficiently write, record, or edit a macro.

Secure Online Shopping for Excel Training Resources

I welcome you to visit my Secure Online Shopping Site – http://shop.thecompanyrocks.com – where you can learn about the range of video training resources that I offer.

Watch Excel Tutorial in High Definition

Follow this link to watch my tutorial in High Definition on my YouTube Channel – DannyRocksExcels.

View Excel Video Tutorial Now

Link to this post!
Filed Under: Excel Training, Excel VBA Macros Tagged: , , , , , ,
February 6, 2013 Leave a Comment

Excel Macros: When to Use Relative Cell Referencing When Recording

This is Part 2 of my new series of Excel Tutorials: “Recording, Running, and Editing Excel Macros.” In this episode, I demonstrate how and when to use either Relative Cell Referencing or Absolute Cell Referencing.

Best Practice: Chose Relative Cell Referencing

Chose Type of Cell Referencing for Macro

Default Setting for Recording Macros – Absolute Cell Referencing

In my experience, the first major mistake Excel Users make when recording a Macro – they neglect to turn on Relative Cell Referencing. In the majority of Macros, you are better off using Relative Cell Referencing. There are a few – specialized – reasons to use Absolute Cell Referencing. However, they are in the distinct minority.

Select Starting Cell Before Recording a Macro

Once again, in my experience, I find that too many Excel users fail to select their “Starting Cell” before they click on the Record Macro Button. My Best Practice: “Minimize Cell Movements When Recording a Macro!”

Whenever Possible Use Ctrl + Enter When Recording a Macro

My Best Practice: “Minimize Cell Movements When Recording a Macro.” I recommend that your use Ctrl + Enter when entering or editing a value or formula in a cell. This procedure will keep the focus on the active cell. This will also minimize extraneous cell movements while recording your Macro. When you take steps to avoid extraneous cell movements, your Macro will be easier to edit or update.

Demonstration of Absolute vs. Relative Cell Referencing in a Macro

Watch my video tutorial to see the very different results that you get depending upon the type of cell referencing that you use when recording a Macro.

Secure Online Shopping at The Company Rocks

I invite you to visit my secure online shopping site – http://shop.thecompanyrocks.com  Take a few minutes to look at  – and , hopefully, purchase – one of the many video training resources that I offer. I guarantee that you will be 100% satisfied with my training materials. If not, I will refund your purchase with no questions asked!

Watch My Video in High Definition

Follow this link to watch my Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Play My Video Now

Link to this post!
Filed Under: Excel Training, Excel VBA Macros Tagged: , , , , , , ,
February 4, 2013 Leave a Comment

The 4 Steps to Take Before Recording an Excel Macro

Prepare to Record a Macro

The 4 Steps to Take Prior to Recording a Macro

With this video tutorial, I begin a new series – “How to Record, Run, and Edit a Macro in Microsoft Excel. I’ve designed this series to introduce Intermediate Level Excel Users to the “ins and out” of working with Macros.

Here are the “4 Steps to Take Before Recording an Excel Macro:”

  1. Add the Developer Tab to the Ribbon. I show you how to do this in both Excel 2010 and Excel 2007.
  2. Set the Proper Level for Macro Security. You do this from the Trust Center. My recommendation is to choose “Disable Macros with Notification.” – While this is a somewhat cryptic description, it is similar to the “Medium Level” Macro Security Settings in Excel 2003.
  3. Establish Trusted Locations. Here, you inform Excel that the Workbooks in this Folder (and Subfolders) contain Macros that are “safe” to open with the Macros Enabled.
  4. Unlike earlier versions of Excel, if a workbook contains a Macro, you MUST save it with the file extension .XLMS (Macro-enabled Workbook.) This applies to Excel Version 2007 and higher.

By taking these 4 steps, you will ensure that the Macros that you create will open safely and that they will run smoother.

Shop at The Company Rocks Online Website

At my secure online shopping website – http://shop.thecompanyrocks.com – I offer a wide range of video training resources. I invite you to click on this link and take a look at my products.

Watch Video in High Definition

Follow this link to view my Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Watch Video Now

Link to this post!
Filed Under: Excel Training, Excel VBA Macros Tagged: , , , , , ,