Blog Archives

Excel – Dealing with Non-Printable Characters

Every so often you may encounter a problem with Excel where a function is not working properly, even though you know that your function syntax is correct. And when you manually check the data you find that the function is not returning the correct answer.

So what is going on?

This problem happened to me again recently and it took a little while for the penny to drop as it is fairly rare, but highly annoying when it does.

And it is down to something called “non-printable characters”. These pesky characters are invisible, which makes it doubly difficult to identify. These tend to crop up in reports that have been downloaded from other systems, which for reasons unknown, slip in characters of some sort that cannot be seen or identified on screen, but nevertheless cause mayhem with a number of functions.

As an example I’ll use the data which caused me the most recent problem.

Sample data causing the problem

Sample data causing the problem

The company I work for uses a web based database to manage courses and delegates and some information was extracted to generate a catalogue within a workbook (sample above). I wrote some VBA code to help users find courses either by name of ID number and in one instance, the dreaded end-debug screen kept on appearing.

The form I created allows users to enter partial names or IDs and search for information based on that partial string. When they entered the number 26, a message told them that two records had been found.

Message returned by the VBA code

Message returned by the VBA code

This was done using a COUNTIF function. However, there were in fact 6 records that contained the number 26.

...but autofilter finds more records

…but autofilter finds more records

So on the one hand the COUNTIF function was finding 2 records, but the loop in the code which populated an array in the memory was crashing as it found a third record that contained the number 26. The dynamic array was not big enough as the size was recalculated based on the findings of the COUNTIF function.

Initially I looked at formats – text vs. numbers for example.

I also looked for any spaces that might have caused an issue.

I manually added a COUNTIF function in the worksheet to double-check the findings of the function created in VBA but this gave the answer 2 as well.

=COUNTIF(C:C,”*26*”)

Finally, realising that this was an extract from a non-Excel source, I tried using the CLEAN function suspecting that there may be some non-printable characters in there somewhere. The CLEAN function removes all non-printable characters from text. The syntax is very simple:

=CLEAN(cell reference)

In a new column I wrote this formula and then copied all the “cleaned” text values and used PASTE SPECIAL VALUES over the original cells and retested the COUNTIF function.

…and hey presto…it found all 6 records.

So next time your functions are not working and seem incapable of identifying values in cells, try applying the CLEAN function to your data, it might just be the solution to your problem.

Advertisements

Excel – Using INDEX & MATCH (Part 1)

If you are familiar with the VLOOKUP and HLOOKUP functions then you will know how useful they can be. There is however one drawback in that they only search for the look up value in the first column or row of a selected look up table. In the majority of cases this is fine, but there may be times when you need to look both to the right and left of the look up column.

This is where INDEX and MATCH functions come in. On their own, they are probably two of the most useless functions going – together they are extremely powerful and sometimes jokily referred to as VLOOKUP on steroids! But to understand how they work we need to take at look at each one in turn.

MATCH

The basic syntax for MATCH is:

=MATCH(lookup_value,lookup_array, [match type])

Which in plain English is…

=MATCH(what value are you looking for, where are you trying to find it, do you want a near or exact match)

Whereas V/HLOOKUP require a TRUE or FALSE (1 of 0) value in the final argument, MATCH has three options;

1 = nearest match under/less than the look up value

0 = exact match

-1 = nearest match over/greater than the look up value

MATCH basically returns the position (represented by a number) of the look up value in a single row or column.

If I am looking for “X” in a row of cells then my formula would be =MATCH( “X”,A1:F1,0)

Finding x's position with MATCH

Finding x’s position with MATCH

My function would return the number 4 i.e. it is in the 4th cell, reading from left to right in my selected cells. If looking in a single column of cells then it returns the position reading from top to bottom. Not the most useful bit of information you are ever likely to extract from a spreadsheet if the truth be told!

INDEX

There are two versions of INDEX, but the one I am looking at here is the basic one that only looks at one table. The syntax for INDEX is:

