Archive for the ‘excel’ Category
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. Read the rest of this entry »
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. Read the rest of this entry »
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. Read the rest of this entry »
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. Read the rest of this entry »
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. Read the rest of this entry »
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. Read the rest of this entry »
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. Read the rest of this entry »
countif
As with the basic SUMIF function, you might want to perform COUNTIF using multiple criteria? Read the rest of this entry »
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. Read the rest of this entry »
