Sculpt

Professional Excel development

Get values by looping through an array variable

leave a comment »

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.

Read the rest of this entry »

Written by Austin

January 26, 2007 at 12:35 am

Compile data from several workbooks

leave a comment »

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. Read the rest of this entry »

Written by Austin

January 23, 2007 at 10:08 pm

Check a worksheet exists in a specified workbook

leave a comment »

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. Read the rest of this entry »

Written by Austin

January 22, 2007 at 9:16 pm

List files in a specified folder

leave a comment »

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

Written by Austin

January 9, 2007 at 11:54 pm

Create a Function Library Add-In

leave a comment »

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. Read the rest of this entry »

Written by Austin

January 8, 2007 at 11:16 pm

Create a dynamic named range using OFFSET

leave a comment »

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. Read the rest of this entry »

Written by Austin

January 4, 2007 at 2:37 pm

Create a custom function

leave a comment »

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. Read the rest of this entry »

Written by Austin

January 4, 2007 at 1:10 pm

Posted in functions, if(iserror), vba