=INDEX(array, row_num,[column_number])

…or in plain English;

=INDEX(range to check, which row do you want to look in, which column do you want to look in)

Using INDEX on its own is not the most useful thing you can do in Excel. For example;

=INDEX(A1:D7,5,3) would return 32

I could have just as easily written =C5. So it looks like a very long winded way of referencing cells. However…when you combine MATCH and INDEX together you get a completely different beast.

I’ll use a table we use in class as it is good way of showing how it can be used.

Mileage grid

Mileage grid

As you can see, we have a number of towns/cities around the UK with distances between them. Rather than search myself for distances between two given locations, I want to be able to use a series of drop down lists to select locations and a formula to find the correct distance between them. So I need something dynamic to do this, and this is where INDEX and MATCH come into play.

I’m going to use a couple of drop down lists to make my choices easier rather than type out the names each time. I’ll assume you know how to create VALIDATION lists, if not please see my blog http://wp.me/p2EAVc-5b on how to set those up.

To break this down I will use the MATCH function on its own to return the row and column numbers for each town/city I choose from my drop down lists. First, calculate the row position;

=MATCH(S19,A1:A17,0)

In this example, I have chosen Southampton and the MATCH function returns the number 5.

Now for the column position;

=MATCH(S20,A1:Q1,0)

This time I’ve selected Nottingham and therefore returns the number 10.

Finding towns with MATCH

Finding towns with MATCH

Change the location in each of the drop down lists and see the values from each MATCH function change. So now you can see that MATCH is a dynamic function when linked to something like a VALIDATION list.

As we saw before, INDEX relies on row and column numbers to work. Normally, these would be manually entered and therefore fixed. As MATCH can be dynamic, it returns a number we can use to our advantage by embedding a MATCH function in the row and column arguments of INDEX.

How to next MATCH in INDEX

How to next MATCH in INDEX

Our new nested function will look like this;

=INDEX(A1:Q17,MATCH(S19,A1:A17,0),MATCH(S20,A1:Q1,0))

Although it looks complicated, and may take a while to get used to the nested syntax, it is basically an INDEX function with two MATCH functions sitting inside it.

This now gives you a totally dynamic search function. Although in this example the look up values are the top row and first column, INDEX & MATCH will work using the look up on any column or row in your table.

What’s really neat, is that if you decide to move the town names around a bit (sticking to the same column or row mind you!) it will still work because the MATCH function will look for an exact match of the text value chosen in the drop down. Unless the spelling changes, it will find it wherever it is in the row or column!

In Part 2 I’ll show you how you can use INDEX and MATCH across multiple tables as well as extracting information from one column based on data found in another.

Excel – MIN & MAX vs. SMALL & LARGE Functions

The MIN and MAX functions are two of Excel’s basic functions to find the single lowest or highest value in a range of cells and they appear in the drop down list on the AUTOSUM button.

Drop down next to AUTOSUM

Many beginners often ask in training sessions when they would be likely to use these functions, but once you know them, it’s surprising how much use you can actually get out of them.

The other two functions, SMALL and LARGE also return lowest and highest values respectively but with the added option to find the 2nd , 3rd, 4th, 5th lowest/highest ….etc.

The basic syntax for MIN and MAX is;

=MIN(range to test)

=MAX(range to test)

If we look at a basic table with a range of values…

Basic table to use MIN, MAX etc.

You might be able identify the lowest or highest values as this is only a small number of cells, but try doing that with 1,000s or more – it would be very easy to get the wrong answer.

In this example, to find the lowest single value our formula would be;

=MIN(A2:C15) giving us the answer 45.

To get the highest;

=MAX(A2:C15) giving us the answer 975.

Simple, quick…enough said.

Now let’s take a look at SMALL and LARGE.

The basic syntax for these is;

=SMALL(range to test, nth value)

=LARGE(range to test, nth value)

