Philip Treacy, Author at My Online Training Hub • Page 4 of 5

January 31, 2017

Return An Array From A UDF

This post is going to look at how to return an array from a udf. My last post looked at

January 24, 2017

Return a Range from a UDF

I've previously written a UDF to count colored cells and then perform various maths functions on the values in those

November 25, 2016

Count, Sum and Average Colored Cells

We often get asked if we can count colored cells on a worksheet, and yes you can. You could count

November 11, 2016

Create an Excel Add-In for User Defined Functions (UDF’s)

In a previous post I discussed storing your UDF's in PERSONAL.XLSB so that you could use them in any workbook.

creating a reference to personal.xlsb for user defined function udf

October 27, 2016

Creating a Reference to PERSONAL.XLSB for User Defined Functions (UDF’s)

You've created your own PERSONAL.XLSB and want to use it to store VBA that you are going to use all

September 21, 2016

Creating Multi-Function UDF’s

My last post looked at creating a simple user defined function (UDF). This post looks at a little more advanced

September 15, 2016

Creating a UDF (User Defined Function) in Excel

Any user of Excel will have used its functions. From the humble SUM to VLOOKUP, INDEX and MATCH, OFFSET and

Clearing Downstream Dependent Data Validation Lists

August 25, 2016

Clear Downstream Dependent Data Validation Lists

Following on from Mynda's post last week on Dependent Data Validation, I've written a little VBA that enhances the functionality.

July 28, 2016

Moving VBA Code and Forms Into PERSONAL.XLSB

After you’ve written some VBA, or created your own form, or you’ve found some useful code on the internet, you

Hide, Delete, Sort Sheets with VBA in Excel

July 21, 2016

Hide, Delete and Sort Sheets With VBA Userform

You have a workbook with lots of sheets and you want to do things like hide sheets, delete some sheets

June 30, 2016

How to Record a Macro in Excel

Prompted by a couple of questions on our Excel forum I thought I'd write about how to record a macro

June 15, 2016

Excel Status Bar

In my previous blog post I created a progress bar using a userform. We can use this progress bar to

June 9, 2016

Excel Progress Bar for VBA

If your code runs for any noticeable amount of time, you might find that using some sort of visual feedback

April 27, 2016

Maximize Excel Across Multiple Screens

If you want to maximise Excel on a single monitor you can just click on the maximize button That's ok

show text, numbers, dates, formulas on sheet

April 6, 2016

Highlight Text, Numbers, Dates and Formulas on a Sheet

Imagine that you are preparing data for a pivot table, and you want to make sure that all the cells

March 15, 2016

Get Started With VBA – 3 Easy Examples

You can create very complicated and powerful VBA routines in Excel, but not all VBA code has to be like

selecting multiple items in data validation list

March 3, 2016

Populating Multiple Cells from Single Data Validation (Drop Down) List

If you want to limit the choices available for the values that can be entered into a cell, you could

December 4, 2015

Error Handling in VBA

If VBA can’t execute a statement (command) then a run-time error occurs. By default Excel deals with these, so when

November 6, 2015

Finding File Meta Data Using FileSystemObject

In a previous post I looked at using the FileSystemObject to search through folders on your computer's disk and create

October 1, 2015

Save Settings in Registry Using Excel

If you want to save data from one Excel session to another, you can store this in the Windows registry.

September 25, 2015

Automatically Add Items to Data Validation List

If you are using data validation (and you should) to help you with your data entry, then you might find

September 4, 2015

Add Data to Combo Box Drop Down List in Excel Form

If you've been following the previous posts in this series you'll know that by now we have a user form

Excel forms, insert, update, delete data

August 21, 2015

Excel Forms – Insert, Update and Delete

Carrying on with the Excel form I created in an earlier post, I’m adding code to allow us to insert

July 9, 2015

Excel Form Data Validation

When creating Excel userforms it seems like a good idea to check the data going into the form before adding

July 9, 2015

Excel Date Picker

In my previous post about Excel forms the date picker I used was a 32 bit ActiveX control. If mentioning

July 2, 2015

Excel Forms

Excel forms, or Userforms, can be used to collect information from a user, validate it, then enter that data into

June 17, 2015

Apps for Office In Excel

I want to be clear that when I say apps for Office in Excel, what I am talking about are

Highlight Selected Cell In Excel and Preserve Cell Format

May 20, 2015

Highlight Selected Cells in Excel and Preserve Cell Formatting

When you select a cell, or cells, in Excel, the row and column headers change color to indicate what you

March 30, 2015

Pausing or Delaying VBA Using Wait, Sleep or A Loop

You may want some way of pausing or delaying VBA code execution and you can do this with two functions

February 19, 2015

Find Missing Numbers, Really Fast

A while ago I wrote a routine using the .Find method to find missing numbers in a sequence. So if