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:...
Tag: Excel
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...
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...
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...
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...
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 &...
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...
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...
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 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...
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 – 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 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 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 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 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 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 Online Tutorial – learn Excel VBA programming & access examples, illustrations, live codes and downloadable files
Excel and VBA Solutions Excel VBA Programming – Enhance your Excel skills Visual Basic for Applications (VBA) is a programming language used to work with Microsoft Excel, and also with other Microsoft Office applications like Word, Access, PowerPoint & Outlook, and with many third-party applications. VBA for Excel: VBA programming is used to get enhanced...
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...
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 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...
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 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 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...
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...
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...
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...
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: 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...
Printing a Pivot Table Report – Excel
Printing a Pivot Table Report – Excel As applicable to Excel 2007 Excel Pivot Table Tutorial: 1. Create a Pivot Table report; Add, Copy, Rearrange & Remove Pivot Table Fields; ‘PivotTable Field List’ Pane. 2. ‘PivotTable Options’ dialog box – Layout & Format, Totals & Filters, Display, Printing & Data. 3. Pivot Table Report – Clear...
Create a Pivot Chart in Excel – graphical display of a Pivot Table
Create a Pivot Chart in Excel – graphical display of a Pivot Table As applicable to Excel 2007 Excel Pivot Table Tutorial: 1. Create a Pivot Table report; Add, Copy, Rearrange & Remove Pivot Table Fields; ‘PivotTable Field List’ Pane. 2. ‘PivotTable Options’ dialog box – Layout & Format, Totals & Filters, Display, Printing &...
Excel Pivot Table Design & Layout, Pivot Table Styles
Excel Pivot Table Design & Layout, Pivot Table Styles As applicable to Excel 2007 In this section we discuss the Design tab which contains options to format a Pivot Table report and has three groups – Layout, Pivot Table Style Options and Pivot Table Styles. Excel Pivot Tables Tutorial: 1. Create a Pivot Table report;...
Excel Pivot Table Report – Sort Data in Row & Column Labels & in Values Area, use Custom Lists
Excel Pivot Table Report – Sort Data in Row & Column Labels & in Values Area, use Custom Lists As applicable to Excel 2007 Excel Pivot Tables Tutorial: 1. Create a Pivot Table report; Add, Copy, Rearrange & Remove Pivot Table Fields; ‘PivotTable Field List’ Pane. 2. ‘PivotTable Options’ dialog box – Layout & Format,...
Excel Pivot Table Report – Summary Functions & Custom Calculations, Insert Calculated Fields or Calculated Items
Excel Pivot Table Report – Summary Functions & Custom Calculations, Insert Calculated Fields or Calculated Items As applicable to Excel 2007 Excel Pivot Tables Tutorial: 1. Create a Pivot Table report; Add, Copy, Rearrange & Remove Pivot Table Fields; ‘PivotTable Field List’ Pane. 2. ‘PivotTable Options’ dialog box – Layout & Format, Totals & Filters,...
Excel Pivot Table Report – Group Items, Group Date and Time Values
Excel Pivot Table Report – Group Items, Group Date and Time Values As applicable to Excel 2007 Excel Pivot Tables Tutorial: 1. Create a Pivot Table report; Add, Copy, Rearrange & Remove Pivot Table Fields; ‘PivotTable Field List’ Pane. 2. ‘PivotTable Options’ dialog box – Layout & Format, Totals & Filters, Display, Printing & Data....
Excel Pivot Table Report – Field Settings, Expand or Collapse Fields & Items, Refresh Data, Change Data Source & Show or Hide options
Excel Pivot Table Report – Field Settings, Expand or Collapse Fields & Items, Refresh Data, Change Data Source & Show or Hide options As applicable to Excel 2007 Excel Pivot Tables Tutorial: 1. Create a Pivot Table report; Add, Copy, Rearrange & Remove Pivot Table Fields; ‘PivotTable Field List’ Pane. 2. ‘PivotTable Options’ dialog box...
Excel Pivot Table Report – Clear All, Remove Filters, Select Mutliple Cells or Items, Move a Pivot Table
Excel Pivot Table Report – Clear All, Remove Filters, Select Mutliple Cells or Items, Move a Pivot Table As applicable to Excel 2007 With the tools available in the Actions group of the ‘Options’ tab (under the ‘Pivot Table Tools’ tab on the ribbon), you can Clear a Pivot Table, Remove Filters, Select Multiple Cells...
Excel Pivot Table Report Layout & Format, Totals & Filters, Display, Printing & Data
Excel Pivot Table Report Layout & Format, Totals & Filters, Display, Printing & Data As applicable to Excel 2007 Excel Pivot Tables Tutorial: 1. Create a Pivot Table report; Add, Copy, Rearrange & Remove Pivot Table Fields; ‘Pivot Table Field List’ Pane. 2. ‘Pivot Table Options’ dialog box – Layout & Format, Totals & Filters,...
Excel Pivot Tables Tutorial: Create a Pivot Table Report, Add & Remove Fields
Excel Pivot Tables Tutorial: Create a Pivot Table Report, Add & Remove Fields Create a Pivot Table and Pivot Chart report; Add, Copy, Move, Rearrange & Remove Pivot Table Fields; use Pivot Table Field List pane. As applicable to Excel 2007 Excel Pivot Table Tutorial: 1. Create a Pivot Table report; Add, Copy, Rearrange &...