**All Excel Templates **** **** Super Special $189.00! New Templates Added!**

We Recommend SmartDraw for Flowcharts **Newsletter**Subscriber Specials

What's New at OzGrid | Excel Tips and Tricks | Excel VBA Tips and Tricks ** What's New at OzGrid**

We are currently updating all our Training Lessons (for Excel 2003) and the new improved Level 1 course is now available. We will be working solidly this month to have the Level 2 lessons ready in time for the next newsletter. The Training courses are being offered in two modes, as discount downloadable training and now via our new Training Forum. Click here to see the details.

If you are a Forum member you are able to take the training in our new Training Forum at a discount. Click here to do this and see our prices. Level 1 now available.

With all the upgrades now complete to our site, the last few weeks have seen an increase in hits to the site. Hopefully the hits will continue to climb as Dave works solidly on marketing our new improved site and improving its ranking.

Still haven't received the stats forour book , hopefully we will be able to

report back in the next newsletter on its success!!

We also have an association with Amazon and have set-up a store here All proceeds fro this store will be used to enhance the question Forum and keep it totally free. See the "Books, Software, CD's, DVD's etc" link near the top of all Forum pages.

That's all for this month, we hope you enjoy the newsletter

This month, while answering a question on our question Forum I was reminded of an old Excel 4.0 Macro function called **EVALUATE.**

The question was along these lines. A user had cells with contents like;

A1= 21+69+89+25+31

A2= 21*25

A3= 100/10

A4= 100/10*(10*10+10)

A5= 100/10*10*10+10

None of these cells had an equal sign and as such the cells were seen as Text by Excel. The person wanted to leave the original cell contents intact and use Column **B** to return the result of the equations.

The usual suspect of *= "="&A1* was tried in cell **B1** but only resulted in **B1** showing *=21+69+89+25+31* and NOT evaluating the formula. It was at this point the word EVALUATE came into my mind. At first I tried *=EVALUATE(A1)*. Excel did not like this and came back with an error message "That Function is not valid". After this, the penny dropped fully I remembered how it had to be done. See the steps below

1) Select cell **B1**

2) Go to **Insert**>**Name**>**Define**

3) Type the name **Result** (can be any valid range name)

4) In the **Refers to:** box type: *=EVALUATE($A1)*

5) Click **Add** then **OK**.

It is very important to note that I selected cell B1 and used a Relative Row reference for **$A1**.

I then simply entered *=Result* into cell **B1** and copied it down and it

worked! It even obeys the use of Parenthesis as in the case of: 100/10*(10*10+10)

Another interesting one I answered was for a user who wanted to **stop** his SUM

Function reference automatically changing when adding a cell and shifting all to the right. For example, in cell **A1** was the Function: *=SUM($B1:$Z1)*

The user was inserting cells in Column B and this was causing *=SUM($B1:$Z1)*

to change to *=SUM($C1:$AA1)* when inserting a cell in **B1**. That is, selecting cell **B1**, right clicking, choosing **Insert** and then checking **Shift cells right** and clicking **OK**.

The solution was in the use of the **INDIRECT** function.**From the Excel Help;***Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.*

What I suggested was to use: *=SUM(INDIRECT("$B1:$Z1"))* and this stopped the Column references changing. Unfortunately it caused another issue and that was, copying the formula down rows meant the Row references were not changing.

To overcome this new problem I ended up with the formula; *=SUM(INDIRECT("$B" & ROW() & ":$Z" &ROW()))*

This solved the problem. Just be aware that both the INDIRECT and ROW function are Volatile and will recalculate each time a change is made in Excel. For this reason, their use should be restricted to prevent calculation slow down.**Volatile Functions**

Volatile functions are simple functions that will recalculate each time a change of any sort occurs in any cell on any worksheet. Most functions will only recalculate if a cell which they are referencing has changed. Some of the most common volatile functions used are undoubtedly the NOW() and TODAY() functions. If you are going to be using the result of these functions frequently throughout your spreadsheet, avoid the temptation of nesting these functions within other functions to get your desired result. Instead, simply type the volatile function into a single cell on your spreadsheet and reference that cell from within other functions. This alone can potentially cut down on the amount of volatile functions by hundreds, if not thousands at times.

In April we uploaded to our site a free open source Excel Add-in containing lot's of custom functions.DOWNLOAD FROM HERE

This month I will again show how to use a few of these and explain the code that drives them.**NOTE:** Some bugs have been fixed so you should download the latest version from the URL above.

This month I will again show how to use a few of these and explain the code that drives them.****FindOffset****

