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
The OzGrid Add-ins Collection Special! | | ExcelEveryWhere HTML
Hi all,
Another busy month here at OzGrid. Dave has developed a new logo, which he has now put on the web-site. We hope that it more effectively shows clients what the focus of our business is. Let us know what you think of it, we would be interested in your comments. In line with this, we are making some changes to our web-site in the hope that it will be more user-friendly for visitors, and hopefully freshen up the appearance, look and feel of the business.
Dave has been doing a quite a bit of consultancy work this month, which he enjoys for a change. Usually our consultancy work is done by one of the developers that we use, but he likes to complete the odd job here and there to keep his hand in with this type of work when he can. Lecturing has also been in high demand for the both of us this month, mainly we think because we are drawing near to the end of the Australian Financial Year, which is 30 June here.
We hope you enjoy the rest of the newsletter - until next month, keep healthy and happy.....
Super Special! Buy theAnalysis Collection and get the entireOzGrid Add-ins Collection (now even bigger) for Free! Just send your purchase confirmation to[email protected]
This months half price special is (same as last month) for our new Manager Add-ins. You can pick from any of these:
Remember the special only lasts 10 days, from the 12th June 2003 and will end on the 22nd June 2003. To take up this 10 day offer, send an email to[email protected] before 22nd June 2003 and we will send you an invoice, then the Add-ins upon receipt of payment. You can pay online via our secure site, or via the PayPal secure site. Our PayPal email account is [email protected], be sure to include the exact name of the product.
FREE EXCEL QUESTION & ANSWER FORUM! This month we will be discussing Scenarios. Scenarios are part of the "What If" Analysis Toolpak available in Excel. Using what is called the Scenario Manager to create your scenarios, you can use scenarios to test data in a worksheet where values are applied to a worksheet to see what happens to the data. These values are not actually stored in cells, they are stored in a hidden part of your workbook and are then retrieved through the Scenario Manager. The Scenario Manager will step us through the process of creating and modifying our Scenarios. We are going to look at the basics of Scenarios in this issue.
Before you can set up different scenarios, you first need to create a Base Model, or default scenario.
Open the attached workbook, where we have created a very basic spreadsheet to calculate loan repayments on a housing loan. We are going to test a couple of different scenarios that vary the interest rate to see what type of results we come up with. The workbook as you see it now will be our base model from which we will create another two scenarios.
To create our base model the first thing we will do is click in cell B9. This is the cell that contains the interest rate and therefore the cell that we will want to replace with different values to see what happens to our final calculations. Once clicked in B9, go to Tools>Scenarios. This will result in the Scenario Manager dialog box being displayed on your screen. Note that the text in front of you says No Scenarios defined. Choose Add to add scenarios. Click theAdd button. The dialog box called Add Scenario will be in front of you and you will see your mouse insertion point waiting for you to type under the Heading Scenario Name. Type in Base Model. Notice that under the heading Changing Cells: B9 is already there. This is because you were clicked in this cell to start with. Now click theOK button at the bottom right hand side of the dialog box. The next part of the Scenario Manager, the Scenario Values dialog box should now be in front of you, and you should see 0.065 (6.5%) highlighted. Because this scenario is to be used as our base, or original model, we will make no changes here other than to select the Add button.
Our Base Model has now been added to our list of scenarios and we are taken back to the Add Scenario dialog box, where we will add another Scenario. Let's call this Scenario Best Case, so type this in under Scenario Name. Again notice that B9 still appears under the Changing cells: heading. Click the OK button and you will be taken to the Scenarios Values dialog box. This time we will type in 0.05 (5%). Now click the Add button. One more Scenario to Add. Let's call this one Worst Case, so type this in under Scenario Name. Click the OK button and type in 0.080. This time click the OK button (not Add to add another scenario) to accept your final scenario and close down the dialog box.
Notice that this time you have been taken back to the Scenario Manager where you should be able to see in front of you Base Model, Best Case, Worst Case. Now to test our Scenarios to see what sort of results we get. This is really easy to do. Click on Best Case then click Show. See the results of your calculations based on an interest rate of 5%. Now click on Worst Case then Show to see the results of your calculations based on an interest rate of 8%.
Pretty cool hey. Two more things to show you regarding Scenarios. You can create a Summary of the Scenarios you have created and place this on a new Worksheet, or into a Pivot Table. Click on the Summary button. This will bring up the Scenario Summary dialog box. The default here is Scenario Summary, although notice you do have the option of creating a Scenario Pivot Table Report. We will stick to the default in this exercise. Under Result Cells we will click the collapse button (red) to the right of this area to collapse the Scenario Summary dialog box up. Now highlight cells B17, B18, B19. Now hit the collapse button again, then select OK. You should now be able to see a summary of your scenarios on a new, separate worksheet. Click back on to the Base Model worksheet and go to Tools>Scenarios. If you wanted to delete a Scenario, all you need to do is to click on the Scenario that you wish to delete to highlight it and click on the Delete button.
Scenarios are an extremely powerful feature of Excel, and we have looked at them in their simplest form here. You can set them up so that the values change multiple cells in a scenario, you can name these values with real names and you can even import Scenarios from different workbooks. Scenarios are discussed fully in our Level 3 Excel training course, this is just a taste of how powerful they are.
FREE EXCEL QUESTION & ANSWER FORUM! This month we will carry on from where we left off last month on Excel Workbook and Worksheet Events. If you missed last 2 issues, or were not subscribed, you can read them here:Issue 24 - Apr 2003 andIssue 25: May 2003
Ok, let's look at some of the Events we can trap using the Private Module of the Sheet Object. As with the Workbook Events, there is a quick way to get here. Right click on the sheet name tab, select View Code. Then, if using Excel 97 you will have the default Event Worksheet_SelectionChange, if you are using Excel 2000+ you will need to select Worksheet from the top left drop down box. It is within this Private module that ALL Worksheet Event code must reside.
TheWorksheet_SelectionChange Event is fired every time a user selects any cell on that Worksheet. It matters not if they use the Mouse or Keyboard. One popular use for this Event is to prevent users from selecting specified cells, without having to protect the sheet. This would of course mean we need to know which cell they have selected, this is where the argument ByVal Target As Range come into play. The code below will simply display a message box informing the user if they attempt to select the cell A1
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
MsgBox "Please do not select this cell.", vbInformation
Range("A2").Select
End If
End Sub
As you can see we have used the Address Property of the Target Range Object to determine which cell they have selected. The Target Variable has been pre dimensioned for us inside the parenthesis to the right of the Procedure name.
Now, while this does go some of the way to prevent the user selecting the cell and perhaps deleting it, it will not work if the user selects a range of cells, with A1 being one of them. To do this we need to find out whether their selection includes the cell A1. One way (the wrong way) is to use a For Each loop and loop for all cells in the selection and then use an If Statement to determine if one of the cells is A1. The correct way is to use the IntersectMethod.
FROM THE EXCEL HELP
Returns a range object that represents the rectangular intersection of two or more ranges.
expression.Intersect(Arg1, Arg2, ...)
expression Optional. An expression that returns an Application object.
Arg1, Arg2, ... Required Range. The intersecting ranges. At least two Range objects must be specified
END OF EXCEL HELP
Armed with this knowledge we can now modify our code to look like:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Selection, Range("A1")) Is Nothing Then
MsgBox "Please do not select cell A1.", vbInformation
Range("A2").Select
End If
End Sub
Notice that we use the Nothing Keyword to determine if A1 Intersects with the users selection.
FROM THE EXCEL HELP
TheNothing Keyword is used to disassociate an object variable from an actual object. Use the Set statement to assign Nothing to an object variable. For example:
Set MyObject = NothingSeveral object variables can refer to the same actual object. When Nothing is assigned to an object variable, that variable no longer refers to an actual object. When several object variables refer to the same object, memory and system resources associated with the object to which the variables refer are released only after all of them have been set to Nothing, either explicitly using Set, or implicitly after the last object variable set to Nothing goes out scope.
END OF EXCEL HELP
Now let's look at some of my favourites for using the Worksheet_Change Event. This Event is fired when cells in any worksheet are changed by the user or by an external link.
Getting past Conditional Formattings 3 Criteria Limit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:A10")) is Nothing Then
Select Case Target
Case 1 To 5
icolor = 6
Case 6 To 10
icolor = 12
Case 11 To 15
icolor = 7
Case 16 To 20
icolor = 53
Case 21 To 25
icolor = 15
Case 26 To 30
icolor = 42
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
See this page for a VERY handy little function for getting the ColorIndex Property of any cell:
Have a Cell Ticked Upon Selection
This code is an alternative to Checkboxes and can save a lot of space and is much easier to count the ticks! Just use the COUNTIF Function. This code works on only range A1:A10, but can be modified to suit. It could also be used in theBefore Double Click event.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"
Else
Target = vbNullString
End If
End If
End Sub
Ok all, I'll have to leave it here for this month, so until so until next month keep excelling!