Returning to our data table above, we could extract the following lowest values;

=SMALL(A2:C15,1)
giving us the answer 45…exactly the same as MIN i.e. lowest value

=SMALL(A2:C15,3) giving us the answer 124 (3rd lowest value)

=SMALL(A2:C15,12) giving us the answer 239 (12th lowest value)

And to get the largest values;

=LARGE(A2:C15,1)
giving us the answer 975…exactly the same as MAX i.e. highest value.

=LARGE(A2:C15,5) giving us the answer 863 (5th highest value)

=LARGE(A2:C15,9) giving us the answer 787 (9th highest value)

So there you have finding lowest and highest values. As with all Excel functions, combining these with other functions will give you a lot more power to analyse and find values in your spreadsheets. Look out also for my blogs on DATE and TEXT functions.

 

Excel – Counting Functions

Abacus Counting

Ever sat at your desk holding a pen to the screen counting cells? I know I have in the past. Well…no need, Excel has a bunch of counting functions to help you out.

In this blog I am going to look at five counting functions:

  • COUNT
  • COUNTA
  • COUNTBLANK
  • COUNTIF
  • COUNTIFS

COUNT

The most basic of the counting functions. You can find this in the drop down list next to the AUTOSUM button.

Basic COUNT function

Basic COUNT function

Somewhat helpfully, it shows COUNT NUMBERS which is exactly what COUNT does. It only counts cells that contain numbers and ignores text cells. Alternatively, just type =COUNT( and select the cells you want to count. In the screenshot below you can see what COUNT returns depending on the contents of the selected cells.

Applying the COUNT function

Applying the COUNT function

COUNTA

This is the counterpart to COUNT. This will count cells that contain anything – text or numbers. If you want to count cells that only contain text then you have to use COUNTA otherwise you get a count of 0. Again, the screenshot below shows you the various results based on the contents of the cells

Applying the COUNTA function

Applying the COUNTA function

COUNTBLANK

Does exactly what is says…counts blank cells. Can’t say any more than that really. Note however, that if a cell contains a space (typed by accident probably) it will ignore it as the cell is no longer “blank” as it contains a space character.

Using COUNTBLANK - watch out for spaces!

Using COUNTBLANK – watch out for spaces!

COUNTIF

Slightly more complex than the previous three but very useful. The COUNTIF function counts how many times a specific value (numerical or text) appears in a range of cells. For example you might be organising an event and need to get people’s choice for main course at the evening dinner. Let’s say there is a choice between fish, beef, chicken and vegetarian. You need to know how many people have opted for each of the mains – this is where COUNTIF can help.

The syntax for COUNTIF is as follows:

=COUNTIF(range of cells to check, what to count)

If you are counting text values then the “what to count” bit must be in double speech marks i.e. “fish”.

If you want to count how many times a number appears then no need for any speech marks. However, if you are counting how many numbers are greater than, less than etc. , you need to put this in speech marks too (this often catches people out and is not really logical). For example I want to count numbers greater than 10, my formula would read:

=COUNTIF (A1:A20,“>10”)

A few examples below:

Using COUNTIF to work out menu requests

Using COUNTIF to work out menu requests

COUNTIFS

Whereas COUNTIF checks a single range of cells, COUNTIFS allows you check multiple ranges each with different criteria. In order to count, all criteria must be met in all of the selected ranges. You can create up to 127 different range/criteria combinations.

The syntax for COUNTIFS is as follows:

=COUNTIFS(range1 to check,range1 criteria, range2 to check, range2 criteria….)

Using our example of the menu choices, let’s extend our choices to include wine. So people can choose a main and a choice of red, white or rose. Our function will then have to count people who want fish and white wine, fish and rose or (perish the thought) fish and red wine, and then the same for each of the other mains.

Using COUNTIFS to work out main and wine combos

Using COUNTIFS to work out main and wine combos

A closer look at the COUNTIFS function

A closer look at the COUNTIFS function

So there you have a selection of counting functions. Never again will you need your biro to point to cells on screen whilst trying to scroll down at the same time!

Check out my other blogs on functions – date and text functions.

 

 

Excel: Text Functions (Part 2)

Continuing from my last blog, here are the remaining text functions I have found useful:

LOWER

Syntax: =LOWER(text or cell ref)

This will convert any characters in UPPER case to LOWER case. Your immediate thought will probably be ‘when am I ever going to need that?’, but people enter data into your spreadsheets all in lower case or upper case or some weird illogical combination of both, and so to give your text values some sort of uniform appearance, this sort of function comes in handy. You will most probably use this in combination with other text functions.

Lower

UPPER

Syntax: =UPPER(text or cell ref)

This gives you the opposite to the previous function, converting all LOWER case characters to UPPER case. As with LOWER, you will probably use it in combinations with other functions.

Upper

PROPER

Syntax: =PROPER(cell ref)

A function of limited use really, and probably limited to using with names. This one capitalises the first letter of every word in a cell. This can be useful when working with names that have been entered in some haphazard way…

Proper

…but avoid using with sentences or blocks of text.

Proper_text

Not pretty!

FIND

Syntax: =FIND(what to find, cell ref)

Another text function which on its own is of little interest but works best when combined with other functions. Basically it gives you the position (reading from left to right) of whatever it is you are trying to find. If you try finding a text string (i.e. multiple characters or even a word) it will give you the position of the first character or digit of what you are trying to find.

Find

In column B, I am looking for the dash (-) and in column C, looking for the text string “Sm”. Not really of much use until combined with other functions to be honest but check out the combinations section further on for some examples. Also note that it will only give you the position of the first instance it finds starting from the left.

EXACT

Syntax: =EXACT(cell ref 1, cell ref 2)

Basically, this tells you whether contents of two cells are identical or not. Another one that is best used in combination with other functions, testing values before they are manipulated in some way.

Exact

In column B, I am looking for the dash (-) and in column C, looking for the text string “Sm”. Not really of much use until combined with other functions to be honest but check out the combinations section further on for some examples. Also note that it will only give you the position of the first instance it finds starting from the left.

SUBSTITUTE

Syntax: =SUBSTITUTE(cell ref, old text, new text [,optional instance number]))

