Search Contextures Sites

 

 

FAQ - Excel Macros, Excel VBA

Menu: Frequently Asked Questions about Microsoft Excel

  1. Excel Macros
    1. When I open a file, it asks if I want to "Enable or Disable a Macro"-- there are no Excel macros
    2. Can my Excel Macros make Excel NOT ask "do you want to overwrite" type of questions?
    3. My Excel macros trigger my Event macros. How can I prevent that?
    4. My Excel macros clear a range and now take forever. How can I make them faster?
    5. Can I ask my user for confirmation before executing the Excel macros?
    6. Is there a way to hide the process of executing Excel macros?
    7. My Stop Recording toolbar has disappeared. How do I get it back?
    8. How do I get the Developer tab on my Excel Ribbon?
  2. User Defined Functions
    1. I have a user defined function that doesn't recalculate.
  3. Buttons to Run Excel Macros
    1. When right-clicking a button, "Assign Macro" is not in the menu.
  4. Run Excel Macros Automatically
    1. How do I run an Excel macro every time a certain cell changes its value?
    2. I want Excel to run this macro automatically every time the Excel file is opened.
    3. I want to add a toolbar to my spreadsheet that when clicked, brings up Userform1.
    4. I want to show a userform each time my file is opened.
    5. Is it possible to call Excel macros from a worksheet formula?
  5. Excel Macros and Security
    1. Some of the Excel macros won't run without first unprotecting the worksheet.
    2. Is there a way to protect the Excel macros I create so people can't see or alter them?
    3. How can I unprotect a VBA project using Excel macros?
  6. Working with Ranges in Excel Macros
    1. How do I find the first empty cell in column A?
    2. How do I find the cell below the last entry in column A?
    3. How do I find the last row in my spreadsheet?
    4. I want to loop through all selected cells and perform an operation on each of them.
    5. I want to loop through all worksheets and perform an operation on each
    6. I want to loop through all workbooks in a folder and perform an operation on each
  7. Working with Files in Excel Macros
    1. How can I tell if a file exists in a specific folder?
    2. How can I tell if a specific workbook is open?
    3. How can I tell if a specific worksheet exists?
    4. I want to let the user select a file within my Excel macros.
    5. I want to let the user enter a "Save As" location in my Excel macros.
    6. How do I close a file/close Excel with a macro

Learn how to create Excel dashboards.

1. Excel Macros

All of a sudden, when I open the file, it asks if I want to "Enable or Disable a Macro". There are no Excel macros in this workbook.

An Excel macro has been added and then removed, leaving an empty module. Empty modules trigger the macro query, as does an actual macro.

To see the steps for removing this warning, please watch this short video tutorial. The written instructions are below the video.


  1. Warning: As a precaution, you should make a backup copy of the file, before you remove any code.
  2. Right click on any sheet tab and choose View Code, to open the Visual Basic Editor.

    view code

  1. In the Project Explorer at the left of the screen, find the workbook. In the sample shown here, Book4 is the workbook name -- VBAProject (Book4)
  2. Look for a Modules folder, and open it. (If there is no Modules folder, go to Step 6.)
  3. For each module in the folder:
    1. Right-click on the module name.
    2. Choose Remove Module1 (the name of your module may be different)
    3. Click No when asked if you want to Export.

      remove module

  1. Open the Microsoft Excel Objects folder.
  2. For each worksheet, and for ThisWorkbook:

    1. Double-click on the object name, to open its code module. In this sample, you'd double-click on Sheet1 (Sheet1)

    sheet1 module

    1. On the keyboard, press Ctrl+A to select all the code (even if the code module looks empty)
    2. Press the Delete key.

    sheet 1 code

  1. Look for a Forms folder, and open it.
  2. Delete any UserForms that it contains.
  3. Look for a Class Modules folder, and open it.
  4. Delete any class modules that it contains.

    remove userform

  1. Close the Visual Basic Editor.
  2. Save the changes to the workbook.

A macro to remove all VBA code in a workbook is available at Chip Pearson's web site:
    http://www.cpearson.com/excel/vbe.htm

Can I have my Excel macros make Excel NOT ask "the file already exists, do you want to overwrite" type of questions?

Application.DisplayAlerts = False
  'code to save, overwrite, delete, whatever goes here 
Application.DisplayAlerts = True 

My macros trigger my Event macros. How can I prevent that?

Application.EnableEvents = False
  'code to clear, overwrite, delete, whatever goes here 
Application.EnableEvents = True 

My Excel macros clear a range and now take forever. How can I make them faster?

