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

Write a Custom Excel Function to Link Worksheet Tab Name to a Cell

Custom Functions in Excel

Custom Excel Functions

In response to many viewer requests, I am creating a two-part series of video tutorials that show you how to create links between the name of an Excel Worksheet Tab and a cell on that worksheet.

Here, in Part One, I demonstrate how to write a Custom Excel Function to capture the name of the worksheet tab in a cell.

Write Custom Excel Functions

You write Custom Excel Functions in the Visual Basic Editor. One way to open the Visual Basic Editor is to use the keyboard shortcut Alt + F11. Here are the elements that I use in this lesson:

  • Open the Project Explorer Pane (Keyboard Shortcut Ctrl + R).
  • Insert a Module to contain the VBA Code that you write for your Custom Function
  • Open the Code Pane (Keyboard Shortcut F7).

Write the Code for the Custom Function

In its simplest form, this is all of the code that you need for this Custom Excel Function:

Function SHEETNAME2() As String
SHEETNAME2 = Application.Caller. Parent.Name
End Function

 

That’s it!

Automatically Update Results of Custom Functions

A major difference between Custom Functions that you write and Excel’s Built-in Functions is that by default, the result of a Custom Function does not automatically update when values or references change. If you want your Custom Function to automatically update whenever the worksheet changes, add this line of code to your Custom Function in the Module:

Application.Volatile True

Source for this Code

I found the code for this Custom Excel Function in John Walkenbach’s Book, “Excel 2007 Formulas. He is an Excel Expert and I admire his writing style. You can check out his reources at this site:

http://spreadsheetpage.com/

Watch this Video in High Definition on YouTube

Click on this link to view this Excel Tutorial on my YouTube Channel – DannyRocksExcels.

Watch Part 2 of This Series

Here is the link to Part 2 in this Series. In this lesson I show you how to write an Excel Macro in VBA to update the Name of the Worksheet Tab based upon the value in a cell on the worksheet.