Whereas REPLACE replaces everything that meets your criteria, SUBSTITUTE gives you the choice to (effectively) replace every instance of your criteria or a specific instance i.e. the 2nd or 3rd time etc. that it appears in the cell. Let’s say our part number has been changed where the 2nd dash needs to be substituted with a forward slash. This function, has quite a specific use and may not be one you will use on daily basis, but it is worth knowing and as with most functions works best in combination with other functions.

Substitute

REPT

Syntax: =REPT(“character/letter/symbol”, number of times to be repeated)

In terms of functions, this one ranks amongst one of the most pointless….but it can come in very handy to create a fake chart! Basically you pick a character and work out how many times you want to repeat it in a cell.

Rept

Sparklines in 2010 have made this a tad redundant but can still be quite effective for a quick chart effect, and you can get creative using Wingdings or Webdings as well as apply conditional formatting! Awesome!

And then it’s all about putting them together in various combinations to extract and modify text…

Other functions in the TEXT category that may be of interest but personally have either never used or used just once for a very specific job are;

REPLACE – replaces characters but you are more likely to use CTRL + H and use the replace dialog box.

CHAR and CODE if you need to generate random characters based on their code, or to work out the code for a given character e.g. A= 65, a = 97……85 = U.

SEARCH returns the position of a specific character (reading from left to right) within a text string specifying from which point you want to start searching.

TEXT allows you to format a value into a specific text format which of course means you can no longer perform calculations on that value.

VALUE is TEXT’s counterpart converting numbers formatted as text into numerical values.

