Get values by looping through an array variable

When we added a custom command bar we hard-coded our values for the dropdown control. In reality, we’d want to avoid this: hard-coding would reduce the life-span of the workbook and make it very inflexible.

Compile data from several workbooks

This code shows you how to compile data from several similarly-structured workbooks into one worksheet. It utilises much of the code from the previous two posts, Check a worksheet exists in a specifed workbook and List files in a specified folder.

Check a worksheet exists in a specified workbook

When opening a specified workbook, you may need to check that it contains a particular worksheet. First open the workbook – to do this, we’ll use the File Dialog picker. The code below opens the File Dialog, then opens the selected workbook.

List files in a specified folder

There are numerous ways of listing files located in a specific folder. Below shows one of the easier ways of doing this.

Create a Function Library Add-In

A Function Library Add-In can be a convenient way of distributing your custom functions thoughout your organisation. The Add-In can then be installed, through the Tools > Add-Ins Excel menu, by the user when required.

Create a dynamic named range using OFFSET

Named Ranges are a powerful tool in Excel – ranges can be used to shorten commonly-used functions and are more user friendly. Yet named ranges refer to absolute cell references. When you’re using a dynamic record list, you’ll need to ensure the named range is also dynamic.

Create a custom function

When you frequently use a non-standard function, it may be an idea to create a custom function at workbook start-up using a User-defined function (UDF). The IF(ISERROR) function is one example of this.

Add a custom icon to a custom command bar

You can add an icon directly to your custom command bar directly through Excel with no need for Add-Ins.

countif

As with the basic SUMIF function, you might want to perform COUNTIF using multiple criteria?

Customise the right-click Cell command bar

You might need to disable some functionality in the standard right-click cell command bar to halt users performing functions that could threaten the integrity of your workbook. You may also want to add some functionality not available in the standard cell command bar.