All Excel Templates Super Special $189.00! New Templates Added!
We Recommend SmartDraw for Flowcharts NewsletterSubscriber Specials
 SmartDraw is the quick and easy way to draw quality flowcharts, org charts, web graphics, and business presentations. You can try SmartDraw free for 30 days and see why it was voted "Best Business Program" two years in a row. For business charts and diagrams- ISO 9000 Flowcharts, Floor Plans, Circuit Diagrams, Flow Charts, Org Charts, VisualScript XML, Floor Plans, Business Forms, Network Diagrams, Circuit Diagrams, Engineering Diagrams, Flyers, Maps, Timelines, Clip Art, and Web Graphics and MUCH MORE |
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)
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
End Function
EXPLANATION
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 Item Property is then used to Offset from this cell, that is (RowOffset + 1, ColOffset + 1) We add 1 to each as the Item Method Start s from 1 as opposed to 0 for the Offset Property. See Item Property as it applies to the Range Object for details. It is a faster alternative to the Offset Property. However, Offset Property can take negative numbers, while the Item Property cannot.
Once it has done this, the Exit For tells the Function to leave the loop early and display the result.
**Has_Formula**
Will return TRUE or FALSE depending on whether the cell referenced houses a formula or not. Used like: =Has_Formula(A1)Great one to use with Conditional Formatting so formula cells are color coded.
*CODE*
Function Has_Formula(Check_Cell As Range) as Boolean
Has_Formula = Check_Cell.HasFormula
End Function
EXPLANATION
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.
**Last_Cell_value**
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).
Note: Cannot be used on entire Rows or Columns.
*CODE*
Function Last_Cell_value(Col_or_Row_Range As Range)
'
' 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
End Function
EXPLANATION
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 Ctrl+ Up Arrow.
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.
Until next month, keep Excelling!
ADVERTISEMENTS
Artificial neural network software for stock markets!
MAIN SOFTWARE CATEGORIES