So that’s TEXT functions in Excel, but as I have mentioned a few times, the power of these functions really comes through when you combine them together. Here are a few examples, but how many and how you combine them will depend entirely on your own requirements…

  1. =UPPER(LEFT(TRIM(A2),3))Removes any leading/trailing spaces, extracts the first 3 characters starting from the left and then converts them into upper case.
  2. =LEFT(A2,FIND(“ ”,A2)-1)Works out the position of the first space in the cell, takes away 1 from that value to give you the position of the last character before the space and extracts all characters up to that point.

    Left

  3. =IF(LEFT(A4,1)=”T”,A4,E4&”.”&D4&”@”&F4)If the first letter is a “T” then display that value, otherwise join a number of cells together adding a dot and an @ sign to make up an e-mail address.
  4. =SUBSTITUTE(IF(ISERROR(LEFT(A2,FIND(“@”,A2)-1)),A2,LEFT(A2,FIND(“@”,A2)-1)),”.”,” “)This checks the cell contents to see if there is an @ symbol. If not (in this example…if the test produces an error) display the contents, but if no error is generated take all characters to the left of the @, and then replace any dots with spaces.

ScreenHunter_57 Mar. 04 20.15

So now it is just a case of experimenting with different combinations. If you are not confident nesting functions then build them up a bit at a time. Let’s take the first combination example:

=UPPER(LEFT(TRIM(A2),3))

Think about the problem logically to apply the functions in the correct order:

  • Remove any spaces that may or may not be there so we TRIM first as we don’t want to start counting characters before we remove any chances of counting spaces or non-printable characters.
  • Then what do we want to extract? So now we can apply the LEFT function to the output of the TRIM function.
  • Finally, we want to convert whatever comes out of that into UPPER case.

So apply one function at a time, make sure it works at each stage with each additional function. If you try to do it all in one go, chances are you’ll only confuse yourself and end up ‘correcting’ things that are fine and making things worse. With experience you will be able to string these together in next to no time.


 

Excel: Text Functions (Part 1)

Whenever people think of Excel they think of numbers and calculations. However, there is a wide selection of functions that allow you to work with and manipulate text. Perhaps not something you would normally consider doing when working with Excel but you’d be surprised how useful these functions can become.

Here is s selection of text functions I have found useful over the years, dealing with a variety of problems and situations.

CLEAN

Syntax : =CLEAN(cell ref)

This removes any non-printable characters. Seems like an odd thing, but I have found this to be quite a common problem when importing data from other systems such as SAP/Oracle etc. into Excel. These systems tend to export data in .csv format and sometimes directly to Excel but you will often find what look like spaces at the beginning or end of a text string or set of numbers. So you try using the TRIM function (see further on) to remove spaces but that doesn’t work as they are not ‘spaces’, and so you resort to removing them manually, or if there are too many to clear you just give up. This is where CLEAN comes in handy.

CONCATENATE

Syntax: =CONCATENATE(cell ref/”free text”, cell ref/“free text”,……)

This allows you join the contents of multiple cells into a single one. Let’s say you have a list of names, but the title, first name and surname are all in separate cells but you need to display it all in one.

Use the CONCATENATE function to join them together, referencing each cell reference separated by a comma.

ScreenHunter_55 Feb. 13 12.20

Unfortunately, the function has no idea that these are separate words so there are no spaces between them. To add these in we need to add space characters into our formula.

ScreenHunter_55 Feb. 13 12.21

Any ‘free’ text or spaces that you want to include in your final output must be in double speech marks (i.e. “text or space”).

Having said all of that, my preferred method of joining cell values together is by using & in my formula.

Using &

Which gives you the same end result as CONCATENATE.Your choice.

DOLLAR

Syntax: =DOLLAR(number or cell ref, decimal places)

Not an obvious function and the name can be misleading. Basically it formats a number to your SYSTEM’s currency. So if you are in the UK, somewhat confusingly it formats a number in GBP with a £ sign in front. Let’s say I want to display a comment combined with a cell value….