If you have Google Desktop Search installed, either turn it off in Excel or disable events:

Application.EnableEvents = False
  'code to clear a range 
Application.EnableEvents = True 

Can I ask my user for confirmation before executing Excel macros?

Sub AskAndDo() 
If MsgBox("Are you sure ?", vbYesNo + vbQuestion) = vbNo Then 
   Exit Sub 
Else
   'Code goes here  
End If

End Sub 

Is there a way to hide the process of executing Excel macros?

If you turn off ScreenUpdating, the Excel file won't show all the steps as the macro runs. It might also help the macro run faster.

Application.ScreenUpdating = False 
  'code here 
Application.ScreenUpdating = True  
 

My Stop Recording toolbar has disappeared. How do I get it back?


To reactivate the Stop Recording toolbar in Excel 2003:

  1. Choose Tools | Macro | Record New Macro
  2. Click OK
  3. Choose View | Toolbars | Stop Recording
  4. Click the Stop Recording button (the blue square)

stop recording

The next time you record a macro, the toolbar should automatically appear.

Note: When you're finished recording, click the Stop Recording button. If you close the toolbar by clicking the X, it will disappear again.

How do I get the Developer tab on the Excel Ribbon?

In Excel 2010 and 2013:

  1. Right-click on the Ribbon, and click Customize the Ribbon.
  2. In the Customize the Ribbon list, add a check mark to the Developer tab.
  3. add developer tab to ribbon

  4. Click OK, to close the Excel Options window.

In Excel 2007:

  1. Click the Office button, then click Excel Options.
  2. Click the Popular category
  3. Add a check mark to Show Developer tab in the Ribbon.
  4. Click OK, to close the Excel Options window

2. User Defined Functions

I have a user defined function that doesn't recalculate.

Include all the cells that your UDF depends on in the argument list. Or enter this as the first statement in your Function:

Application.Volatile 

This will cause the function to be executed whenever a calculation occurs in the workbook.

3. Buttons to Run Excel Macros

When a button is drawn onto a sheet the assign macro is not displayed. When right-clicking on the button the "Assign Macro" context menu item is not present.

There are buttons from the Forms toolbar and there are buttons from the Control Toolbox. If "Assign Macro" is not an option, then the button is from the Control Toolbox.

To assign a macro to a button from the Control Toolbox:

  1. Right-click on the button, and choose "View code"
  2. In the command button click code, call your macro like this:
Private Sub CommandButton1_Click() 
  Call Macro1 
End Sub 

view code button

4. Run Excel Macros Automatically

How do I run a macro every time a certain cell changes its value?

There is an event called Worksheet_Change which is triggered when a value is entered (it will not fire when a formula result changes). One of the arguments to this event is 'Target' which is a reference to what changed. Since this event will occur whenever a value changes - you can use the target to see if it is the cell you are interested in:

Private Sub Worksheet_Change(ByVal Target As Range) 
 If Intersect(Target, Range("C5")) Is Nothing Then 
  Exit Sub 
 Else 
  'The cell you are monitoring has changed! 
  'Do whatever you need to do... 
 End If 
End Sub 

I want Excel to run this macro automatically every time the Excel file is opened.

Place the code in (or call it from) the Workbook_open event of the ThisWorkbook module in the VB editor. Or simply name your macro Auto_Open. If you choose to use both then Workbook__open will run before Auto_open.

Auto_open will not run if the workbook is opened by another macro, you must use the RunAutoMacros method. Contrary; Workbook_open will run if the workbook is opened by a macro, you must use Application.EnableEvents = False to prevent it.

I want to add a toolbar to my spreadsheet that when clicked, brings up Userform1.

Assign the toolbar button to this macro, which should be in a standard VBA module:

Sub ShowForm () 
  Userform1.Show
End Sub 

I want to show a userform each time my file is opened.

Combine the two solutions above:

Private Sub Workbook_Open() 
  UserForm1.Show
End Sub 

or

Sub Auto_open() 
  UserForm1.Show 
End Sub 

See Chip Pearson's http://www.cpearson.com/excel/events.htm for detail and many more useful events.

Is it possible to call Excel macros from the condition true or false side of a worksheet formula? i.e. If(A2="OK",Run macro1,run macro2)

Basically, the answer is No. You can write functions in VBA that you can call from worksheet cells, but these functions can only return a value. They can't modify other cells or alter any part of the Excel environment. (You may be able to use a worksheet change event to call the macro.)

5. Macros and Security

Some of the functions that we want our macros to accomplish are not possible without first unprotecting the worksheet/workbook with the password.

