Convert to Uppercase – Excel VBA Codes: To insert vba code for worksheet event procedures, right click on the Worksheet name tab, select View Code and paste the code into the Code Window that appears at right. Convert Text and Cells with Formulas to UpperCase – worksheet event procedure Private Sub...
Category: Excel VBA
VBA codes to determine Last Used Row & Last Used Column in Excel
VBA Codes – Find last used row in a worksheet or in a specific column; Find last used column in a worksheet or in a specific row. Related Links: Find last used row number & cell address with excel functions. ———————————————————————————————————– Contents: Use End(xlUp) to determine Last Row with Data, in one...
Change Font Color in Cells with VBA
VBA Code for customized Font Color in Cells Code for different font color in cells having “formulas”, “formulas and numbers” and “constants”. Sub FontColorFormulas1() ‘Worksheet Code for different font color in cells having formulas, formulas and numbers and constants. Run this code to change font colors in Used Range in Active Sheet.Dim...
Find Smallest and Largest Value in a Range with VBA
Finding Smallest and Largest Value with VBA. Using ParamArray to find Minimum Value. Related Links: Remove Duplicates in a range, using “Find ‘Smallest’, ‘Largest’, ‘K-th Smallest’ and ‘K-th Largest’ Numbers in a Range, with Excel Functions. ———————————————————————————————– Contents: Determine smallest value in range Determine largest value in range Determine smallest value in each non-blank row...
Application.OnTime VBA, Schedule Excel to Run Macros at Periodic Intervals or a Specified Time
Scheduling Excel to Run a Procedure at periodic intervals or at a specific time of day, with the OnTime Method. Automatically run macros. Excel Application.OnTime Method – Scheduling OnTime Events Use the Application.OnTime Method to run a procedure at specified intervals or at a specific time of day. Syntax: ApplicationObject .OnTime(EarliestTime,...
Declaring Variables, Using Dim Statement, Variable Names & Variable Data Types in VBA
Declaring Variables, Using Dim Statement, Variable Names & Data Types in Excel VBA ————————————————– Contents: Declaring Variables Dim and ReDim Statements Variable Names Variable Data Types ————————————————– Declaring Variables A variable is a named storage location used to store temporary values or information for use in execution of the code. In your vba program, a...
Find Method in Excel VBA, Find Multiple Occurrences, Find Method to do Vlookup, Find Method to Search Date
Find Method in Excel VBA, Find Multiple Occurrences, Use Find Method to do Vlookup, Find Method to Search Date ———————————————————————— Contents: Find Method in Excel VBA Find multiple occurrences of an item or value in a range Using Find Method to do VLookUp Using Excel Find Method to Search for a Date ———————————————————————— Find Method...
Excel VBA Events, Event Procedures (Handlers), Triggering a VBA Macro
Excel VBA Events, Event Procedures (Handlers), Triggering a VBA Macro Related Links: Worksheet event – Change Event – is illustrated in chapter: Worksheet Change Event in VBA and Preventing Event Loops; Worksheet event – Selection Change Event – is illustrated in chapter: Worksheet Selection Change Event, Excel VBA; Event Procedures of ActiveX...
Worksheet Change Event, Excel VBA
Worksheet Change Event in VBA and Preventing Event Loops Related Links: Excel VBA Events, Event Handlers, Trigger a VBA Macro. Worksheet Selection Change Event, Excel VBA. ———————————————————————— Contents: Worksheet_Change Event Preventing Event Loops with Application.EnableEvents = False ———————————————————————— Worksheet_Change Event: You can auto run a VBA code, when...
Worksheet Selection Change Event, Excel VBA
Worksheet Selection Change Event in Excel VBA and Preventing Event Loops Related Links: Excel VBA Events, Event Handler, Trigger a VBA Macro. Worksheet Change Event in VBA and Preventing Event Loops. ———————————————————————— Contents: Worksheet_SelectionChange Event Preventing Event Loops with Application.EnableEvents = False ———————————————————————— Worksheet_SelectionChange Event: You can auto...
Excel VBA – Convert Excel Column Number to Column Letter, and Column Letter to Column Number
Excel VBA – Convert Excel Column Number to Column Letter, and Column Letter to Column Number Related Links: VBA Chr & Asc functions explained; corresponding Excel CHAR and CODE functions. ——————————————————————————————- Contents: VBA Codes to Convert Excel Column Number to corresponding Column Letter VBA Codes to Convert Excel Column Letter to corresponding...
Run VBA Macros on Protected Worksheet; Unprotect & Protect, UserInterfaceOnly argument; Worksheet.Protect Method
Run VBA Macros on Protected Worksheet ——————————————————————————————- Contents: Unprotect & Protect Error Handler UserInterfaceOnly Run macro on protected worksheet, code remaining visible & editable, but protection password hidden Using the UserInterfaceOnly argument to Enable Auto Filter Using UserInterfaceOnly argument to Allow Grouping Worksheet.Protect Method ——————————————————————————————- If you run macros on a protected...
Excel VBA Dates & Time, Format Function, User Defined Date, Number & String Formats
Excel VBA Dates & Time, Format Function, User Defined Date, Number & String Formats ————————————————————————————————————— Contents: Excel VBA Dates & Time Format Function in VBA Named Date/Time Formats Named Numeric Formats Characters used to create User-Defined Number Formats with the VBA Format Function Characters used to create User-Defined Date & Time Formats with the VBA...
Excel VBA Date & Time Functions; Year, Month, Week & Day Functions
Excel VBA Date & Time Functions; Year, Month, Week & Day Functions —————————————————– Contents: VBA DateSerial Function VBA DateValue Function VBA TimeSerial Function VBA TimeValue Function VBA IsDate Function VBA CDate Function VBA DateAdd Function VBA DateDiff Function VBA DatePart Function VBA Date Function VBA Now Function VBA MonthName Function VBA Day Function VBA Month...
Custom Number Formats, Date & Time Formats in Excel & VBA; NumberFormat property
Custom Number Formats, Date & Time Formats in Excel & VBA; NumberFormat property ————————————————————————— Contents: Custom Number Formats in Excel / VBA Excel Home Tab, Format Cells dialog box VBA, NumberFormat property Number Codes Text Codes Specify Font Color Four Sections of Code Specify Conditions Custom Date & Time Formats in Excel / VBA...
Excel VBA Loops, with examples. For Loop; Do While Loop; Do Until Loop.
Excel VBA Loops, with examples. For Loop; Do While Loop; Do Until Loop. ——————————————————————————————– Contents: The For Loop The For … Next Statements The For Each … Next Statements The Do While Loop The Do While … Loop Statements; The Do … Loop While Statements The Exit Do Statement The Do Until Loop The Do...
Conditional Statements in Excel VBA – If…Then…Else Statements (VBA)
Conditional Statements in Excel VBA Related Links: 1. Excel IF Function and IF Statements. 2. Select…Case Statement (VBA). There are primarily two Conditional Statements in VBA: If…Then…Else Statements and Select…Case Statement. In both of these, one or more conditions are evaluated and a block of code is executed depending on the result of the evaluation....
Conditional Statements in Excel VBA – Select…Case Statement (VBA)
Conditional Statements in Excel VBA Related Link: If…Then…Else Statements (VBA). There are primarily two Conditional Statements in VBA: If…Then…Else Statements and Select…Case Statement. In both of these, one or more conditions are evaluated and a block of code is executed depending on the result of the evaluation. Select…Case Statement (VBA) If…Then…Else Statements (VBA) ———————————————————————————————– Contents:...
Excel VBA: ActiveX Controls, Form Controls & AutoShapes on a Worksheet
Excel VBA – ActiveX Controls, Form Controls & AutoShapes on a Worksheet ———————————————————————————————– Contents: Forms in Excel ActiveX controls, Form controls & AutoShapes in an Excel Worksheet Shape object & OLEObject object ActiveX Controls in VBA Add an ActiveX Control, a Form Control or an AutoShape, in Worksheet Illustrating Form Controls, ActiveX Controls & AutoShapes:...
UserForms in Excel VBA – UserForm Basics; Add Controls dynamically at run-time using the Add Method; UserForm Controls (A Snapshot)
UserForms in Excel VBA – UserForm Basics; Add Controls dynamically at run-time using the Add Method; UserForm Controls (A Snapshot) ———————————————————————– Contents: Excel UserForms Create a UserForm UserForm Controls – A SnapShot UserForm Basics Add Controls to a UserForm ———————————————————————– Note: In below given examples, vba codes are required to be entered in the Code...
Excel UserForm and Controls – Properties; Setting control properties at design-time or run-time.
UserForm and Controls – Properties ———————————————————————- Contents: UserForm and Controls Basic Properties common to the UserForm and most Controls Other Properties common to the UserForm and most Controls Specifying Color in Properties Window Applicability of Properties to UserForm and its Controls – A SnapShot ———————————————————————- UserForm acts as a container in which...
Excel UserForm Controls – Label, TextBox and CommandButton
UserForm Controls – Label, TextBox and CommandButton UserForm acts as a container in which you add multiple ActiveX controls, each of which has a specific use and associated properties. By itself, a UserForm will not be of much use unless ActiveX controls are added to it which are the actual user-interactive objects. Using ActiveX Controls...
Excel UserForm Controls – ComboBox and ListBox: AddItem Method, BoundColumn, List Property, ListIndex, RowSource, Selected Property, …
UserForm Controls – ComboBox and ListBox ———————————————————- Contents: Difference between ListBox and ComboBox Key Properties of ComboBox and ListBox Add Items/Data to (Populate) a ListBox or ComboBox Extract ListBox & ComboBox Items, with VBA Delete ListBox rows using the RemoveItem Method ———————————————————- UserForm acts as a container in which you add multiple...
Excel UserForm Controls – CheckBox, OptionButton and ToggleButton
UserForm Controls – CheckBox, OptionButton and ToggleButton UserForm acts as a container in which you add multiple ActiveX controls, each of which has a specific use and associated properties. By itself, a UserForm will not be of much use unless ActiveX controls are added to it which are the actual user-interactive objects....
Excel UserForm Controls – Frame, MultiPage and TabStrip; group OptionButtons; create a wizard using MultiPage control
UserForm Controls – Frame, MultiPage and TabStrip UserForm acts as a container in which you add multiple ActiveX controls, each of which has a specific use and associated properties. By itself, a UserForm will not be of much use unless ActiveX controls are added to it which are the actual user-interactive objects. Using ActiveX Controls...
Excel UserForm Controls – ScrollBar and SpinButton; create Mortgage Calculator using ScrollBar
UserForm Controls – ScrollBar and SpinButton UserForm acts as a container in which you add multiple ActiveX controls, each of which has a specific use and associated properties. By itself, a UserForm will not be of much use unless ActiveX controls are added to it which are the actual user-interactive objects. Using ActiveX Controls on...
Excel UserForm Controls – Image and RefEdit; use LoadPicture property with GetOpenFilename Method
UserForm Controls – Image and RefEdit UserForm acts as a container in which you add multiple ActiveX controls, each of which has a specific use and associated properties. By itself, a UserForm will not be of much use unless ActiveX controls are added to it which are the actual user-interactive objects. Using ActiveX Controls on...
Excel VBA Arrays: One-Dimensional & Multi-Dimensional, Dynamic Arrays, ReDim Statement, Lower & Upper Bound, 200-Year Calendar in Excel, ParamArray
Excel VBA Arrays: Declaring Arrays, One-Dimensional and Multi-Dimensional Arrays, Fixed-Size and Dynamic Arrays, ReDim Statement, Lower Bound and Upper Bound, Array Size, Create a 200-Year Calendar with Excel VBA, ParamArray (Parameter Array) ————————————————————————————————————— Contents: VBA Arrays Declare Arrays, Sizing an Array, Determining the Upper and Lower Bounds One-Dimensional and Multi-Dimensional Arrays Fixed-size and Dynamic Arrays;...
200-Year Calendar in Excel VBA, Free Download
200-Year Calendar in Excel VBA The Calendar has been created with vba code, in excel. It spans 2 centuries, covering years 1900 to 2100. Select the year from the drop-down list (yellow color cell), and the Calendar will auto-generate for that year, showing all 12 months. See below for a snapshot, and...
Create an Excel Pivot Table report using VBA
Create an Excel Pivot Table report using VBA Related Links: 1. Create an Excel Pivot Table report using VBA 2. Referencing an Excel Pivot Table Range using VBA 3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA 4. Excel Pivot Table Address, Location, Move & Copy using VBA 5. Excel...
Referencing an Excel Pivot Table Range using VBA
Referencing an Excel Pivot Table Range using VBA This section explains how to access, reference, select or use a specific part of a Pivot Table, be it the Field or Data Labels, Row or Column Ranges, the Data or Values Area, the Page Area, specified cells or range in a PivotTable report or the entire...
Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA
Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA This section explains how to access, reference, select or use Pivot Fields and Pivot Items in a Pivot Table, viz. Pivot Field, Column Field, Row Field, Data Field, Page Field, Pivot Items in a Field, Hidden Fields, … Related Links: 1. Create...
Excel Pivot Table Address, Location, Move & Copy using VBA
Excel Pivot Table Address, Location, Move & Copy using VBA This section explains how to get the Address of a PivotTable in a worksheet; Move PivotTable to a new location; Copy a PivotTable and paste as values or paste as a PivotTable. Related Links: 1. Create an Excel Pivot Table report using VBA 2. Referencing...
Excel Pivot Table Layout and Design, using VBA
Excel Pivot Table Layout and Design, using VBA Layout Forms, Subtotals, GrandTotals, Number Format, Pivot Table Styles & Style Options, Layout & Display options, … Related Links: 1. Create an Excel Pivot Table report using VBA 2. Referencing an Excel Pivot Table Range using VBA 3. Reference Pivot Fields and Pivot Items in an Excel...
Excel Pivot Table Properties & Settings, using VBA
Excel Pivot Table Properties & Settings, using VBA Excel Pivot Table: Get data from a PivotTable, DrillDown of PivotTable Fields, Display & Sort the PivotTable FieldList, Return the Parent object, Manual Update, Name of a PivotTable object, Print Settings, Save the PivotCache data, create a new PivotTable for each Page field item, return the Data...
Refresh Excel Pivot Table and Cache of PivotTable, using VBA
Refresh Excel Pivot Table and Cache of PivotTable, using VBA Refresh Data in a PivotTable report: You have different ways to refresh and update data in a PivotTable report with any changes made in the source data. This section explains how to update or refresh a PivotTable report, with VBA code. Related...
Excel Pivot Tables Grouping: Group Items, Group Data and Group Date Values, using VBA
Excel Pivot Tables Grouping: Group Items, Group Data and Group Date Values, using VBA You can create a group containing the selected items, in the Rows & Columns Areas of a PivotTable report. Grouping data bunches rows or columns the way you want and which might not be possible with the PivotTable tools like sorting...
Excel Pivot Tables: Sort Fields, Values & Dates, use Custom Lists, with VBA
Excel Pivot Tables: Sort Fields, Values & Dates, use Custom Lists, with VBA Related Links: 1. Create an Excel Pivot Table report using VBA 2. Referencing an Excel Pivot Table Range using VBA 3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA 4. Excel Pivot Table Address, Location, Move &...
Excel Pivot Tables: Filter Data, Items, Values & Dates using VBA
Excel Pivot Tables: Filter Data, Items, Values & Dates using VBA Related Links: 1. Create an Excel Pivot Table report using VBA 2. Referencing an Excel Pivot Table Range using VBA 3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA 4. Excel Pivot Table Address, Location, Move & Copy using...
Excel Pivot Tables: Summary Functions, Custom Calculations & Value Field Settings, using VBA
Excel Pivot Tables: Summary Functions, Custom Calculations & Value Field Settings, using VBA Related Links: 1. Create an Excel Pivot Table report using VBA 2. Referencing an Excel Pivot Table Range using VBA 3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA 4. Excel Pivot Table Address, Location, Move &...
Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas using VBA
Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas using VBA Related Links: 1. Create an Excel Pivot Table report using VBA 2. Referencing an Excel Pivot Table Range using VBA 3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA 4. Excel Pivot Table Address, Location, Move &...
Create & Customize Excel Pivot Table Charts, using VBA
Create & Customize Excel Pivot Table Charts, using VBA Related Links: 1. Create an Excel Pivot Table report using VBA 2. Referencing an Excel Pivot Table Range using VBA 3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA 4. Excel Pivot Table Address, Location, Move & Copy using VBA 5....
Excel VBA – Record and Run Macros using the Excel Macro Recorder
Excel VBA – Record and Run Macros using the Excel Macro Recorder Applicable to Excel 2007 ———————————————————————— Contents: Start Recording a Macro – the Record Macro dialog box Naming a Macro Assign a Keyboard Shortcut Store a Macro Add a Description Recording a Macro View, Edit & Run a Recorded Macro Use Relative References...
Visual Basic Editor in Excel (VBE) – the VBA Code Editor
Visual Basic Editor in Excel (VBE) – the VBA Code Editor ———————————————————- Contents: Launch the Visual Basic Editor Visual Basic Editor components Code Window Project Explorer Properties Window The Programming Workspace ———————————————————- The Visual Basic Editor (VBE) in Excel is an environment used to write, edit and debug VBA code, and is contained in the...
Excel VBA Objects; Excel Object Model; Access a Workbook, Worksheet or Range Object; Set Object Properties & Call its Methods
Excel VBA Objects; Excel Object Model; Access a Workbook, Worksheet or Range Object; Set Object Properties & Call its Methods ———————————————————————————– Contents: VBA Objects The Excel Object Model Active Object Access an Object / Access a Single Object from its Collection Properties and Methods of Objects Working with Objects in Excel VBA ———————————————————————————– An object...
Starting with VBA in Excel, Writing VBA Code for Macros or Procedures
Starting with Excel VBA, Writing VBA Code ———————————————————– Contents: Excel VBA Objects, Properties & Methods Event Procedures in VBA Visual Basic Editor (VBE) Modules in Excel VBE VBA Procedures Creating a sub-procedure Run or Execute a Procedure Line Continuation within VBA code Auto Syntax Check Comment Text within VBA code Indenting your VBA code Using...
Excel VBA – Message and Input Boxes in Excel, MsgBox Function, InputBox Function, InputBox Method
Excel VBA – Message and Input Boxes in Excel, MsgBox Function, InputBox Function, InputBox Method —————————————————————————————————————————— Contents: Using Message Box in vba code MsgBox Function Example: Message Box Buttons options Example: Set Default Button for Message Box Example: Format MsgBox Example: Using MsgBox return values with Select_Case statemnts Example: Using MsgBox return values with If_Then...
Excel VBA Application Object – the Default Object in Excel
Excel VBA Application Object, Properties & Methods Related Links: Working with Objects in Excel VBA Excel VBA Workbook Object, working with Workbooks in Excel Microsoft Excel VBA – Worksheets Excel VBA Range Object, Referencing Cells and Ranges Excel VBA Custom Classes and Objects ———————————————————————————————————– Contents: The Application Object Instances where using the Application...
Excel VBA Workbooks – Reference, Open, Add, Name, Save, Activate, Copy & Close Workbooks; SendMail Method
Excel VBA Workbooks – Reference, Open, Add, Name, Save, Activate, Copy & Close Workbooks; SendMail Method Related Links: Working with Objects in Excel VBA Excel VBA Application Object, the Default Object in Excel Microsoft Excel VBA – Worksheets Excel VBA Range Object, Referencing Cells and Ranges Excel VBA Custom Classes and Objects ————————————————————————————– Contents: The...
Excel VBA Worksheets – Refer, Activate or Select, Add & Name, Copy or Move, Hide or Display, Delete, Page Layout & View, Calculate
Excel VBA Worksheets – Refer, Activate or Select, Add & Name, Copy or Move, Hide or Display, Delete, Page Layout & View, Calculate Related Links: Working with Objects in Excel VBA Excel VBA Application Object, the Default Object in Excel Excel VBA Workbook Object, working with Workbooks in Excel Excel VBA Range Object, Referencing Cells...
Excel VBA Referencing Ranges – Range, Cells, Item, Rows & Columns Properties; Offset; ActiveCell; Selection; Insert
Excel VBA Referencing Ranges – Range, Cells, Item, Rows & Columns Properties; Offset; ActiveCell; Selection; Insert You can refer to or access a worksheet range using properties and methods of the Range object. A Range Object refers to a cell or a range of cells. It can be a row, a column or a selection...
Excel VBA Refer to Ranges – Union & Intersect; Resize; Areas, CurrentRegion, UsedRange & End Properties; SpecialCells
Excel VBA Refer to Ranges – Union & Intersect; Resize; Areas, CurrentRegion, UsedRange & End Properties; SpecialCells An important aspect in vba coding is referencing and using Ranges within a Worksheet. You can refer to or access a worksheet range using properties and methods of the Range object. A Range Object refers to a cell...
Excel VBA: Calling Sub Procedures & Functions, Placement in Modules
Excel VBA: Calling Sub Procedures & Functions, Placement in Modules ——————————————————————————– Contents: Sub procedures, Function Procedures & Property Procedures Naming Rules & Conventions for Procedures VBA Procedures Scope – Public vs Private Placement of Macros / Sub procedures in appropriate Modules Calling Procedures Executing Procedures ——————————————————————————– Sub procedures, Function Procedures & Property Procedures A VBA...
Excel VBA: Passing Arguments to Procedures, Parameter Arrays (ParamArray)
Excel VBA: Passing Arguments to Procedures, Parameter Arrays (ParamArray) ——————————————————————————————————— Contents: Argument Data Types Passing Arguments By Value Passing Arguments By Reference Optional Arguments Pass an Arbitrary or Indefinite Number of Arguments – Parameter Arrays (ParamArray) ——————————————————————————————————— When an external value is to be used by a procedure to perform an action,...
Excel VBA Custom Classes & Objects, Class Modules, Custom Events
Excel VBA Custom Classes & Objects, Class Modules, Custom Events ————————————————————— Contents: Custom Classes and Objects Custom Class Events ————————————————————— In vba you can create your own custom objects by defining classes & inserting class modules. You can also create your own custom class events, in addition to Excel’s own inbuilt event procedures. In this...
Excel VBA – Empty, ZLS, Null, Nothing, Missing
Excel VBA – Empty, ZLS, Null, Nothing, Missing ———————————– Contents: Empty VarType Function Null Nothing Missing ———————————– In excel vba we often refer to an Empty variable, ZLS (zero-length string) or null string or vbNullString, Null value, Missing Argument, or using the Nothing keyword with an object variable. It is important to differentiate and understand...
Excel VBA String Functions: LEFT, RIGHT, MID, LEN, REPLACE, INSTR, INSTRREV
Excel VBA String Functions: Excel VBA String Functions for Finding and Replacing Text, with Examples: LEFT, RIGHT, MID, LEN, REPLACE, InStr & InStrRev Functions ——————————————————- Contents: LEFT Function (Worksheet / VBA) RIGHT Function (Worksheet / VBA) MID Function (Worksheet / VBA) LEN Function (Worksheet / VBA) REPLACE Function (Worksheet) REPLACE Function (VBA)...
Excel VBA String Functions: SPLIT, JOIN, CONCATENATE
Excel VBA String Functions: SPLIT, JOIN, CONCATENATE Excel VBA Functions to Split a String into Substrings, and Join an Array of Substrings to form a String, with Examples. Also refer Related Link: Excel VBA String Functions: LEFT, RIGHT, MID, LEN, REPLACE, INSTR, INSTRREV —————————————————————– Contents: Split Function (VBA) Join Function (VBA) Concatenate with & (Worksheet...
Excel VBA Errors & Error Handling, On Error & Resume Satements, Exit Statement, Err Object
Excel VBA Errors & Error Handling, On Error & Resume Satements, Exit Statement, Err Object ————————————————————————————————- Contents: VBA Erros & Error Handling Error Handling Setting, in VBE Error Handler On Error Statements Using an Exit Statement Error Handling in Nested Procedures & The Resume Statement Get Information from the Error Object Raise Method...
Excel VBA Debugging Tools in Visual Basic Editor – Breakpoints & Break Mode, Stepping Through Code, Debugging Views
Excel VBA Debugging Tools in Visual Basic Editor – Breakpoints & Break Mode, Stepping Through Code, Debugging Views ————————————————————— Contents: Debug Code by using a Message Box Use Breakpoints to Debug Code Using Break Mode to Debug Code Stepping Through Code Debugging Views Immediate Window Locals Window Watch Window Call Stack ————————————————————— Debugging is a...
Charts in Excel VBA – Add a Chart, the Chart object & the ChartObject object
Charts in Excel VBA – Add a Chart, the Chart object & the ChartObject object Contents: Worksheet & Chart Sheet in Excel Add a Chart ChartObject object Chart object Excel is a great tool to create charts quickly & easily, to display worksheet data in a meaningful manner for users. This section illustrates creating &...
Chart Elements in Excel VBA (Part 1) – Chart Title, Chart Area, Plot Area, Chart Axes
Chart Elements in Excel VBA (Part 1) – Chart Title, Chart Area, Plot Area, Chart Axes, Chart Series, Data Labels, Chart Legend Contents: Chart Title Chart Area Plot Area Chart Axis This chapter discusses some important chart elements contained on a chart, which include: chart area (ChartArea object); chart title (ChartTitle object); plot area (PlotArea...
Chart Elements in Excel VBA (Part 2) – Chart Series, Data Labels, Chart Legend
Chart Elements in Excel VBA (Part 2) – Chart Title, Chart Area, Plot Area, Chart Axes, Chart Series, Data Labels, Chart Legend Contents: Chart Series DataLabels Object / DataLabel Object Chart Legend This chapter discusses some important chart elements contained on a chart, which include: chart area (ChartArea object); chart title (ChartTitle object); plot area...
Child Objects common for many chart elements: Border Object, ChartFillFormat Object, Interior Object, Font Object
Child Objects commonly used with Chart Elements: Border Object, ChartFillFormat Object, Interior Object, Font Object Contents: Border Object ChartFillFormat Object Interior Object Font Object You can use the Border object, ChartFillFormat Object, Interior Object & Font Object with several Chart Elements to customize the border, fill, interior and font settings. Each object has multiple properties...
ChartFormat object – line, fill & effect formatting for chart elements: FillFormat object, LineFormat object, ShadowFormat object, GlowFormat object, SoftEdgeFormat object, ThreeDFormat object
ChartFormat object – line, fill & effect formatting for chart elements: FillFormat object, LineFormat object, ShadowFormat object, GlowFormat object, SoftEdgeFormat object, ThreeDFormat object Contents: The ChartFormat object The FillFormat obj Setting ForeColor & BackColor for the Fill Setting a Solid Fill Setting Gradient Type for the Fill Gradient Stops – the End Points of Color...
Create Charts in Excel VBA: Embedded Charts – Line with Markers; Clustered Stacked Column Chart; Clustered Stacked Bar Chart displaying Variance; Pie chart; XY Scatter chart & Bubble chart
Create Charts in Excel VBA: Embedded Charts – Line with Markers; Clustered Stacked Column Chart; Clustered Stacked Bar Chart displaying Variance; Pie chart; XY Scatter chart & Bubble chart Contents: Create an Embedded Chart – Line with Markers Creating a Clustered Stacked Column Chart Create a Clustered Stacked Bar Chart displaying Variance between Series Pie...
Automate Microsoft Word from Excel, using VBA
Automate Microsoft Word from Excel, using VBA ——————————————————————— Contents: Automating an Office Application Practical Examples of Automating Word from Excel ——————————————————————— Automation is a process by which one application communicates with or controls another application. This can be done in two ways: Early Binding and Late Binding. Binding is a process where you assign an...
Automate Microsoft Outlook from Excel, using VBA
Automate Microsoft Outlook from Excel, using VBA ————————————————————————————————————————————— Contents: Create a new instance of the Outlook application and Create an instance of a NameSpace object variable Reference Existing Outlook Folders and Create New Folders, in Automation Create New Outlook Items and Reference Outlook Items, in Automation ————————————————————————————————————————————— In this section it is...
Export contacts from Outlook to Excel – automate in vba
Export contacts from Outlook to Excel – automate in vba ————————————————————————————————————————– Contents: Automating Microsoft Outlook from Excel Export contacts from a specific Contact Items Folder to an Excel Worksheet, using Early Binding Export contacts from the default Contact Items Folder to an Excel Worksheet, using Late Binding ————————————————————————————————————————– Data from Outlook...
Import Contacts from Excel to Outlook – automate in vba
Import Contacts from Excel to Outlook – automate in vba —————————————————————————————————————– Contents: Automating Microsoft Outlook from Excel Export data from an Excel Worksheet to the default Contacts folder (new contact items added with Application.CreateItem Method), using Early Binding Export data from an Excel Worksheet to the specified Contacts folder (new contact items added...
Automate Outlook using vba: Sending Email from Excel using Outlook
Automate Outlook using vba: Sending Email from Excel using Outlook ———————————————————————————————— Contents: Automate Outlook using vba Send text and also contents from the host workbook’s worksheet range as Mail Body, and add an attachment with the mail, using Early Binding Send the Host Workbook as an attachment with the mail, using Early Binding...
Automate Microsoft PowerPoint from Excel, using vba, Run a Slide Show
Automate Microsoft PowerPoint from Excel, using vba, Run a Slide Show ————————————————————————————————————— Contents: Automate Microsoft PowerPoint from Excel Create a new PowerPoint ppt of 3 slides with sound effect, and run a slide show, using Early Binding Create a new PowerPoint ppt of 4 slides with sound clips and chart, run & view...
Microsoft Access: ActiveX Data Objects Library (ADO)
Microsoft Access: ActiveX Data Objects Library (ADO) Microsoft Access: ActiveX Data Objects (ADO), Connect with Access Databases from Excel using VBA. Part 1 of 4 Microsoft Access: ActiveX Data Objects (ADO), Connect with Access Databases from Excel using VBA: 1. Microsoft Access: ActiveX Data Objects Library (ADO). 2. Microsoft Access: Use ADO to Execute SQL...
Microsoft Access: Use ADO to Execute SQL statements
Microsoft Access: Use ADO to Execute SQL statements Microsoft Access: ActiveX Data Objects (ADO), Connect with Access Databases from Excel using VBA. Part 2 of 4 Microsoft Access: ActiveX Data Objects (ADO), Connect with Access Databases from Excel using VBA: 1. Microsoft Access: ActiveX Data Objects Library (ADO). 2. Microsoft Access: Use ADO to Execute...
Import or Export Data from Access to Excel using ADO
Import or Export Data from Access to Excel using ADO Microsoft Access: ActiveX Data Objects (ADO), Connect with Access Databases from Excel using VBA. Part 3 of 4 Microsoft Access: ActiveX Data Objects (ADO), Connect with Access Databases from Excel using VBA: 1. Microsoft Access: ActiveX Data Objects Library (ADO). 2. Microsoft Access: Use ADO...
Microsoft Access: ActiveX Data Objects Extensions (ADOX)
Microsoft Access: ActiveX Data Objects Extensions (ADOX) Microsoft Access: ActiveX Data Objects (ADO), Connect with Access Databases from Excel using VBA. Part 4 of 4 Microsoft Access: ActiveX Data Objects (ADO), Connect with Access Databases from Excel using VBA: 1. Microsoft Access: ActiveX Data Objects Library (ADO). 2. Microsoft Access: Use ADO to...
Connecting to Microsoft Access Database from Excel VBA, using DAO Object Model
Connecting to Microsoft Access Database from Excel VBA, using DAO Object Model Microsoft Access: Data Access Objects Library (DAO), Connect with Access Databases from Excel using VBA. Part 1 of 3 Microsoft Access: Data Access Objects Library (DAO), Connect with Access Databases from Excel using VBA 1. Connecting to Microsoft Access Database from Excel VBA,...
Microsoft Access DAO Object Model: Create an Index, Create Relationship between Fields, Create and Exceute a Query
Microsoft Access DAO Object Model: Create an Index, Create Relationship between Fields, Create and Exceute a Query Microsoft Access: Data Access Objects Library (DAO), Connect with Access Databases from Excel using VBA. Part 2 of 3 Microsoft Access: Data Access Objects Library (DAO), Connect with Access Databases from Excel using VBA: 1. Connecting to Microsoft...
Microsoft Access DAO Object Model: Import or Export Data from Access to Excel
Microsoft Access DAO Object Model: Import or Export Data from Access to Excel Microsoft Access: Data Access Objects Library (DAO), Connect with Access Databases from Excel using VBA. Part 3 of 3 Microsoft Access: Data Access Objects Library (DAO), Connect with Access Databases from Excel using VBA. 1. Connecting to Microsoft Access Database from Excel...