ScreenHunter_55 Feb. 13 13.22

It displays OK, but it has not brought through the currency formatting from the source cell (B1). To get this to display I need to use the DOLLAR function.

ScreenHunter_55 Feb. 13 13.24

In the example above I have set the number of decimal places to zero. If you don’t put anything in the decimal option, by default it will put in 2 decimal places. If you are working with any other currency and want to display the currency symbol then DOLLAR won’t help you – see below. For that you’ll need to build the currency code into your formula (=”Total Cost €”&B1).

ScreenHunter_55 Feb. 13 16.53

LEFT

Syntax: =LEFT(cell ref, number of characters to select)

This function lets you extract any number of characters (defined by you) starting from the left hand side of the cell. You could apply this to a text string directly in the formula but referencing a cell is the most likely scenario. For instance, I used this to extract the first two letters from a part number as this was the code for the business unit and was useful to extract when pivoting my data.

Left

LEN

Syntax: =LEN(cell ref)

This gives you the length of the contents of a cell. You will rarely use it on its own….the number of characters/numbers in a cell is rarely of interest, unless you are editing content to go out on Twitter and you need to check you have no more than 140 characters! Remember that spaces count as characters.

Len

Note that B4 has returned a length of 10 characters when in fact it should only be 9. That’s because there is a space at the end of excelmate. So that is something you need to check and plan for when using any text function. For that problem, you’ll need TRIM…read on.

TRIM

Syntax: =TRIM(cell ref)

Here’s one that will come in handy more often than you think. This removes any leading or trailing spaces in your cell. It won’t remove spaces between characters!

The ‘excelmate’ bit looks identical but it has removed the end space. If you check LEN again it will now give you the correct number of characters.

Len

RIGHT

Syntax: =RIGHT(cell ref, number of characters to select)

Similar to LEFT, except this time it selects characters from the right hand side of the cell.

Right

Note the odd result for ‘excelmate’. Only three characters are displayed instead of the four required. Remember….spaces are classed as characters too, so in this case there is a space on the end. Once again a good case to use TRIM.

MID

Syntax: =MID(cell ref, position of character to start from, no. of characters to select from start point)

So what do you do if you don’t want characters from either end of the cell contents? This is where MID comes in handy. The function looks in a cell, and you tell it to find a character by its position reading left to right i.e. character 3 would be the 3rd character starting from the left. Then, starting from that character, select a number of characters (e.g. 4). So using ‘excelmate’ as an example…

Mid

In this example I have used text directly in the formula but most likely you will refer to a cell.

In Part 2, I will be looking at the following functions:

LOWER, UPPER, PROPER, FIND, EXACT, SUBSTITUTE, REPT and a handful of other functions that you may need once in a  blue moon as well as a few examples of text function combinations.

Excel – Create Your Own Custom or User Defined Function (UDF)

As we all know Excel has more built in functions than any one human could ever need. Personally I have never had to use the BESSELK function and I very much doubt I ever will….in fact I have no idea what it does.

But what if there is some sort of calculation you do on a regular basis for which there is no built in function? You may well have a calculation that is specific to your company or job role and you use it on a daily basis. The calculation itself might be quite complex and requires lots of brackets, sub-calculations etc making it prone to typing errors or simply takes you ages to put together each time you need it.

What if you could create your own function with all the calculations built in and all you had to do was enter the basic data? Well…you can, with the help of VBA.
First of plan your function on paper; what is the calculation going to do and how?

For each element within your calculation, how many variables do you have? This is where it gets a bit confusing for people not used to working with or even knowing what I am referring to as variables.

Let’s try and build a simple example to understand what a variable is and how we can build variables into our function.

I want to be able to calculate the cost of a car journey so I can work out costs more accurately rather than just apply a flat rate per mile.

So what would I need to know to work this out?

• The distance driven

• The cost of fuel

• The average fuel consumption of the car being used to make the journey