Worksheets("MySheet").Unprotect password:="drowssap"
  'your code here 
Worksheets("MySheet").Protect password:="drowssap" 

Be sure to protect your macro code to hide the sheet password.

Is there a way to protect the Excel macros I create so people can't see or alter them?

In the Visual Basic Editor, go to Tools > VBAProject properties, lock the project for viewing, and enter a password.

How can I unprotect a VBA project using Excel macros?

You cannot. A workaround is to simulate keystrokes with the SendKeys method


6. Working with Ranges in Excel Macros

How do I find the first empty cell in column A?

If ActiveSheet.UsedRange.Count < 2 Then 
  MsgBox 1 
Else 
  MsgBox Columns("A:A").Find(What:="", LookAt:=xlWhole).Row              
End If 

How do I find the cell below the last entry in column A ?

MsgBox Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

(This will return 2 on an empty column A)

or
Sub FindLastCell()
  Dim LastCell As Range
  With ActiveSheet
    Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
    If IsEmpty(LastCell) Then
      'do nothing
    Else
      Set LastCell = LastCell.Offset(1, 0)
    End If
  End With
  MsgBox LastCell.Row
End Sub

How do I find the last row in my spreadsheet?

MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row 
Note: You may want to reset the last cell

I want to loop through all selected cells and perform an operation on each of them.

For many tasks, there might be a faster method than looping through all the cells, but if you need to loop, you can use For Each...Next. In this example, the font in each cell is changed to bold.

Sub DoOnSelection() 
Dim oCell As Range 
For Each oCell In Selection 
  oCell.Font.Bold = True 
Next 
End Sub

I want to loop through all worksheets and perform an operation on each of them (unprotecting or whatever).

Sub AllSheets() 
Dim ws As Worksheet 
For Each Ws In ActiveWorkbook.Worksheets 
  MsgBox ws.Name 
Next 
End Sub 

I want to loop through all workbooks in a folder and perform an operation on each of them.

Sub AllFolderFiles() 
Dim wb As Workbook 
Dim TheFile As String 
Dim MyPath As String 
MyPath = "C:\Temp" 
ChDir MyPath 
TheFile = Dir("*.xls") 
Do While TheFile <> "" 
  Set wb = Workbooks.Open(MyPath & "\" & TheFile)              
  MsgBox wb.FullName 
  wb.Close 
  TheFile = Dir 
Loop 
End Sub

7. Working with Files in Excel Macros

How can I tell if a file exists in a specific folder?

Function bFileExists(rsFullPath As String) As Boolean 
  bFileExists = CBool(Len(Dir$(rsFullPath)) > 0)
End Function 

How can I tell if a specific workbook is open?

Function bWorkbookIsOpen(rsWbkName As String) As Boolean 
On Error Resume Next 
  bWorkbookIsOpen = CBool(Len(Workbooks(rsWbkName).Name) > 0) 
End Function  

How can I tell if a specific worksheet exists?

Function WksExists(wksName As String) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function
You can call this function from your code, e.g.:
Msgbox WksExists("Sheet19") 

I want to let the user select a file within my Excel macros.

Sub SelectWebPageToOpen() 
Dim ThePage As Variant 
ThePage = _ 
 Application.GetOpenFilename("Webpage (*.htm*), *.htm*", _ 
  , "Pick one:")
 If ThePage = False Then 
  MsgBox "You cancelled" 
 Else 
  MsgBox "Do something with file " & CStr(ThePage)              
 End If 
End Sub 

I want to let the user enter a "Save As" location in my Excel macros.

Sub SelectSaveFileName() 
Dim TheFile As Variant 
TheFile = Application.GetSaveAsFilename("C:\Temp\File.xls", _ 
  "Workbook (*.xls), *.xls", , "Your choice:") 
If TheFile = False Then 
  MsgBox "You cancelled" 
Else
  MsgBox "Do something with file " & CStr(TheFile) 
End If 
End Sub 

How do I close a file/close Excel with a macro?

ActiveWorkbook.Close savechanges:=False 'true ???
             --will close the active workbook 
Workbooks("mywkbk.xls").Close savechanges:=False 'true ???
             --will close mywkbk.xls 
ThisWorkbook.Close savechanges:=False 'true ???
             --will close the workbook that holds the code that's running. 
Application.Quit will close all of Excel.
             --Be careful with this one. 

| Main Index

Original FAQs compiled by Harald Staff, Excel MVP 2000-2005. Updates and additions by Debra Dalgleish.

 

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: May 4, 2014 12:24 AM