Function that will search a range for a value, then offset x rows and x columns from the found result. Used like; =FindOffset($A$1:$E$10,"Dog",2,3)

- Where $A$1:$E$10 the range to look for "Dog"
- 2 (optional) tells the function to offset 2 columns right from the cell

housing "Dog" - 3 (optional) tells the function to offset 3 rows down from the cell housing "Dog"

So, if the word "Dog" was in cell B5 the function would return the value in cell D8 (2 columns right and 3 rows down from B5).***CODE*****Function FindOffset(LookInRange As Range, FindVal, _Optional ColOffset As Long, Optional RowOffset As Long)**

Dim lCount As Long, lRow As Long

On Error Resume Next

For lCount = 1 To LookInRange.Columns.Count

lRow = Application.WorksheetFunction.Match _

(FindVal, LookInRange.Columns(lCount), 0)

If lRow > 0 Then

FindOffset = LookInRange.Cells(lRow, lCount) _

(RowOffset + 1, ColOffset + 1)

Exit For

End If

Next lCount

On Error GoTo 0

A For loop is told to loop as many times as there are Columns in the Range variable LookInRange, that is:LookInRange.Columns.Count

Each time the code loops the WorksheetFunction.Match is used to see if the value being searched for (FindVal) is in any of the Columns of the range LookInRange. If it is, If lRow > 0, will return True (a number > 0) and enter the If Statement. Here it encounters the code to specify the cell to offset from, that is LookInRange.Cells(lRow, lCount). The variablelRow houses the correct row number and lCount the correct column number. So, in the case of =FindOffset($A$1:$E$10,"Dog",2,3) where "Dog" is in cell B5 LookInRange.Cells(lRow, lCount) would beLookInRange.Cells(5, 2) which is relevant to LookInRange ($A$1:$E$10) as so is B5. 5th row of $A$1:$E$10 and 2nd Column.

The

Once it has done this, the Exit For tells the Function to leave the loop early and display the result.

Will return TRUE or FALSE depending on whether the cell referenced houses a formula or not. Used like:

Has_Formula = Check_Cell.HasFormula

Check_Cell.HasFormula will return TRUE if the cell, stored in Check_Cell, has a formula and FALSE if it doesn't. The TRUE or FALSE is then passed to Has_Formula.

Will return the last occupied cell from a single column or row range. Used like: =Last_Cell_value($A$5:$Z$5) (last occupied cell in single row) or=Last_Cell_value($A$1:$A$500) (last occupied cell in single column).

'

' Last_Cell_value Macro

' Returns the value in the last occupied cell of a _

single column or row range. *Range cannot include _

row 65536 or column IV.*

If Col_or_Row_Range.Columns.Count = 1 Then

If Col_or_Row_Range.Cells _

(Col_or_Row_Range.Rows.Count, 1) <> "" Then

Last_Cell_value = _

Col_or_Row_Range.Cells(Col_or_Row_Range.Rows.Count, 1)

Else

Last_Cell_value = _

Col_or_Row_Range.Cells _

(Col_or_Row_Range.Rows.Count + 1, 1).End(xlUp)

End If

Else

If Col_or_Row_Range.Cells _

(1, Col_or_Row_Range.Columns.Count) <> "" Then

Last_Cell_value = _

Col_or_Row_Range.Cells(1, Col_or_Row_Range.Columns.Count)

Else

Last_Cell_value = _

Col_or_Row_Range.Cells _

(1, Col_or_Row_Range.Columns.Count).End(xlToLeft)

End If

End If

The code first checks to see if the range is a single column viaCol_or_Row_Range.Columns.Count. If it is, True is returned and it then checks to see if the last cell in the range has any value in it.

If Col_or_Row_Range.Cells _

(1, Col_or_Row_Range.Columns.Count) <> ""

If the last cell does have a value, this value is passed to the function

Last_Cell_value = _

Col_or_Row_Range.Cells(Col_or_Row_Range.Rows.Count, 1)

And the function is done.

If there is no value in the last cell;

Last_Cell_value = _

Col_or_Row_Range.Cells _

(Col_or_Row_Range.Rows.Count + 1, 1).End(xlUp)

is used which in the case of Col_or_Row_Range being $A$1:$A$20 it would use;Col_or_Row_Range.Cells(21, 1).End(xlUp)This is the same as selecting cell A21 and pushing

The second half of the code is basically the same, except it works with the Column count of Col_or_Row_Range and uses xlToLeft as opposed toxlUp.

**ADVERTISEMENTS**

Artificial neural network software for stock markets!

**MAIN SOFTWARE CATEGORIES**