Search Contextures Sites
FAQ - Excel Macros, Excel VBA
- Excel Macros
- When I open a file, it asks if I want to "Enable or Disable a Macro"-- there are no Excel macros
- Can my Excel Macros make Excel NOT ask "do you want to overwrite" type of questions?
- My Excel macros trigger my Event macros. How can I prevent that?
- My Excel macros clear a range and now take forever. How can I make them faster?
- Can I ask my user for confirmation before executing the Excel macros?
- Is there a way to hide the process of executing Excel macros?
- My Stop Recording toolbar has disappeared. How do I get it back?
- How do I get the Developer tab on my Excel Ribbon?
- User Defined Functions
- Buttons to Run Excel Macros
- Run Excel Macros Automatically
- How do I run an Excel macro every time a certain cell changes its value?
- I want Excel to run this macro automatically every time the Excel file is opened.
- I want to add a toolbar to my spreadsheet that when clicked, brings up Userform1.
- I want to show a userform each time my file is opened.
- Is it possible to call Excel macros from a worksheet formula?
- Excel Macros and Security
- Working with Ranges in Excel Macros
- How do I find the first empty cell in column A?
- How do I find the cell below the last entry in column A?
- How do I find the last row in my spreadsheet?
- I want to loop through all selected cells and perform an operation on each of them.
- I want to loop through all worksheets and perform an operation on each
- I want to loop through all workbooks in a folder and perform an operation on each
- Working with Files in Excel Macros
- How can I tell if a file exists in a specific folder?
- How can I tell if a specific workbook is open?
- How can I tell if a specific worksheet exists?
- I want to let the user select a file within my Excel macros.
- I want to let the user enter a "Save As" location in my Excel macros.
- How do I close a file/close Excel with a macro
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.
- Warning: As a precaution, you should make a backup copy of the file, before you remove any code.
- Right click on any sheet tab and choose View Code, to open the Visual Basic Editor.
- 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)
- Look for a Modules folder, and open it. (If there is no Modules folder, go to Step 6.)
- For each module in the folder:
- Right-click on the module name.
- Choose Remove Module1 (the name of your module may be different)
- Click No when asked if you want to Export.
- Open the Microsoft Excel Objects folder.
- For each worksheet, and for ThisWorkbook:
- Double-click on the object name, to open its code module. In this sample, you'd double-click on Sheet1 (Sheet1)
- On the keyboard, press Ctrl+A to select all the code (even if the code module looks empty)
- Press the Delete key.
- Look for a Forms folder, and open it.
- Delete any UserForms that it contains.
- Look for a Class Modules folder, and open it.
- Delete any class modules that it contains.
- Close the Visual Basic Editor.
- Save the changes to the workbook.
A macro to remove all VBA code in a workbook is available at Chip Pearson's web site:
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 = TrueApplication.EnableEvents = False 'code to clear, overwrite, delete, whatever goes here Application.EnableEvents = True
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 = TrueSub AskAndDo() If MsgBox("Are you sure ?", vbYesNo + vbQuestion) = vbNo Then Exit Sub Else 'Code goes here End If End Sub
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
To reactivate the Stop Recording toolbar in Excel 2003:
- Choose Tools | Macro | Record New Macro
- Click OK
- Choose View | Toolbars | Stop Recording
- 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.
In Excel 2010 and 2013:
- Right-click on the Ribbon, and click Customize the Ribbon.
- In the Customize the Ribbon list, add a check mark to the Developer tab.
- Click OK, to close the Excel Options window.
In Excel 2007:
- Click the Office button, then click Excel Options.
- Click the Popular category
- Add a check mark to Show Developer tab in the Ribbon.
- Click OK, to close the Excel Options window
2. User Defined Functions
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:
- Right-click on the button, and choose "View code"
- In the command button click code, call your macro like this:Private Sub CommandButton1_Click() Call Macro1 End Sub
4. Run Excel Macros Automatically
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
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.
Assign the toolbar button to this macro, which should be in a standard VBA module:Sub ShowForm () Userform1.Show End Sub
Combine the two solutions above:Private Sub Workbook_Open() UserForm1.Show End Sub
orSub 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.
In the Visual Basic Editor, go to Tools > VBAProject properties, lock the project for viewing, and enter a password.
You cannot. A workaround is to simulate keystrokes with the SendKeys method
6. Working with Ranges in Excel Macros
If ActiveSheet.UsedRange.Count < 2 Then MsgBox 1 Else MsgBox Columns("A:A").Find(What:="", LookAt:=xlWhole).Row End IfMsgBox Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
(This will return 2 on an empty column A)orSub 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 SubMsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).RowNote: You may want to reset the last cell
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 SubSub 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
Function bFileExists(rsFullPath As String) As Boolean bFileExists = CBool(Len(Dir$(rsFullPath)) > 0) End FunctionFunction bWorkbookIsOpen(rsWbkName As String) As Boolean On Error Resume Next bWorkbookIsOpen = CBool(Len(Workbooks(rsWbkName).Name) > 0) End FunctionFunction WksExists(wksName As String) As Boolean On Error Resume Next WksExists = CBool(Len(Worksheets(wksName).Name) > 0) End FunctionYou can call this function from your code, e.g.:Msgbox WksExists("Sheet19")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 SubSub 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 SubActiveWorkbook.Close savechanges:=False 'true ??? --will close the active workbookWorkbooks("mywkbk.xls").Close savechanges:=False 'true ??? --will close mywkbk.xlsThisWorkbook.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.
Original FAQs compiled by Harald Staff, Excel MVP 2000-2005. Updates and additions by Debra Dalgleish.
Contextures Inc., Copyright ©2014
All rights reserved.
Last updated: May 4, 2014 12:24 AM