This is a comprehensive article about creating and using objects in Excel VBA.

If you are serious about learning VBA then it is important to understand VBA Objects. Using objects is not that difficult. In fact, they make your life much easier.

In this post, you will see how VBA makes brilliant use of objects. How objects such as Collections, Workbooks and Worksheets save you much complexity, time and effort.

Topics include:

• What is a VBA object?
• Object components.
• Creating a VBA object.
• Assigning VBA objects.
• VBA objects in memory.
• Why Set is useful.

This spreadsheet competency framework is a structure for assessing ability and proficiency when using spreadsheets.

It can be used by recruitment agencies, companies, job seekers, training providers and anyone else who uses spreadsheets to carry out their role.

The framework consists of four key skill groups, described as levels. Each has its own expected core competencies, and other indicative skills:

• Basic user. Carries out data entry tasks in spreadsheets, and will have only the most fundamental knowledge necessary to be able to interact with a spreadsheet.
• General user. Modifies spreadsheets, rather than creates sophisticated spreadsheets from scratch.
• Creator. Uses spreadsheets as a primary element of their role, and needs to consider how to create and manage spreadsheets of a greater degree of complexity.
• Developer. Truly expert spreadsheet users, who are familiar with most of the core functionality of spreadsheet packages, and are able to develop high-complexity spreadsheets in a multi-user environment.

This competency framework builds on the twenty principles for good spreadsheet practice.

We explain what the Compound Annual Growth Rate (CAGR) is, and how to make a clear and easy-to-understand CAGR formula in Excel.

In simple terms, CAGR measures the return on an investment over a period of time. CAGR is often used by financial analysts, investment managers and business owners to figure out how their business has developed or compare revenue growth of competing companies.

We show four methods for calculating a CAGR:

• Create a CAGR calculator in Excel using an arithmetic equation.
• CAGR formula based on the POWER function.
• CAGR formula based on the RATE function.
• CAGR formula based on the IRR function.

Writing a long formula is not easy, even for an advanced Excel user. What I mean long is a formula with many nested FUNCTIONS within a single formula. The difficulty I am talking about is not related to whether you understand the functions. It is more about the concentration and carefulness required for writing the formula.

The key to writing a long, complex formula is to break the formula into bite-size formulas with helper columns. This article illustrates the process using an example of converting poorly written text into a consistent format.

On 23 August a paper by three Australian authors gained much publicity about how Excel interprets data entry and how the users don't check their data.

The root problem is that the researchers who uploaded their data into Excel NEVER checked them. The reason they never check is because Excel is seen as a simple application, not programming or software development or data management.

Abstract of the source paper: "The spreadsheet software Microsoft Excel, when used with default settings, is known to convert gene names to dates and floating-point numbers. A programmatic scan of leading genomics journals reveals that approximately one-fifth of papers with supplementary Excel gene lists contain erroneous gene name conversions."

This article provides a comprehensive introduction to the use of Class modules in VBA.

Topics covered include:

• Why we use objects.
• Advantages and disadvantages of Class modules.
• Explanation of how to use VBA Class modules.
• Examples of using Classes in VBA.

Named ranges are one of these crusty old features in Excel that few users understand. New users may find them weird and scary, and even old hands may avoid them because they seem pointless and complex.

But named ranges are actually a pretty cool feature. They can make formulas *a lot* easier to create, read, and maintain. And as a bonus, they make formulas easier to reuse (more portable).

This article has 20 tips for creating and using named ranges in Excel, grouped as follows:

• The basics of named ranges in Excel.
• Named ranges in formulas.
• Key benefits of named ranges.
• Dynamic named ranges.
• Deleting named ranges.

An Excel pioneer, Peter Bartholomew, likes to create Excel names using odd characters.

For example, he uses a ? at the end of a name to identify it as a Boolean value like YES/NO or TRUE/FALSE. He also uses ← to indicate the name points to a prior value.

I like his concept. But I was shocked to see his names because I was led to believe those characters were illegal.

I checked my sources which include Microsoft's reference and the best VBA websites. None of them provided a complete list of invalid characters and some were just plain wrong.

So I rolled up my sleeves and wrote some code to create Excel names using the ASCII standard (first 255) character set:

• Symbols shaded in red cannot be used anywhere in Excel names.
• Symbols shaded in yellow cannot be used as the first character in Excel names.
• All other codes can be used anywhere in Excel names.