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 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 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 – 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...

test

Also refer to Example 23, of using the End & Row properties to determine the last used row or column with data. Also refer to Example 23, of using the End & Row properties to determine the last used row or column with data.       Also refer to Example 23, of using the...

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 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...

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...

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: 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...

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: 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...

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...

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...

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 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...

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....

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...

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...

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 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 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 &...

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...

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 Tables: Filter Data, Items, Values & Dates

Excel Pivot Tables: Filter Data, Items, Values & Dates As applicable to Excel 2007 Excel Pivot Table Tutorial: 1. Create a PivotTable report; Add, Copy, Rearrange & Remove PivotTable Fields; ‘PivotTable Field List’ Pane. 2. ‘PivotTable Options’ dialog box – Layout & Format, Totals & Filters, Display, Printing & Data. 3. PivotTable Report – Clear...

Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas

Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas As applicable to Excel 2007 Excel Pivot Table Tutorial: 1. Create a PivotTable report; Add, Copy, Rearrange & Remove PivotTable Fields; ‘PivotTable Field List’ Pane. 2. ‘PivotTable Options’ dialog box – Layout & Format, Totals & Filters, Display, Printing & Data. 3. PivotTable Report...

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 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 &...

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...

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;...

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 – 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 – 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 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...

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...

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:...

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....

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...

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...

SUMIF with Date Range, Multiple Month Names / Numbers & Year

  SUMIF with Date Range, Multiple Month Names / Numbers & Year      Return SUM of values corresponding to One or Multiple Months, Date Range, and Multiple Names – using SUM, SUMIFS or SUMRODUCT.    Cell F6 formula: Return SUM of column C values, where column A month numbers are either 4, 1, or...

SUM Max values of Each Row, in a Multiple Column Range

  SUM Max values of Each Row, in a Multiple Column Range     Return SUM of Max values in Each Row, for a Multiple Column Range.    Cell E4 formula: Determines the MAX value of each row in the 2 column range A2:B10, and returns the SUM of each of these MAX values.      ...

SUM Cell values containing any of the Multiple Values in Full or in Part

  SUM Cell values containing any of the Multiple Values in Full or in Part     SUMIF – SUM corresponding column B cells where column A cells contain any of the Multiple Values (C2:C5) in Full or in Part.    Cell E8 formula: Return SUM of corresponding column B cells, where column A cells contain any...

SUM Non-Contiguous Ranges of a Column – SUM EACH block of consecutive cells

  SUM Non-Contiguous Ranges of a Column – SUM EACH block of consecutive cells, starting from specific position, & skip specific number of cells     SUM EACH of the Non-Contiguous Blocks of consecutive number of cells, with a specified start position,  after skipping specific number of consecutive cells    Cell D2 formula: In consecutive cells of...

Assign separate Values to Non-Contiguous Cells and return SUM

  SUM – Assign separate Values to Non-Contiguous Cells and return SUM     SUM – Assign separate Values to Non-Contiguous Cells in a column, and return SUM    Cell E4 formula: Check ALL cells in the range A2:A10 for “Yes” & return SUM: if 2nd or 8th cells are “Yes” add 10 for each; if any other...

Sumif to Exclude Cells Hidden by a Filter

  SUMIF using SUBTOTAL function to Exclude Cells Hidden by a Filter      Sumif / Countif using SUBTOTAL function to Exclude / Include Cells Hidden by a Filter.    Cell D6 formula: SUMIF to EXCLUDE cells hidden by using Filter.       ———————————————————————————————–   SUMIF Exclude Hidden Filtered Rows     Cell D6 formula SUMIF to EXCLUDE cells...

Countif Multiple Criteria, relate Criteria to a Value, return SUM

  Count Occurrences of Multiple Criteria in a Column, relate each Criteria to a Number and return SUM     Countif with Multiple Criteria – multiply each occurrence with a value corresponding to the respective Criteria and return SUM    Cell D7 formula: Count occurrences of “Apple” (C2), “Banana” (C3) & “Pear” (C4) in col A, return...

Count Cells in Non-Contiguous Ranges, where a value occurs in whole or in part

Count cells where a value appears atleast once (in whole or as part of a string), in Multiple Non-Contiguous Ranges Related Links: 1. Excel VLOOKP Function, with examples. 2. Left Lookup with VLookup Excel function. 3. Left Lookup, with Index, Match & Offset Excel functions. 4. Vlookup Multiple Values – Return MULTIPLE corresponding values for...

Count Occurrences of a Value in Column, as a whole or in part

Count Occurrences of a Value in all cells of a Column, either individually or part of a string Related Links: 1. Excel VLOOKP Function, with examples. 2. Left Lookup with VLookup Excel function. 3. Left Lookup, with Index, Match & Offset Excel functions. 4. Vlookup Multiple Values – Return MULTIPLE corresponding values for ONE Lookup Value. Count...

Countif Array Criteria, Multiple Columns, AND / OR conditions

COUNTIF with Array Criteria, Multiple Columns, AND / OR conditions Related Links: 1. Excel VLOOKP Function, with examples. 2. Left Lookup with VLookup Excel function. 3. Left Lookup, with Index, Match & Offset Excel functions. 4. Vlookup Multiple Values – Return MULTIPLE corresponding values for ONE Lookup Value. COUNTIF (or Sumproduct) for Multiple Columns, Array Criteria, using...

SUM Largest 4 Values in Non-Contiguous Cells of a Row

  SUM Largest 4 Values in Non-Contiguous Cells of a Row     SUM Largest 4 Values in Non-Contiguous Cells of a Row    Cell B9 formula: Non-Contiguous cells with even intervals – SUM largest 4 values in the 2nd row cells of B2, F2, J2, N2, R2 & V2 (consider 1st cell, skip the next 3 cells). Returns...

SUM Smallest 3 Values in Non-Contiguous Cells with Uneven Intervals

  SUM Smallest 3 Values in Non-Contiguous Cells with Uneven Intervals     SUM of Smallest 3 Non Contiguous Cells (with values above zero or non-blank cells) with Uneven Intervals SUM Smallest 3 of 7 Non Contiguous Cells (A2, A4,A5, A7,A8, A11, A12) – Multiple Methodologies are illustrated    Cell D17 and D27 formulas: Both formulas – Sum...

Average of Consecutive Negative Numbers

  Average of Consecutive Negative Numbers in a Column     Average of Consecutive Negative Numbers in column A.    Cell C3 formula:  Average of Consecutive Negative Numbers (ie. if Consecutive more than Once atleast) in Column A.    ———————————————————————————————–    Average Consecutive Negative Numbers     Cell C3 formula: Average of Consecutive Negative Numbers (ie. if Consecutive more than Once atleast) in...

Average Largest 5 column values, per Matching Id

  Average of Largest 5 Values in a Column, per Matching Id     Average of Largest 5 Values in column B, corresponding to the respective Id in column A.    Cell F2 formula:  Average of Largest 5 Values in column B, corresponding to the respective Id in column A – Blank cells are ignored, and zeros are considered for Average. ...

Average of Last 20 Positive values only in a Column

  Average of Last 20 column values, only Positives / Negatives     Average of last 20 positive values (zero and above) in column A    Cell G4 formula:  Average of last 20 positive values (zero and above) in column A.    ———————————————————————————————–            Cell G4 formula: Average of last 20 positive values (zero and above) in...

Average of first 10 values in a column, Ignore Error Values

  Average of first 10 values in a column, Ignore Error Values, Exclude / Include Zeros & Blank Cells     Average of first 10 values in a column, Ignore Error Values    Cell C8 formula:  Average of the first 10 values in column A, ignore errors, includes zeros, excludes blank cells.     ———————————————————————————————–      Average 1st 10...

Position of First Text Character in a String

  Position of 1st Occurrence of a Text Character in cell string     Position in AlphaNumeric String of 1st Text Character, Last Text Character, 1st Letter (A-Z), Last Digit Preceding 1st Text Character.    Cell B2 formula: Returns the Position of 1st Text (Non-Numerical) Character in cell A2 String.    ———————————————————————————————–  Position of 1st Text...

Position of Last Numeric in a String

  Position of Last Occurrence of a Numeric in a cell string     Position of 1st or Last Numeric character in an AlphaNumeric String.    Cell E2 formula: Returns the Position of Last Numeric in Cell A2 String.    ———————————————————————————————–   Position of Last Numeric in a String    Cell E2 formula: Returns the Position of 1st Numeric character...

Extract the Number Preceding Specific Text in a String

  Extract the Number immediately Preceding Specific Text in a String     Extract the “Number” or “Alphanumeric Sub-string”, immediately Preceding Occurrence of a specific text (“no”) within a String.    Cell C2 formula: Extract the “Number” or “Alphanumeric Sub-string”, immediately Preceding Occurrence of the Text “no” in cell A2 string.      ———————————————————————————————– Extract Number Before Specific...

Extract Text After Last Occurrence of a Number in a String

  Extract Sub-String After Last Occurrence of a Number / Numerical in a String     Extract All Characters starting After the Last occurrence of a Number (ie. 0 to 9) till the end.    Cell B2 formula: Extracts all Text starting After and to the Right of the Last occurrence of a Number (ie. 0 to...

Position of First Number in a String

  Position of 1st Occurrence of a Number in a cell string     Position of 1st Number in String; Extract String to the Left of 1st Number / Starting from 1st Number.    Cell C2 formula: Returns the Position of First Number in Cell A2 String.    ———————————————————————————————–  Position of 1st Number in String    ...

Extract Text after Last Blank Space in a String

  Extract all Text after Last Blank Space in a cell String     Extract Text after Last Blank Space in a String.    Cell D12 formula: Cell D12 formula Extracts all Text after last blank space in cell A12 string.      ———————————————————————————————–  Extract Text After Last Space   Cell D12 formula: Extracts all Text after last blank space in...

Split Names – Extract First, Middle & Last Names in Separate Cells

  Split Names – Extract First, Middle & Last Names in Separate Cells     Extract First Name, Middle Name & Last Name(s) in separate cells, from Full Name, where each name is separated with a Space. 2nd cell to contain the excess Middle Names, where there are more than 3 Names separated with Space    Cell...

AVERAGE of Non Contiguous Cells which are Unevenly Distributed

  AVERAGE of Non Contiguous Cells which are Unevenly Distributed     AVERAGE of Non Contiguous Cells which are Unevenly Distributed    Cell D9 formula: Average 6 Non Contiguous Cells which are Unevenly Distributed (A2, A4, A5, A7, A8, A10) having values greater than zero.      ———————————————————————————————–   Average Non-Contiguous Cells, Unevenly Distributed    Cell D9 formula: Average 6...

Sum Diagonal Cells in a Range

  SUM Diagonal Cells in a Range     SUM / Count Diagonal Cells in a Range Diagonal cells are considered starting from the top-left cell of the range     Cell G4 formula: SUM diagonal cells in range “B4:E9” starting from cell “B4” ie. top-left cell of the range – cells B4, C5, D6, E7.      ———————————————————————————————– ...

Count number of Cells containing Specific Letter(s)

Count the number of Cells containing Specific Letters / Characters Related Links: 1. Excel VLOOKP Function, with examples. 2. Left Lookup with VLookup Excel function. 3. Left Lookup, with Index, Match & Offset Excel functions. 4. Vlookup Multiple Values – Return MULTIPLE corresponding values for ONE Lookup Value. Count the number of Cells containing Specific Letter(s)...

Count Multiple Occurrences of Array Values

  Count Multiple Occurrences of Array Values in a Range     Count Multiple Occurrences of Array Values in a Range    Cell E11 formula: Counts Occurrences of column B values in column A (Unique column B values are counted as many times each appears in column A). Unique column B values are counted as many times...

Rank & Sort Column Values, Ignoring Error Values

  Rank & Sort Numbers in a Column, Ignoring Error Values     Rank & Sort Column Numbers, Skipping Error Values    Column B: Ranks column A values, Ignoring Error values, in Descending Order (high to low). Column A contains numbers, and also error values (#N/A, #DIV/0!, etc), in random order. Enter Array formula (Ctrl+Shift+Enter) in cell...

Extract a Sorted List of Unique values in a column

  Return Sorted Unique List of Values from Column having only Text or only Numbers     Sort Column values in Ascending / Descending Order, Retain Duplicates or Extract only Once (ie. Uniques)    Column F: Extracts Uniques & Sorts column E values in Ascending order – column E can have ONLY Numbers. Enter Array formula...

Extract Values which appear ONLY once, from a Column

  Extract Values which appear ONLY once (ie. Unique Values) from a Column     Only those values which appear ONCE in a Column are extracted.   Column A values appear either once (Unique) or multiple times (Duplicate) – only those values which appear ONCE are extracted in columns B;   Enter Non-Array formula in...

Extract Unique sub-strings of 1st 5 Characters from each column cell

  Extract Unique List from a Column: (i) Fixed Length of 1st 5 Characters of each cell string; or (ii) Variable Length of Characters in each cell Preceding the 1st “-” (Dash)     Extract a Unique List of Codes from column cells: (i) Fixed Length of 1st 5 Characters; (ii) Variable Number of Characters...

Unique List Appearing in One Column and Not in Other

  Return Unique List of values which appear only in One Column and NOT the other – or Unique List from Both Columns     Return Unique List of Values from a Column which do not appear in the Other Column   Column D: Extract Unique List of values from column A (each column A value...

Unique List from Single Column, Skip Specific Values

  Extract Unique List from a Single Column (Multiple Occurrences Appear only Once) – Skip Specific Value(s)     Return Unique List of Values from a Single Column, skipping Specific Values   Column A values appear either once (Unique) or multiple times (Duplicate) – these are extracted to appear ONLY ONCE, Skipping Specified Value(s).   In...

Split Values into Multiple Equal Groups

  Split Values into Multiple Equal Groups / Ranks     Assign Column Values into Groups, with Group 1 having Largest Values & Group 5 Lowest Values    Column Values are divided equally between Groups, each group is ranked as per column values, Group 1 having Largest Values & Group 5 Lowest Values.   Column...

Count Unique values in a Column, with Conditions

  Count Unique values in a Column, with Condition(s)     Count Unique values in a Column, with Condition(s) – each distinct value is counted only once irrespective whether it appears once or multiple times    Column A contains values (text or numbers) which occur either only once or multiple times (Duplicate).   Columns B and C...

Count Rows with occurrence of atleast one number

  Count Number of Rows with occurrence of atleast one 1     Count Number of Rows in a Multi Column-Row Array, having occurrence of atleast one 1    If a row in the range A2:F20 contains atleast a single 1, it is considered and counted. Even if a row contains multiple 1s, it is counted as...

Count Continuous Non-Blank cells which appear Last

  Count Number of Continuous Non-Blank cells which appear Last     Count the number of Continuous Non-Blank cells which occur last in the row (ie. the last group of non-blank cells)    Column A & B formulas Count the number of Continuous Non-Blank cells which occur last in the row (ie. the last group of non-blank...

Count Groups of Consecutive Positive Values

  Count Groups of Consecutive Positive or Negative or Zero Values     Count Groups of Consecutive Positive Values    Column C formulas Count Groups of Consecutive Positive / Negative / Zero Values; Average (or Maximum) Number of Cells in Positive / Negative / Zero Value Groups.   Cell C3 Formula: Count Groups of Consecutive Positive Values in...

Count Maximum Number of Consecutive Occurrences of a Value

  Count Maximum Number of Consecutive Occurrences of a value in a Column     Count Maximum Number of Consecutive Occurrences of a value in a Column    Cell C3 formula Counts Maximum Number of Consecutive Occurrences, of a value in a Column – Maximum Consecutive Occurrences of the number 5 (cell B3 value) in column A.    ...

Count Rows containing Multiple specified Values

  Count Number of Rows in a Range where Multiple Values appear atleast once in the Same Row      Count Number of Rows, wherein Multiple Specified Values appear in a Row    Cell K3 formula Counts Number of Rows in a Range where Multiple Values appear atleast once in the Same Row.   Cell K3: Number of rows...

Count Uniques, Multiple AND / OR Conditions

  Count Unique values in a column, subject to Multiple Conditions – AND / OR     Count Unique values in column A subject to multiple AND/OR conditions    Cell J2 formula – Count Unique values in column A subject to multiple AND/OR conditions: (i) If ANY column B, D or F has “Y” (ie. if atleast...

Count Unique Names, where Name corresponds to Multiple Values

  Count Unique Names, where each Name corresponds to Multiple Values     Count Unique Names (Multiple Occurrences of a Name are Counted as One) in column, where each Name corresponds to specific Multiple Values    Cell F6: Count Unique Names in column A, having corresponding column B values of both cells E2:E3 (ie. “ABC” & “KLM”)....

Count Unique Values matching any one of the Multiple values

  Count Unique Values where other column values Match ANY of the Array values     Count Unique column A values (Multiple Occurrences of a Value are Counted Once) where column B values Match ANY of the Array values (ie. Array Criteria Match)    Column G formulas COUNT Unique column A values where column B values Match...

Count Uniques, Condition in One or Both Columns

  Count Values Once (Unique) or as many times they Occur, Condition in Either 1 or Both Columns     Count Values where Multiple Occurrences are Counted Once (Unique values) for Condition of “Yes” in Either 1 or Both Columns    Cell E4: Count number of Unique Names in column A (Multiple Occurrences are Counted Once), if Either...

Count Once for Occurrence of a Value in all columns of Same Row

  Count Occurrences in one or both Columns, consider Once if Occurrence in Same Row of 2 columns     Count Occurrences of a Value in Whole or in Part: (i) Count Once if Occurrence in Same Row of One or Both columns or (ii) Count Twice if Occurrence in Both Columns    Column E COUNTS Occurrences...

Count Uniques Appearing in Only One or in Both Columns

  Count Uniques appearing in BOTH Columns or in 1 Column and NOT in Other     Count Uniques: (i) which appear in BOTH Columns; (ii) which appear in 1 Column and NOT in the Other Column    Each Student (column A) has Multiple Subjects in Semester 1  (column B) and in Semester 2 (Column C)....

Vlookup Multiple Values – Return MULTIPLE corresponding values for ONE Lookup Value

Vlookup Multiple Values – Return MULTIPLE corresponding values for ONE Lookup Value Related Links: 1. Excel VLOOKP Function, with examples. 2. Left Lookup with VLookup Excel function. 3. Left Lookup, with Index, Match & Offset Excel functions. 4. Case Sensitive Vlookup; Finding the 1st, 2nd, nth or last occurrence of the Lookup Value. Return MULTIPLE...

Excel Text and String Functions: TRIM & CLEAN

Excel Text and String Functions: Removing spaces and nonprinting characters from text: Excel TRIM & CLEAN functions Related Links: 1. ASCII Code, Extended ASCII characters (8-bit system) and ANSI Code. 2. Excel CODE & CHAR functions. 3. Excel Text and String Functions: LEFT, RIGHT, MID, LEN, FIND, SEARCH, REPLACE, SUBSTITUTE. When you import data into...

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...

ASCII Code, Extended ASCII characters (8-bit system) and ANSI Code.

ASCII Code, Extended ASCII characters (8-bit system) and ANSI Code Related Links: 1. VBA Chr & Asc functions explained; corresponding Excel CHAR and CODE functions. 2. Excel Text and String Functions: TRIM & CLEAN. A ‘character set‘ maps characters to their identifying code values. Unicode is a global standard for character encoding and is the...

Excel CODE & CHAR Functions, VBA Asc & Chr Functions

Excel Text and String Functions: Excel CODE & CHAR Functions, VBA Asc & Chr Functions, with examples. Related Links: 1. Using VBA Chr and Asc functions to convert excel column number to corresponding column letter, and column letter to column number. 2. ASCII Code, Extended ASCII characters (8-bit system) and ANSI Code. 3. Excel Text...

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...

Excel Text and String Functions: LEFT, RIGHT, MID, LEN, FIND, SEARCH, REPLACE, SUBSTITUTE

Excel Text and String Functions:   Excel Functions for Finding and Replacing Text, with Examples: LEFT, RIGHT, MID, LEN, FIND, SEARCH, REPLACE, SUBSTITUTE Related Links: 1. Excel Text and String Functions: TRIM & CLEAN. 2. Excel CODE & CHAR functions. ————————————————————————————————————————- This section deals with the Excel Worksheet String Functions – click here for detailed...

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...

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...

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...

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...

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...

Excel Date and Time Functions and Formulas

  Excel Date and Time Functions & Formulas     Excel Dates Equate to Serial Numbers:   Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system....

CHOOSE Function in Excel

  CHOOSE Function in Excel and its uses, make Vlookup Look Left & Find Date of Next Monday with CHOOSE function. Related Links: VLOOKUP formula that looks left instead of right. ————————————————————————————————————————– Excel CHOOSE Function The CHOOSE function returns a value from a list of values based on a specified position number.   Syntax: CHOOSE(position_number,...

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...

Find ‘Smallest’, ‘Largest’, ‘K-th Smallest’ and ‘K-th Largest’ Numbers in a Range

Find ‘Smallest’, ‘Largest’, ‘K-th Smallest’ and ‘K-th Largest’ Numbers in a Range with Excel Functions   Related Links: Remove Duplicates in a range, using “Find Smallest and Largest Value with VBA. Using ParamArray to find Minimum Value.   —————————————————————————————————————————  Find Smallest Number or K-th Smallest Number in a Range Find Smallest Number in a Range,...

Excel IF Function and IF Statements

  Excel IF Function and IF Statements, with Examples     Related Link: If…Then…Else Statements (VBA).   ————————————————————————————————————————   Excel IF Function: The IF function is used to test if a condition is True or False. If a specified condition evaluates to TRUE, the IF function returns one value, and if the condition evaluates to...

Excel VLOOKUP Function

  VLOOKP Function in Excel (with examples), using Exact and Approximate Matches, Error (#N/A) handling, …     Related Links: 1. Left Lookup with VLookup Excel function. 2. Left Lookup, with Index, Match & Offset Excel Functions. 3. Vlookup Multiple Values – Return MULTIPLE corresponding values for ONE Lookup Value. 4. Case Sensitive Vlookup; Finding...

Excel OFFSET Function

  Using the Excel OFFSET Function, with Examples   ——————————————————————————————————————————   OFFSET Function: The excel OFFSET function returns a range, which is offset by a specified number of rows and columns from a reference range.   Syntax: OFFSET(reference_range, rows, columns, height, width)   1. reference_range is a single cell or a range of adjacent cells...

Remove Duplicates in a range, using “Remove duplicates” button in Data Tools

  Related Links: Count Number of Unique Values, with Excel Functions Remove Duplicates or Create a List of Unique Records Using Advanced Data Filter Remove Duplicates or Create a List of Unique Records using Excel Formula —————————————————————————————————————————   Remove Duplicates in a range, using “Remove duplicates” button in the top menu tab Data -> Data...

Remove Duplicates or Create a List of Unique Records using Excel Formula

  Related Links: Count Number of Unique Values, with Excel Functions Remove Duplicates or Create a List of Unique Records Using Advanced Data Filter Remove Duplicates in a range, using “Remove duplicates” button in Data Tools ————————————————————————————————————————–     Remove Duplicates or Create a List of Unique Records in Excel     Create a List of...

Remove Duplicates or Create a List of Unique Records using Advanced Data Filter

  Related Links: Count Number of Unique Values, with Excel Functions Remove Duplicates or Create a List of Unique Records using Excel Formula Remove Duplicates in a range, using “Remove duplicates” button in Data Tools     Create a List of Unique Records Using Advanced Data Filter: ——————————————————————————————————– Using Advanced Data Filter   Select a...

Count Number of Unique Values in a Range (with Excel Functions)

  Related Links: Remove Duplicates or Create a List of Unique Records Using Advanced Data Filter Remove Duplicates or Create a List of Unique Records using Excel Formula Remove Duplicates in a range, using “Remove duplicates” button in Data Tools —————————————————————————————————————————   Count Number of Unique Values (when No Blank Cells), with Excel Functions:  ...

Excel INDIRECT Function

  Excel INDIRECT Function, with Examples:   —————————————————————————————————————————–   Excel INDIRECT Function: The INDIRECT function returns the result of a specified reference.   Syntax: INDIRECT(text_reference, reference_type)   1. text_reference is a reference to a cell and that cell can contain: (i) an A1-style reference; (ii) an R1C1-style reference; (iii) a named range;  or (iv) a...

Convert Text and Cells with Formulas to UpperCase with VBA

  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...

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...

Shade Alternate Rows – Conditional Formatting

  Shade Alternate Rows using Conditional Formatting   —————————————————————————————————————————-   Shade Alternate Rows, including blank/unused range   Select worksheet range you wish to format   Home  ->  Conditional Formatting -> New Rule -> select “Use a formula to determine which cells to format” option (Excel 2007)   Format -> Conditional Formatting  ->  “Formula Is”  (earlier...

Excel SUMPRODUCT Function

  Using SUMPRODUCT FUNCTION Using  double unary minus (–)  n SUMPRODUCT Function -> A significant use   ————————————————————————————————————————————————————   Excel SUMPRODUCT Function:   The SUMPRODUCT function multiplies corresponding items of two or more arrays and then sums up these products.   Syntax: SUMPRODUCT(array1, array2, … array255)   1. array1, array2, … array255 are arrays whose...

Left Lookup with Vlookup Excel Function

  VLOOKUP formula that looks Left instead of Right   Related Links: 1. Excel VLOOKP Function, with examples. 2. Left Lookup, with Index, Match & Offset Excel functions 3. Vlookup Multiple Values – Return MULTIPLE corresponding values for ONE Lookup Value. 4. Case Sensitive Vlookup; Finding the 1st, 2nd, nth or last occurrence of the...

Left Lookup with Index, Match and Offset Excel Functions

  Left Lookup with Index, Match and Offset Excel Functions   Related Links: 1. Excel VLOOKP Function, with examples. 2. Left Lookup with VLookup Excel function 3. Vlookup Multiple Values – Return MULTIPLE corresponding values for ONE Lookup Value. 4. Case Sensitive Vlookup; Finding the 1st, 2nd, nth or last occurrence of the Lookup Value....

Last used row number and cell address, last numeric or text value in column

  Find Last Used Row in a Column:   Related Links: VBA Codes – Last used row; Last used column.   ———————————————————————————————————     Formula for ‘Cell Address of the last numeric value within column’   =ADDRESS(MATCH(9.99999999999999E+307, A:A), COLUMN(A1))  [Formula]   The formula returns cell address of the last numeric value in column, which is...

Second Blog Post

Lorem Ipsum is text that is traditionally used by designers when working on a site before the content is ready. Pellentesque bibendum metus ut dolor fermentum ut pulvinar tortor hendrerit. Nam vel odio vel diam tempus iaculis in non urna. Curabitur scelerisque, nunc id interdum vestibulum, felis elit luctus dui, ac dapibus tellus mauris tempus...

Footer Module

This module shows the Joomla! copyright information. Help {loadposition footerload}

Fruit Shop

Welcome to the Fruit Shop We sell fruits from around the world. Please use our website to learn more about our business. We hope you will come to our shop and buy some fruit. This mini site will show you how you might want to set up a site for a business, in this case...

Getting Help

There are lots of places you can get help with Joomla!. In many places in your site administrator you will see the help icon. Click on this for more information about the options and functions of items on your screen. Other places to get help are: Support Forums Documentation Professionals Books

Getting Started

It’s easy to get started creating your website. Knowing some of the basics will help. What is a Content Management System? A content management system is software that allows you to create and manage webpages easily by separating the creation of your content from the mechanics required to present it on the web. In this...

Happy Orange Orchard

At our orchard we grow the world’s best oranges as well as other citrus fruit such as lemons and grapefruit. Our family has been tending this orchard for generations.

Joomla!

Congratulations! You have a Joomla! site! Joomla! makes it easy to build a website just the way you want it and keep it simple to update and maintain. Joomla! is a flexible and powerful platform, whether you are building a small site for yourself or a huge site with hundreds of thousands of visitors. Joomla...

Language Switcher

The language switcher module allows you to take advantage of the language tags that are available when content, modules and menu links are created. This module displays a list of available Content Languages for switching between them. When switching languages, it redirects to the Home page defined for the chosen language. Thereafter, the navigation will...

Latest Articles Module

This Module shows a list of the most recently published and current Articles. Some that are shown may have expired even though they are the most recent. Help {loadposition articleslatestload}

Login Module

This module displays a username and password login form. It also displays a link to retrieve a forgotten password. If user registration is enabled (in the Global Configuration settings), another link will be shown to enable self-registration for users. Help {loadposition loginload}

Menu Module

This module displays a menu on the site (frontend).  Menus can be displayed in a wide variety of ways by using the menu options and css menu styles. Help {loadposition menuload}

Most Read Content

This Module shows a list of the currently published Articles which have the highest number of page views. Help {loadposition articlespopularload}

News Flash

Displays a set number of articles from a category based on date or random selection. Help {loadposition newsflashload}

Parameters

As you make your Joomla! site using the extension you will control the details of the display using parameters. Parameters control everything from whether the author’s name is displayed to who can view what to the number of items shown on a list. Default parameters for each components are changed using the Options button on...

First Blog Post

Lorem Ipsum is filler text that is commonly used by designers before the content for a new site is ready. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed faucibus purus vitae diam posuere nec eleifend elit dictum. Aenean sit amet erat purus, id fermentum lorem. Integer elementum tristique lectus, non posuere quam pretium sed....

Feed Display

This module allows the displaying of a syndicated feed. Help {loadposition feeddisplayload}

Archive Module

This module shows a list of the calendar months containing archived articles. After you have changed the status of an article to archived, this list will be automatically generated. Help {loadposition archiveload}

Article Categories Module

This module displays a list of categories from one parent category. Help {loadposition articlescategoriesload}  

Articles Category Module

This module allows you to display the articles in a specific category. Help {loadposition articlescategoryload}

Authentication

The authentication plugins operate when users login to your site or administrator. The Joomla! authentication is in operation by default but you can enable Gmail or LDAP or install a plugin for a different system. An example is included that may be used to create a new authentication plugin. Default on: Joomla Default off: Gmail...

Australian Parks

Welcome! This is a basic site about the beautiful and fascinating parks of Australia. On this site you can read all about my travels to different parks, see photos, and find links to park websites. This sample site is an example of using the core of Joomla! to create a basic website, whether a “brochure...

Banner Module

The banner module is used to display the banners that are managed by the banners component in the site administrator. Help. {loadposition bannersload}

Beginners

If this is your first Joomla site or your first web site, you have come to the right place. Joomla will help you get your website up and running quickly and easily. Start off using your site by logging in using the administrator account you created when you installed Joomla!.

Contacts

The contact component provides a way to provide contact forms and information for your site or to create a complex directory that can be used for many different purposes. Help

Content

The content component (com_content) is what you use to write articles. It is extremely flexible and has the largest number of built in views. Articles can be created and edited from the front end, making content the easiest component to use to create your site content. Help

Custom HTML Module

Sub FontColorFormulas1()‘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 formulaColor As LongDim formulanumbersColor As LongDim constantColor As LongDim cell As Range formulaColor = RGB(Red:=0, Green:=255, Blue:=0)formulanumbersColor = RGB(Red:=0, Green:=0, Blue:=0)constantColor = RGB(Red:=0, Green:=0, Blue:=255)   ...

Directions

Here’s how to find our shop. By car Drive along Main Street to the intersection with First Avenue.  Look for our sign. By foot From the center of town, walk north on Main Street until you see our sign. By bus Take the #73 Bus to the last stop. We are on the north east...

Editors

Editors are used thoughout Joomla! where content is created. TinyMCE is the default choice in most locations although CodeMirror is used in the template manager. No Editor provides a text box for html content. Default on: CodeMirror Help TinyMCE Help No Editor Help Default off: None

Editors-xtd

These plugins are behind the buttons found beneath your editor. They only run when an editor plugin runs. Default on: Editor Button: Image Editor Button: Readmore Editor Button: Page Break Editor Button: Article Default off: None

Administrator Components

All components also are used in the administrator area of your website. In addition to the ones listed here, there are components in the administrator that do not have direct front end displays, but do help shape your site. The most important ones for most users are Media Manager Extensions Manager Menu Manager Global Configuration Banners...

Pages: 1 2 3 4 5 6 7

User

Default on: Joomla Help Default off: Two new plugins are available in 1.6 but are disabled by default. Contact Creator HelpCreates a new linked contact record for each new user created. Profile HelpThis example profile plugin allows you to insert additional fields into user registration and profile display. This is intended as an example of the types...

Users

The users extension lets your site visitors register, login and logout, change their passwords and other information, and recover lost passwords. In the administrator it allows you to create, block and manage users and create user groups and access levels. Help Please note that some of the user views will not display if you are...

Using Joomla!

With Joomla! You can create anything from a simple personal website to a complex ecommerce or social site with millions of visitors. This section of the sample data provides you with a brief introduction to Joomla! concepts and reference material to help you understand how Joomla! works. When you no longer need the sample data,...

Weblinks

Weblinks (com_weblinks) is a component that provides a structured way to organize external links and present them in a visually attractive, consistent and informative way. Help

Weblinks Module

This module displays the list of weblinks in a category. Help {loadposition weblinksload}

Who’s Online

The Who’s Online Module displays the number of Anonymous Users (e.g. Guests) and Registered Users (ones logged in) that are currently accessing the Web site. Help {loadposition whosonlineload}

Wonderful Watermelon

Watermelon is a wonderful and healthy treat. We grow the world’s sweetest watermelon. We have the largest watermelon patch in our country.

Wrapper Module

This module shows an iFrame window to specified location. Help {loadposition wrapperload}

News Feeds

News Feeds (com_newsfeeds) provides a way to organize and present news feeds. News feeds are a way that you present information from another site on your site. For example, the joomla.org website has numerous feeds that you can incorporate on your site. You an use menus to present a single feed, a list of feeds...

Breadcrumbs Module

Breadcrumbs provide a pathway for users to navigate through the site. Help {loadposition breadcrumbsload}

Content

Content plugins run when specific kinds of pages are loaded. They do things ranging from protecting email addresses from harvesters to creating page breaks. Default on: Email Cloaking Help Load Module Help Page Break Help Page Navigation Help Rating Help Default off: Code Highlighting (Geshi) Help

Upgraders

If you are an experienced Joomla! 1.5 user, this Joomla! site will seem very familiar. There are new templates and improved user interfaces, but most functionality is the same. The biggest changes are improved access control (ACL) and nested categories. This release of Joomla! has strong continuity with Joomla! 1.6 while adding enhancements.

Typography

H1 ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmonpqrstuvwzyz H2 ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmonpqrstuvwzyz H3 ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmonpqrstuvwzyz H4 ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmonpqrstuvwzyz H5 ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmonpqrstuvwzyz H6 ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmonpqrstuvwzyz P The quick brown fox ran over the lazy dog. THE QUICK BROWN FOX RAN OVER THE LAZY DOG. Item Item Item Item Item Item Item Item Item tem Item Item Item Item Item Item Item Item

Professionals

Joomla! 1.7 continues development of the Joomla Framework and CMS as a powerful and flexible way to bring your vision of the web to reality. With the administrator now fully MVC, the ability to control its look and the management of extensions is now complete.

Random Image Module

This Module displays a random image from your chosen image directory. Help {loadposition randomimageload}

Related Items Module

This Module displays other Articles that are related to the one currently being viewed. These relations are established by the Meta Keywords.  All the keywords of the current Article are searched against all the keywords of all other published articles. Help {loadposition relateditemsload}

Sample Sites

Your installation includes sample data, designed to show you some of the options you have for building your website. In addition to information about Joomla! there are two sample “sites within a site” designed to help you get started with builidng your own site. The first site is a simple site about Australian Parks. It...

Search

The search component proviedes basic search functionality for the information contained in your core components. Many third party extensions also can be searched by the search component. Help

Search Module

This module will display a search box. Help {loadposition searchload}

Search

The search component uses plugins to control which parts of your Joomla! site are searched. You may choose to turn off some areas to improve performance or for other reasons. Many third party Joomla! extensions have search plugins that extend where search takes place. Default On: Content Help Contacts Help Weblinks Help News Feeds Help Categories Help

Site Map

{loadposition sitemapload} By putting all of your content into nested categories you can give users and search engines access to everything using a menu.

Statistics Module

The Statistics Module shows information about your server installation together with statistics on the Web site users, number of Articles in your database and the number of Web links you provide. {loadposition statisticsload}

Syndicate Module

The syndicate module allows you to display a link that allows users to take a feed from your site. It will only display on pages for which feeds are possible. That means it will not display on single article, contact or weblinks pages, such as this one. Help {loadposition syndicateload}

System

System plugins operate every time a page on your site loads. They control such things as your URLS, whether users can check a “remember me” box on the login module, and whether caching is enabled.  New in 1.6 is the redirect plugin that with the redirect component assist you in managing changes in URLs. Default...

The Joomla! Community

Joomla! means All Together, and it is a community of people all working and having fun together that makes Joomla! possible. Thousands of people each year participate in the Joomla! community, and we hope you will be one of them. People with all kinds of skills, of all skill levels and from around the world...

The Joomla! Project

The Joomla! Project consists of all of the people who make and support the Joomla! Web Platform and Content Management System. Our mission is to provide a flexible platform for digital publishing and collaboration. The core values are: Freedom Equality Trust Community Collaboration Usability In our vision, we see: People publishing and collaborating in their...

Latest Users Module

This module displays the latest registered users. Help {loadposition userslatestload}

Scroll to top