Back to Home

Answers to Frequently Asked Questions about Microsoft Excel

Macros, VBA Functions

  1. Macros
    1. When I open a file, it asks if I want to "Enable or Disable a Macro"-- there are no macros
    2. Can my Macro make Excel NOT ask "do you want to overwrite" type of questions?
    3. Can I ask my user for confirmation before executing the macro?
    4. Is there a way to hide the process of executing macro?
    5. My Stop Recording toolbar has disappeared. How do I get it back?
  2. User Defined Functions
    1. I have a user defined function that doesn't recalculate.
  3. Buttons
    1. When right-clicking a button, "Assign Macro" is not in the menu.
  4. Run Macros Automatically
    1. How do I run a 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 a macro from a worksheet formula?
  5. Macros and Security
    1. Some of the code won't run without first unprotecting the worksheet.
    2. Is there a way to protect the macros I create so people can't see or alter them?
    3. How can I unprotect a VBA project using code?
  6. Working with Ranges
    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
    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 macro.
    5. I want to let the user enter a "Save As" location in my macro.
    6. How do I close a file/close Excel with a macro

1. Macros

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

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

  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.
 


  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.
 
  

 

  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)

     

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

 

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

Can I ask my user for confirmation before executing the macro? 

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

Is there a way to hide the process of executing macro? 

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:

  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.

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

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 it's from the Control Toolbox.

Choose "View code" and call your macro from it like this: 

Private Sub CommandButton1_Click() 
  Call Macro1 
End Sub

4. Run 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 a macro 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 macros I create so people can't see or alter them?

Go to Tools > VBAProject properties, lock the project for viewing, and enter a password.

How can I unprotect a VBA project using code?

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


6. Working with Ranges

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. 

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

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

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

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.

FAQs collected by Harald Staff, Excel MVP

Posted by Debra Dalgleish, Excel MVP
Excel Tips and Book List

Back to Home