OzGrid Excel Add-in Collection. All the OzGrid Add-ins bundled together SPECIAL!
OzGrid's Excel Newsletter
You are more than welcome to pass on this newsletter to as many people as you wish.
To no longer receive our newsletter, send an email with the words "action: Unsubscribe" in the body of the email, or click here.
Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation
Read this issue and past issues online here:
OzGrid Excel Add-in Collection. All the OzGrid Add-ins bundled together SPECIAL!What's new - Half Price Special - Excel Section - Excel VBA Section - Helpful Info
Hi all
February was yet another busy month for OzGrid, with our new Excel Question and Answer Forum a huge success, better than even we expected. If you haven't already, check out theHey That Is Cool section. We have more than doubled membership of the Forum since we published our last newsletter, and we can only hope that this will continue to increase. If you haven't been there already, do so and check the Q/A board, or just shoot the breeze if you prefer. Registration costsnothing and only requires a Username, Password and email address. You will then have free 24/7 support!
Software Sales
Software sales, ours and our affiliates are on the increase. As are the number of products we have on offer. Because of this we are always looking for software applications that would be of interest to the visitors of our website. If you have a software product that you are interested in selling via our web-site, please contact us, so we can talk about it. We already have one of the largestExcel Add-in Collections on the planet!
Significant Discounts
Due to the sheer number of enquiries that we have had, we are also changing the pricing structure of our own OzGrid Add-Ins. Discounts will be offered for more than one license of a particular product. The discounts will be:
2 - 4 Licenses - 20% discount
5 - 9 Licenses - 30% discount
10+ Licenses - 50% discount
Gloom and Doom
We intend to take a 4 day family break at the beginning of April, before autumn sets in and to celebrate Dave's 40th birthday. Because of this Dave will be focusing on finishing off a couple of programming jobs in our local area this month, so he has a clean slate and a clear conscience before our trip!!!
So - enjoy the month of March - and keep Excelling!!!!!
27 Excel Add-ins, 3 Downloadable Books on VBA, a 75% Saving The Analysis Add-ins Collection
Super Special! Buy theAnalysis Collection and get the entireOzGrid Add-ins Collection for Free! Just send your purchase confirmation to[email protected]Offer ends 10 April 2003
This months half price special is for our 2 most popular Excel Add-ins. The OzGrid Excel Plus and the Duplication Manager. We have bundled these together for a saving already, but will now reduce this already special price by a further 50%. You can see both these add-ins and their featuresHere. The normal bundled price is $39.95 which is already over a 30% discount! As newsletter subscribers you can have this bundle for $19.98 - that is under $10 per add-in!
Remember the special only lasts 10 days, from the 10th March 2003 and will end of the 20th March 2003. To take up this 10 day offer, send an email to[email protected] before March 20th 2003 and we will send you an invoice and then the Add-ins upon receipt of payment. You can pay online via our secure site, or via the PayPal secure site.
It should also be noted that we have just added a new feature to the Duplication Manager that removes duplicates from 1 list that occur in another. This has been added by popular demand.
Super Special! Buy theAnalysis Collection and get the entireOzGrid Add-ins Collection for Free! Just send your purchase confirmation to[email protected] Offer ends 10 April 2003
This month I thought we would look at some of Excel's Logical Functions. These consist of
Without a doubt, the most commonly used Logical function would be the IF Function. The IF Function, like all the logical functions, will return TRUE or FALSE. Let's look at a simple use of the IF function. In this example we will use it to determine whether the cell A1 is greater than 100. If it is, then we will multiply that number by 10, otherwise if it's not greater than 100 we will divide 10 by it.
IF(A1>100,A1*10,10/A1)
As you can see, the Syntax for the IF function is:
=if(logical_test,value_if_true,value_if_false)
Note the Argument value_if_false is not bolded. This is because we do not have to supply the False argument. If we omit it, our formula would return the BooleanFALSE if it evaluates to False.
Let us now suppose we need to check if cell A1 is between the value 100 and 200. For this we need to Nest the AND function into our logical_test for the IF function.
=IF(AND(A1>100,A1<200),A1*10,10/A1)
The AND has a Syntax of:
=AND(logical1,logical2,...) up to 30 logical
It will return True ONLY if all supplied logical arguments evaluate to True. If only one from many, evaluate to False and the rest are True, the AND Function will return False. As you can see we have told the AND function to check whether cell A1 is between 100 and 200 by telling it to evaluate the 2 logical arguments we have supplied. The result (TRUE or FALSE) is returned to the logical_test argument of the IF Function.
Let's now suppose we want to check the contents of 2 other cells and if either one is greater than 100 multiply A1 by 10.
=IF(OR(A2>100,A3>100),A1*10,10/A1)
The OR has a Syntax of
=OR(logical1,logical2,...) up to 30 logical
Very much the same as the AND function. The one and only difference is, only one of all supplied logical arguments needs to be true for the OR Function to return TRUE. In our example this means that if either A2 or A3 is greater than 100, the result TRUE is returned to the logical_test argument of the IF Function.
So far we have been making one VERY big assumption and that is, Cell A1 will house a number. Try typing some text in cell A1 and all our Function will return the #VALUE! error. This is simply because we cannot multiply or divide with text. To account for these types of situations we need to either first check A1 houses a number.
=IF(ISNUMBER(A1),IF(OR(A2>100,A3>100),A1*10,10/A1),"")
The above is one way to do this and if cell A1 does not house a number then return "" (empty text). There is still a potential problem when A1 is equal to zero. We end up with the #DIV/0! error. Meaning we are trying to divide by zero and that's not possible. To account for this also, we could use
=IF(ISNUMBER(A1),IF(OR(A2>100,A3>100),A1*10,IF(A1>0,10/A1,"")),"")
This will now prevent trying to divide 10 by zero. If we wanted we could take, what I call the blanket approach, by using one of Excel's Information Functions. In this case the ISERROR or ISERR Functions. These have a Syntax of.
=ISERROR(value)
and
=ISERR(value)
They are both very similar but while the ISERROR checks for all errors (and returns TRUE or FALSE), the ISERR checks for all errors except the #N/A! and again returns True or False. Meaning we could use:
=IF(ISNUMBER(A1),IF(OR(A2>100,A3>100),A1*10,IF(ISERR(10/A1),"",A1/10)),"")
This will prevent any errors in regards to trying to divide by zero. The problem with this approach is you could well be masking an error that you perhaps should be aware of! One way to overcome this potential problem is to use the ERROR.TYPE Function. It has the Syntax
=ERROR.TYPE(error_val)
and again, will evaluate to either True or False. The error_val's are:
#NULL! = 1
#DIV/0! = 2
#VALUE! = 3
#REF! = 4
#NAME? = 5
#NUM! = 6
#N/A = 7
Anything else#N/A
Meaning, we could use:
=IF(ISNUMBER(A1),IF(OR(A2>100,A3>100),A1*10,IF(ERROR.TYPE(10/A1)=2,"",A1/10)),"")
This will only prevent the #DIV/0! error should A1 be zero.
Error checking/trapping in Worksheet functions can get quite complex very quickly. Even with our above formula we could still encounter an error within the OR Function. If this happens our OR function will return neither TRUE or FALSE, it will return an error and that will force our formula to again return an error. So as you can see, a simple formula can soon turn into a monster if we are to try and account for all possible errors. Truth is, it is not feasible to always to try and trap all errors. The best way to prevent errors is to prevent them at the source. In this case, the source is cells A1, A2 and A3 and the easiest way to ensure no invalid data goes into to these cells is by using theValidation feature found under Data on the main menu.
If Excel doesn't have the Functions you need, you have to seeExcel Business Functions. These are the real thing (written in C++) and not simply User Defined Functions.
To start this months VBA section I thought I would include a handy piece of code that can be used to set the print area on the sheet, then insert horizontal page breaks at specified rows of the Print area.
Sub InsertPageBreaks()
Dim rRange As Range
Dim rFind As Range
Dim strFind As String
Dim i As Integer
On Error Resume Next
Set rRange = Application.InputBox _
(Prompt:="Select range to print.", Title:="PRINT RANGE", Type:=8)
On Error GoTo 0
If rRange Is Nothing Then Exit Sub
ActiveSheet.PageSetup.PrintArea = rRange.Address
strFind = InputBox("Character identifier for page breaks. " _
& "Must be in Column 1 of Print Range! Default is a " _
& "single Full Stop. Character WILL be removed." _
, "PAGE BREAKS", ".")
If strFind = vbNullString Then Exit Sub
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks
Set rFind = rRange.Cells(1, 1)
For i = 1 To WorksheetFunction.CountIf(rRange, strFind)
Set rFind = rRange.Columns(1).Find(What:=strFind, After:=rFind, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
rFind.Clear
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=rFind(2, 1)
Next i
End Sub
If you do a lot of printing where you wish to specify your horizontal page breaks this bit of code can save you hours.
When using VBA in a spreadsheet it is very wise to name cells you will be working with. This is so when/if a user moves the cell or adds/deletes rows/columns our code is not left referring the wrong range. It also means we no longer need to precede the range with the sheet name. The exception to this rule is when we refer to a named range from within a Private Sub of another sheet. To name a cell or range of cell via VBA is very simply
Sheet1.Range("A1:A10").Name="MyRange"
From this point on we can refer to the range like
Range("MyRange")
Unless, as I have said, this resides in the Private Module of the sheet object other than Sheet1. If we omit the Sheet reference using this code in another Sheet Objects Private module we will encounter an error. Personally I tend to use the sheets CodeName regardless. This then lets me know at a glance which sheet "MyRange" resides on.
Names in Excel VBA are a Collection of all Name Objects. Each name has both a Text name and an Index number. The Index number is often of no use to us, but the text name is. Remember last month we had some code that looped through all the Name Objects in the ActiveWorkbook. SeeIssue 22
Sometimes in Excel we need to determine whether a particular cell is part of a named range. For example how could we tell if Sheet1.Range("A1") is part of the named range "MyRange"? To achieve this we use the Intersect Method. The Intersect Method returns a range object that represents the rectangular intersection of two or more ranges. The all important bit here is the returns a range object it is this we can check to determine if one range is part of another. The default non existent range Object is the Nothing Keyword. Meaning, if the Intersect does NOT return a Range Object it returns Nothing.
Sub IsPartOfNamedRange()
If Intersect(Sheet1.Range("MyRange"), _
Sheet1.Range("A1")) Is Nothing Then
MsgBox "Sheet1 Range 'A1' is not part of the " _
& "named range 'Myrange'"
End If
End Sub
This simple bit of code will show our message box if Sheet1 range A1 is part of the named range "MyRange".
This is fine if we only need to check if it is part of one named range and we know the name of that range. But what would happen if we had no idea what the name of the ranges are in the Workbook and we needed to check if A1 is part of any named range? In this case we would need to loop through all Name Objects in the Names Collection and use the Intersect method on each.
Sub IsPartOfAnyNamedRange()
Dim nCell As Name
For Each nCell In Names
If Not Intersect(Sheet1.Range(nCell), _
Sheet1.Range("A1")) Is Nothing Then
MsgBox "Sheet1 Range 'A1' is part of the " _
& "named range " & nCell.Name
End If
Next nCell
End Sub
This code will tell us the name of the range that sheet1 range A1 is part of, if any.
Possibly the most frequently used reason for checking if one range is part of another range, or a named range is via the Worksheet_Change Event. This Event resides in the Private Module of the Sheet Object. The fastest way to get there is to, right click on the sheet name tab an select View Code. Once in here we can have our code fire when/if Change occurs within a specified range. The cell which fires the Change Event is known as the Target and Target is a Range Object.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.Range("MyRange"), _
Sheet1.Range("A1")) Is Nothing Then
Application.EnableEvents = False
MsgBox "Get outta here!"
Application.Undo
Application.EnableEvents = False
End If
End Sub
This bit of code will fire ONLY if the cell that changes is part of the named range "MyRange". It then displays the message box and then cancels the users change and reverts the cell back to it's original content. This is done via the use of Application.Undo and it is VERY important to realize that the Application.Undo would cause our Event to fire yet again, which in turn would show our message box, then go to Application.Undo and so on...... Meaning you will end up with your code continually looping. We have taken care of this by using Application.EnableEvents=False. It is VERY important to ensure the Events are turned back on, or else no Event code will fire in ANY open Workbook.
If you would like to undertake some training from professional Excel developers and have full question support,see here. If you are on a tighter budget and only want question support from ourExcel Help Forum see ourDownload Training page.
You are more than welcome to pass on this newsletter to as many people as you wish.
To no longer receive our newsletter, send an email with the words "action: Unsubscribe" in the body of the email, or click here.
Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation
Read this issue and past issues online here: