VBA & macros
Automate repeat work with macros and VBA code.
- Excel VBA: Protect a Sheet and Allow Filter and SortYes. In Excel VBA, protect the worksheet with AllowFiltering:=True and AllowSorting:=True. The filter controls must already exist, and sorting a protected sheet…
- Excel VBA Range Copy: Examples, Fixes, and Worksheet TipsTo copy cells with Excel VBA, use the Range.Copy method and specify a destination range; this copies the cells’ contents and formatting…
- Excel VBA Progress Bar Without a UserForm: 3 MethodsYes. Excel VBA can show progress without a UserForm by updating Excel’s status bar, writing progress to a worksheet cell, or resizing…
- Excel VBA Pivot Table Filter Multiple Values at OnceTo filter a standard Excel PivotTable to several items with VBA, clear the field’s existing filter and set each PivotItem’s Visible property…
- Excel VBA Pivot Table Source Types ExplainedIn Excel VBA, the main PivotTable source types are xlDatabase for a worksheet range or table, xlExternal for an external connection, xlConsolidation…
- Excel VBA Pivot Table Filter Criteria: ExamplesIn Excel VBA, apply PivotTable filter criteria through the relevant PivotField: use CurrentPage for a single report-filter selection, PivotFilters.Add for label or…
- Excel VBA: Open a Worksheet by Name or IndexTo open a worksheet in Excel VBA, activate it with a statement such as ThisWorkbook.Worksheets("Data").Activate; replace Data with the sheet’s tab name.…
- Excel VBA Pivot Charts: How to Create and Refresh ThemYes—VBA can refresh, filter, and manage Excel PivotCharts, and you can create one by selecting a PivotTable and choosing PivotTable Analyze >…
- Excel VBA: Open a Workbook in the Background, HiddenYes. In Excel VBA, open the file with Workbooks.Open, then set each window in the returned workbook’s Windows collection to Visible =…
- Excel VBA Open Workbook Read Only: Code and Key TipsYes. In Excel VBA, pass ReadOnly:=True to Workbooks.Open to open a workbook without saving changes back to its original file. For example:…
- Excel VBA: Open a Workbook Using a Variable File NameTo open an Excel workbook whose name or path is stored in a VBA variable, pass that variable to Workbooks.Open: for example,…
- Excel VBA: How to Open a WorkbookTo open a workbook with VBA, call Workbooks.Open and give it the file’s full path. For example, Set wb = Workbooks.Open(Filename:="C:\Reports\Monthly.xlsx") opens…
- Excel VBA Open Workbook Dialog: A Practical How-ToTo display a workbook picker in Excel VBA, call Application.GetOpenFilename, check whether the user cancelled, and pass the selected path to Workbooks.Open.…
- Excel VBA: Open a Workbook Without Updating LinksTo open an Excel workbook with VBA and skip updating its external workbook links, pass UpdateLinks:=0 to Workbooks.Open. For example, use Set…
- Excel VBA: Open Multiple Files in a Folder with a LoopTo open every Excel workbook in a folder, use VBA’s Dir function to find each matching filename, then call Workbooks.Open inside a…
- Excel VBA: Open a New Workbook with Workbooks.AddTo open a new workbook in Excel VBA, use Workbooks.Add. For example, Set newWb = Workbooks.Add creates and activates a workbook, while…
- Excel VBA: How to Open a UserFormIn Excel VBA, open a UserForm by calling its Show method from a macro or another procedure. Use a modal form to…
- Excel VBA MsgBox New Line: Add Line Breaks CorrectlyTo put a new line in an Excel VBA message box, join the text with vbCrLf: MsgBox "First line" & vbCrLf &…
- Excel VBA New Line in a String: Practical ExamplesTo add a new line to a string in Excel VBA, join the text with a newline constant: use vbCrLf for a…
- Excel VBA On Error MsgBox: A Practical GuideIn Excel VBA, use On Error GoTo HandlerName to send a runtime error to a handler, then show its details with MsgBox…
- Excel VBA MsgBox Examples: Buttons, Results, and Tips for MacrosExcel VBA’s MsgBox function displays a message and can return the button the user clicks. For example, use MsgBox "Report complete", vbInformation,…
- Excel VBA MsgBox Alternatives for Better User AlertsExcel VBA alternatives to MsgBox include the status bar for brief updates, a worksheet cell for persistent messages, and a modeless UserForm…
- Excel VBA MATCH Error Handling: Avoid Run-Time ErrorsFor safer Excel VBA MATCH error handling, use Application.Match and test its result with IsError before using it. Unlike Application.WorksheetFunction.Match, which raises…
- Excel VBA Range of Object Failed? Common Causes and FixesThe Excel VBA error “Method ‘Range’ of object ‘_Worksheet’ failed” usually means VBA could not resolve the address you gave to a…
- Excel VBA: Loop Through All Open Workbooks in a MacroTo loop through every workbook currently open in desktop Excel, use a For Each loop over Application.Workbooks. Declare a Workbook variable, then…
- Excel VBA Macro to Send a Worksheet File via TeamsYes—but Excel VBA has no built-in command that sends a worksheet directly to Microsoft Teams. A practical Windows method is to export…
- Excel VBA: List All Sheets in a WorkbookTo list every sheet in the workbook that contains your VBA code, loop through its Sheets collection and print each name in…
- Excel VBA List Worksheet Names: Methods and ExamplesTo list worksheet names with Excel VBA, loop through the workbook’s Worksheets collection and read each sheet’s Name property. You can print…
- Excel VBA Long Strings: Limits and Practical FixesIn Excel VBA, use a variable-length String to hold long text; it can theoretically contain about 2 billion characters, although available memory…
- Excel VBA: Keep a UserForm on TopTo keep an Excel VBA UserForm above other windows on Windows, call the Windows API function SetWindowPos with the HWND_TOPMOST option when…