Of these three values I need, how many will vary each time I need to do the calculation?

• Each journey will be different so the mileage will vary each time

• The cost of fuel is always changing (usually upwards!)

• Unless you only have one pool car, chances are you will be using different cars, each with their own fuel consumption rate

These are VARIABLES. In each case, there is no fixed value whenever we come to do the calculation. Hopefully, that clears up what a variable is.
So our calculation might be;

(distance driven/fuel consumption) * (cost of fuel * 4.5)

The *4.5 bit is more of a UK thing….fuel prices are quoted as cost per litre but we base fuel consumption on miles per gallon, and one UK gallon is 4.5 litres.
If we were to put numbers into this we might have;

(200miles/35mpg)*(£1.41 per litre * 4.5 litres) = £36.26
i.e. the cost of fuel to do a 200 mile journey in a car that does 35 miles per gallon.

Now that we have planned our function on paper, we now need to translate it into VBA code. Make sure you have a blank workbook open.

Go to the DEVELOPER tab and click on VISUAL BASIC, or press CTRL + F11. If you use 2007 or 2010 it may not be visible. For 2007 users, go to the OFFICE button, select EXCEL OPTIONS and on the POPULAR tab tick SHOW DEVELOPER TAB IN THE RIBBON. If you are on 2010, right click in the ribbon and select CUSTOMIZE RIBBON and tick the DEVELOPER option.

Remember earlier on in this task we opened a blank workbook? In the project window you should see something like VBAProject(Book1). Right click on the name of the workbook (project in VBA terms), click on INSERT, then click on MODULE. A MODULE is simply a folder that stores code within the workbook.

ScreenHunter_54 Jan. 30 12.36

You should now have a blank page to the right of the project explorer window…that’s the list of open workbooks on the left hand side.

ScreenHunter_54 Jan. 30 15.06

Now I need to declare my variables by giving them a name and data type.

ScreenHunter_54 Jan. 30 12.57

The data type you use is important when working with variables. Each variable can only store one type of information. The basic variable types you are likely to use are as follows:

ScreenHunter_54 Jan. 31 10.53

There are others but if you know these it’s a good start.

All that’s left now is to create the function itself substituting the names of the variables into our formula. Start by using the name you have given to your function (make sure you do this or your function won’t work);

ScreenHunter_54 Jan. 30 13.47

If you are not used to working with variables, this does seem a bit odd to start with, looking at a formula that contains words rather than numbers.

To test your new function start typing the name in a cell (in the same workbook that contains the code module) where you want to perform the calculation;

ScreenHunter_54 Jan. 30 13.51

The new function appears in the pop up list of functions! If you cannot remember the order of the variables you need to enter to make the function work, click on the fx button next to the formula bar. This will show you the wizard for your function, showing each variable you need to add in.

ScreenHunter_54 Jan. 30 13.53

Use the wizard as you would any normal function and hey presto…you have an answer without the need to type out all the brackets, constants, operators etc.

If you are happy with your new function and want to make it a permanent part of YOUR Excel you need to save the workbook containing the code as an ADD-IN.

Make sure the workbook is clear: no calculations or data in the workbook. Then just do a SAVE AS, selecting EXCEL ADD-IN(*.XLAM), name the workbook, ideally using the function name and click on SAVE.

To make the function available in Excel you need to activate the add-in. Go to the OFFICE button (2007) or FILE (2010), then click on EXCEL OPTIONS, and then ADD-INS.

ScreenHunter_54 Jan. 30 14.59

At the bottom of the window make sure EXCEL ADD-INS is showing and click on GO.

ScreenHunter_54 Jan. 30 15.00

Tick your newly saved function as an add-in, and click on OK. You are now free to use your function whenever you want in any workbook on your laptop/PC. So if you are some sort of budding quantum physicist or you just have a special way of calculating your home finances, you can now put your own functions into Excel to work on all your data.

 

Excel – How to use the VLOOKUP Function