VBA & macros
Automate repeat work with macros and VBA code.
- VBA Dictionary Example: Create, Find, and Count ItemsA VBA dictionary stores unique keys with associated values, making it useful for counting repeated entries or looking up information by name.…
- VBA Dictionary Documentation: Setup, Methods, Properties & ExamplesFor VBA Dictionary documentation, start with Microsoft’s Dictionary object reference: it describes the Scripting.Dictionary object, which stores unique keys and their associated…
- VBA Dictionary Early Binding: Setup, Benefits, and LimitsYes. To use a VBA Dictionary with early binding in Excel for Microsoft 365 on Windows, add a reference to Microsoft Scripting…
- VBA Dictionary Count: What It Returns and How to Use ItIn VBA, read a dictionary’s item count with its Count property: dict.Count. It returns the number of key–item pairs currently stored, so…
- VBA Dictionary Declaration: Setup, Methods, and ExamplesTo declare a VBA Dictionary in Excel for Microsoft 365 on Windows, use Dim dict As Object followed by Set dict =…
- VBA Dictionary Case Insensitive? How CompareMode WorksYes. A VBA Scripting.Dictionary can treat keys as case-insensitive: set its CompareMode property to vbTextCompare before adding any keys. With that setting,…
- VBA Dictionary as a Public Constant? What Works in ExcelNo: a VBA Scripting.Dictionary cannot be declared as a Public Const, because VBA constants must hold compile-time values and a dictionary is…
- VBA Dictionary Add Key: Syntax, Examples, and Common ErrorsTo add a key and its associated value to a VBA Dictionary, call the dictionary’s Add method: dict.Add "key", value. For example,…
- VBA Dictionary Add Item: Syntax and ExamplesIn Excel VBA, add an item to a Scripting.Dictionary with dict.Add key, item; create the dictionary first, and make sure the key…
- VBA Destination Range: How to Set It in Excel MacrosA VBA destination range is the cell or group of cells where a macro puts, copies, or otherwise writes data. In practice,…
- VBA UserForm Design: Build a Practical Excel FormTo design a VBA UserForm in Excel, open the Visual Basic Editor, choose Insert > UserForm, add controls from the Toolbox, and…
- VBA Convert String to Uppercase with UCase ExamplesUse VBA’s UCase function to convert a string to uppercase; for a result explicitly typed as a String, use UCase$. For example,…
- VBA Convert String to Integer: CInt, CLng, and ErrorsIn VBA, convert a numeric string with CInt for a 16-bit Integer or CLng for a 32-bit Long: for example, result =…
- VBA Coding Dictionary: How the Dictionary Object WorksA VBA Dictionary is an object that stores pairs of keys and values, letting an Excel macro look up a value by…
- VBA Convert String to Date: Safe Parsing in Excel MacrosIn VBA, use CDate to convert a string that VBA recognizes as a date, or DateValue when you want only the date…
- VBA While Loop: Syntax, Examples, and Safe UseA VBA While loop repeats a block of code as long as a condition is true. In Excel, you can write it…
- VBA Code to Sort Data by Multiple ColumnsTo sort Excel rows by multiple columns with VBA, use the worksheet’s Sort object, add one sort field for each key in…
- VBA Code to Update a PivotTable in Excel AutomaticallyTo update one PivotTable in Excel for Microsoft 365 on Windows, call its RefreshTable method from a VBA macro. For example, assign…
- VBA Code to Sort Excel Data by One or More ColumnsTo sort Excel rows with VBA, define the complete data range, add one or more sort keys, set the header option, and…
- VBA Code to Send an Email With an AttachmentTo send an email with an attachment from Excel VBA on Windows, create an Outlook mail item, add the file with .Attachments.Add,…
- VBA Code to Refresh Pivot Tables Automatically in ExcelTo refresh every PivotTable in the workbook that contains your VBA, run a macro that loops through each worksheet and calls RefreshTable.…
- VBA Code to Display a Graph on a UserFormUse an Excel chart on a worksheet as the graph source, export it as a JPEG, then load that image into an…
- VBA Code to Sort Data in Excel: Examples and TipsTo sort Excel data with VBA, define the complete data range, add the column or columns to sort by, set the sort…
- VBA Code to Save a Workbook in Excel: Practical ExamplesTo save the workbook that contains your VBA code, use ThisWorkbook.Save; to save the active workbook, use ActiveWorkbook.Save. To save a workbook…
- VBA Code for UserForm Data Entry: Build a Reliable FormTo enter records through an Excel VBA UserForm, create text boxes and a combo box on the form, validate the fields in…
- VBA Code for UserForm: Build an Excel Data Entry FormTo create a VBA UserForm in Excel, add a UserForm in the Visual Basic Editor, place controls on it, and write event…
- VBA Code for Sending an Email from Excel with OutlookIn Excel for Microsoft 365 on Windows, VBA can create an email through classic Outlook using Outlook’s object model. The example below…
- VBA Dictionary: How to Create and Use OneA VBA Dictionary stores pairs of unique keys and associated values, letting you look up, count, or group data by a key.…
- VBA Code to Add a Worksheet in Excel: Safe ExamplesTo add a worksheet with VBA in Excel, use Worksheets.Add. For example, this macro adds a sheet after the workbook’s last worksheet…
- VBA Close Workbook Without Saving: Safe MethodsTo close a workbook and discard its unsaved changes in Excel VBA, call its Close method with SaveChanges:=False, for example: wb.Close SaveChanges:=False.…