Answers to Frequently Asked Questions (FAQs) about Excel macros and Excel VBA
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.
A macro to remove all VBA code in a workbook is available at Chip
Pearson's web site:
Application.DisplayAlerts = False 'code to save, overwrite, delete, whatever goes here Application.DisplayAlerts = True
Application.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 = True
Sub 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:
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.
Include all the cells that your UDF depends on in the argument list. Or enter this as the first statement in your Function:
This will cause the function to be executed whenever a calculation occurs in the workbook.
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:
Private Sub CommandButton1_Click() Call Macro1 End Sub
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.
For more examples, see Chip Pearson's page on Excel Events and Event Procedures in VBA.
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
Sub Auto_open() UserForm1.Show End Sub
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.
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 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.
You cannot. A workaround is to simulate keystrokes with the SendKeys method
If ActiveSheet.UsedRange.Count < 2 Then MsgBox 1 Else MsgBox Columns("A:A").Find(What:="", LookAt:=xlWhole).Row End If
MsgBox Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
(This will return 2 on an empty column A)
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
Note: 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
Sub AllSheets() Dim ws As Worksheet For Each Ws In ActiveWorkbook.Worksheets MsgBox ws.Name Next End Sub
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
Function bFileExists(rsFullPath As String) As Boolean bFileExists = CBool(Len(Dir$(rsFullPath)) > 0) End Function
Function bWorkbookIsOpen(rsWbkName As String) As Boolean On Error Resume Next bWorkbookIsOpen = CBool(Len(Workbooks(rsWbkName).Name) > 0) End Function
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.:
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
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
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.
Last updated: January 26, 2017 4:47 PM
Contextures RSS Feed