Home > Macros > Basics > FAQs FAQ - Excel Macros, Excel VBA Answers to Frequently Asked Questions (FAQs) about Excel macros and Excel VBA

Excel Macros - General Questions

1. Message - Enable or Disable a Macro
2. Message - Do you want to overwrite?
3. Macros trigger Event macros
4. Make macros faster
5. Confirmation before running macros
6. Hide process of running Excel macros?
7. Stop Recording toolbar disappeared
8. Show the Developer tab on Ribbon

When I open a 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.

   

3. 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)
4. Look for a Modules folder, and open it. (If there is no Modules folder, go to Step 6.)
5. 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.

   

6. Open the Microsoft Excel Objects folder.
7. 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)

   

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

   

8. Look for a Forms folder, and open it.
9. Delete any UserForms that it contains.
10. Look for a Class Modules folder, and open it.
11. Delete any class modules that it contains.

    

12. Close the Visual Basic Editor.
13. Save the changes to the workbook.

A macro to remove all VBA code in a workbook is available at Chip Pearson's web site:
    https://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 running 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 running 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)

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 later:

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. 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

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 run whenever a calculation occurs in the workbook.

Buttons to Run Excel Macros

When right-clicking on a button the "Assign Macro" command is not present.

Some buttons are Form Controls and other buttons are ActiveX Controls.

If you right-click a button from Form Controls, the popup menu includes the Assign Macro command.

If "Assign Macro" is not an option, then the button is from the ActiveX Controls. You can delete that button, and make a new one from the Form Controls.

Or, follow these steps to assign a macro to a button from the ActiveX Controls:

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 

Run Excel Macros Automatically

1. Run macro when cell value changes
2. Run macro when file is opened.
3. Show a userform when file is opened.
4. Call Excel macros from a worksheet formula?

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 a macro automatically when 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.

For more examples, see Chip Pearson's page on Excel Events and Event Procedures in VBA.

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

Use the one of the following macros. As noted above, the first example goes in the ThisWorkbook module.

Private Sub Workbook_Open() 
  UserForm1.Show
End Sub 

or

Sub Auto_open() 
  UserForm1.Show 
End Sub 

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.

For cells that are manually changed, you can use a worksheet change event to call a macro.

5. Macros and Security

1. Macros on protected worksheet.
2. Protect macros from being changed
3. Unprotect a VBA project using Excel macros

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 the Tools menu, and click VBAProject properties. On the Protection tab, click in the box to add a check mark for "Lock the project for viewing", Enter a password, and confirm the password, then click OK.

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

1. Find first empty cell column
2. Find cell below last entry in column
3. Find last row in worksheet
4. Do something to all selected cells.
5. Do something to all worksheets
6. Do something to all workbooks in a folder

  Go to Top

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 

  Go to Top

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

  Go to Top

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

  Go to Top

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

  Go to Top

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 

  Go to Top

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

  Go to Top

7. Working with Files in Excel Macros

1. Does file exist in specific folder?
2. Is a specific workbook open?
3. Does specific worksheet exist?
4. Select a file when macro runs
5. Select "Save As" location when macro runs.
6. Close a file/Excel with a macro

  Go to Top

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 

  Go to Top

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") 

  Go to Top

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 

  Go to Top

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 

  Go to Top

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. 

  Go to Top

8. Excel Online Macros

1. Can I use VBA macros online?
2. Is there a replacement for VBA macros online?

Can I use VBA macros in the online version of Excel?

No, you cannot run, edit or add VBA macros in the online version of Excel. However, you can open and edit a workbook that contains VBA macros, and the macros will remain in that workbook.

To edit or run the VBA macros, open the workbook in the Excel desktop application.  Go to Top

Is there a replacement for VBA macros, to run in online Excel?

You can build new Excel Add-ins, using HTML, CSS and JavaScript. These new add-ins will run across multiple platforms, such as Windows, Mac, iPad, and web browsers.

Learn to build and publish Excel Add-Ins from the documentation on the Microsoft site

Also see the Excel JavaScriptAPI series by Excel MVP, Charles Williams.

  Go to Top

More FAQs

Excel Tips by Harald Staff

FAQ Menu

Functions and Formats

Dates and Times

Macros

Pivot Tables

Excel Resources