OzGrid Excel Add-in Collection. All the OzGrid Add-ins bundled together SPECIAL! Now even more value with $59.00 worth of extra Excel Add-ins for only $15.00 more!
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! Now even more value with $59.00 worth of extra Excel Add-ins for only $15.00 more!What's new - Half Price Special - Excel Section - Excel VBA Section - Helpful Info
The OzGrid Add-ins Collection Special! | | 
ExcelEveryWhere HTML
Hi all,
Now that the war is over, things have started to get really busy here once again. Dave developed three new products at the end of last month, when he had a breather. Truthfully I must tell you that the last two were developed at my request as being a Word girl I was getting frustrated at the lack of some handy features that Word has that are not contained in Excel. Dave obviously got sick of me moaning about it and decided to shut me up once and for all, which he has done. Luckily he finished them before the end of the war as he would not have had time to work on them now.
We have also added a very new and popular product for Excel. It's calledExcelEveryWhere and it's for those that wish to Excel on the WWW or an Intranet. Share your spreadsheet ONLINE with your customers, partners, colleagues, clients and consumers
Since releasing them, demand has been strong for these products and we have had lots of positive feedback. They are also this months half price special These three products are:
Designed to make the managing of all formulas a breeze. Can be used as a tool to:
Specify formula types to Lock, Hide or Lock and Hide and even Protect in the same step.
Also included in the Formula Manager is the Formula Report Generator whichallows you to very easily create a report showing formulas within your Workbook that are either external references, internal referencesor both. This also has other useful features
And finally we have thrown in a Formula Copy. This allows you to easily copy any formula range and paste it to a destination without any references changing. The option is the Transposing of formulas (row to columns and columns to rows) again,without any references changing.
Designed to make working with text in your spreadsheet very easy. Particularly useful for text which has been imported into Excel. Some features include:
For the remainder of the month, we are going to try and get our VBA for Excel UserForms course on-line. This is the only one of our courses that is not yet available on-line, but due to demand, it is a job that has to be done now.
We hope you enjoy the rest of the newsletter - until next month, keep healthy and happy.....
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 (now even bigger) for Free! Just send your purchase confirmation to[email protected]
This months half price special is for our new Manager Add-ins. You can pick from any of these:
Remember the special only lasts 10 days, from the 12th May 2003 and will end on the 22nd May 2003. To take up this 10 day offer, send an email to[email protected] before 22nd May 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. Our PayPal email account is [email protected], be sure to include the exact name of the product.
Super Special! Buy theAnalysis Collection and get the entireOzGrid Add-ins Collection for Free! Just send your purchase confirmation to[email protected] Offer ends soon
FREE EXCEL QUESTION & ANSWER FORUM!
This month I thought we would discuss the use of Data Tables, which are part of the "What if" tools that Excel provides. Data Tables are used to store multiple scenarios so that you can analyse data to see various options. There are two types of Data Tables, One-variable Data Tables and Two-variable Data Tables.
One-Variable Data Tables
One-Variable Data Tables are used if a user wishes to change the value of only one variable to gauge the effect on one or more formulas. With any Data Table a base model is required. Data Tables feed off of a base model and display their results in tables in your workbook in a location specified by you. So you have to tell your table which formulas in the base model that you want to test by placing a formula of reference in your table.
Let's say that we want to test certain scenarios on our housing loan. The first thing that you would need to do would be to set up your base model. This is the model that the Data Table will be based on.
On a new worksheet in cell A2 type in House Price in cell B2 type in $100,000. InA3 type Amount of Loan in B3 type in $70,000. In A4 type Interest Rate in B4 type in 6%. In A5 type in Term of Loan (Yrs) in B5 type in 25. In A6 type in No payments per year in B6 type in 12. In A7 type in Loan Repayment Amount in B7 type in =PMT(B4/B6,B5*B6,B3)*-1. In cell A9 type in Total Repayments in B9 type in =B5*B6*B7. In A10 type in Interest Paid in B10 type in =B9-B3. This completes our Base model.
To set up our Data Table, we need to first set up the area around our table so it is obvious what it is that we are analysing. In cell F2 type in Repayment Amount in cell G2 type in Total Loan Repayments in H2 type in Amount of Interest. Make sure that you bold the headings. In cell E4 type in4% in E5 type in 5% in E6 type in 6% in E7 type in 7% in E8 type in 8% in E9 type in 9% in E10 type in 10%. In F3 type in =B7, in G3 type in =B9, in H3 type in =B10. Make sure that all your percentages are also bold.
Now that we have set up an area for our table calculations (which are actually inserted as array formulas) we need to highlight our table area, so highlight E3:H10. Now select Data>Table It is in this dialog box that we are asked to nominate a cell from the original calculation into which the values in column F should be inserted. So click in the Column Input cell box and click on cell B4. Notice that B4 is inserted as an absolute cell reference. Now click onOK and you should be able to see the results of the calculations that would normally appear in cells B7, B9 and B10 of your base model.
Now as our table calculations are dependent on our base model, this means that every time one of the values that our Data Table is dependent on changes, our Data Table will update accordingly. Click into cell B3 and change the amount to $50,000. Notice now how all the values in the table change to take this into account.
Two-Variable Data Tables
With a two-variable Data Table, you can nominate two series of data that can be placed back into two different input cells in your original model. With a Two-Variable Data Table, one series is entered into the first column of the Table, while the second series is entered into the first row of the table. The formula that you wish to reference is placed into the empty cell at the top of the first column of the table.
In our scenario, let's see what type of results we get if we vary the term of the loan and the interest rate. Try this. Delete the entries you have in cells E3:G3. In cell E3 type in =B7, in F3 type in 20, in G3 type in 25, in H3 type in 30. Now select your table E3:H10 and go to Data>Table to bring up the Data Table dialog box. Your row input cell needs to be $B$5 and your column input cell needs to be $B$4. Click OK and see the results of your Two-Variable Data Table.
Just a couple of things to bear in mind when using Data Tables:
Next month we will have a look at another of Excel's features from the "What If" Tools - Scenarios.
Our 4 Most Popular Bundled Savings!
Excel Business Functions | Stock Quote | Financial Calculators | Business Software
Intelligent Converters | Construction Software | Manufacturing Software | Id's & Barcodes
FREE EXCEL QUESTION & ANSWER FORUM!
Checkout this great article by MG Moreira on Cancelling Long Operations
This month we will carry on from where we left off last month on Excel Workbook and Worksheet Events. If you missed last months issue, or were not subscribed, you can read it here:Issue 24 - Apr 2003
We will look at the Workbook_Activate and Deactivate Events. The Activate Event is the second Event to fire when opening a Workbook, Workbook_Open is first, and the Deactivate Event is the last Event to fire when closing. While the Open Event can only fire once, the Activate Event can fire may times. each time you go to Window>another workbook.xls and then come back, the Activate Event fires. The Deactivate Event will fire when you go to Window>another workbook.xls
These 2 Events are very handy when you wish to have a custom toolbar, or menu items available to a specific Workbook. You use the Workbook_Activate to build it and the Workbook_Deactivate Event to destroy it. As the code for custom toolbars and menu items can get rather lengthy, it is best to place the code inside a standard module and use the Run Statement to run the needed macros. So, right click on the excel icon, top left next to File and go to View Code, now paste in this:
Option Explicit
Private Sub Workbook_Activate()
Run "AddMenus"
End Sub
Private Sub Workbook_Deactivate()
Run "DeleteMenu"
End Sub
Now insert a standard module, via Insert>Module and paste in this code:
Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl
'(1)Delete any existing one. We must use On Error Resume next _
in case it does not exist.
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
On Error GoTo 0
'(2)Set a CommandBar variable to Worksheet menu bar
Set cbMainMenuBar = _
Application.CommandBars("Worksheet Menu Bar")
'(3)Return the Index number of the Help menu. We can then use _
this to place a custom menu before.
iHelpMenu = _
cbMainMenuBar.Controls("Help").Index
'(4)Add a Control to the "Worksheet Menu Bar" before Help.
'Set a CommandBarControl variable to it
Set cbcCutomMenu = _
cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _
Before:=iHelpMenu)
'(5)Give the control a caption
cbcCutomMenu.Caption = "&New Menu"
'(6)Working with our new Control, add a sub control and _
give it a Caption and tell it which macro to run (OnAction).
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Menu 1"
.OnAction = "MyMacro1"
End With
'(6a)Add another sub control give it a Caption _
and tell it which macro to run (OnAction)
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Menu 2"
.OnAction = "MyMacro2"
End With
'Repeat step "6a" for each menu item you want to add.
'Add another menu that will lead off to another menu
'Set a CommandBarControl variable to it
Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)
' Give the control a caption
cbcCutomMenu.Caption = "Ne&xt Menu"
'Add a control to the sub menu, just created above
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "&Charts"
.FaceId = 420
.OnAction = "MyMacro2"
End With
End Sub
Sub DeleteMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
On Error GoTo 0
End Sub
Sub MyMacro1()
MsgBox "I don't do much yet, do I?", vbInformation, "Ozgrid.com"
End Sub
Sub MyMacro2()
MsgBox "I don't do much yet either, do I?", vbInformation, "Ozgrid.com"
End Sub
By using code like this, it is not possible for the user to use the new custom menu items while in another Workbook.
If you were using code like this to build a Custom toolbar with lots of menu items etc, you can avoid the constant deleting and rebuilding of the Custom toolbar by using some code like below:
Sub AddMenus()
On Error Resume Next
Application.CommandBars("Custom Toolbar").Enabled = True
On Error GoTo 0
End Sub
Sub DeleteMenu()
On Error Resume Next
Application.CommandBars("Custom Toolbar").Enabled = False
On Error GoTo 0
End Sub
The code assumes you have created your custom toolbar via the Workbook_Open Event, or you have created a custom toolbar with menu items manually and attached it to the Workbook via View>Toolbars>Customize-Toolbars-Attach, or double click any unpopulated grey area on any toolbar. If you have created it manually and attached it, you should also delete (un-attach) the toolbar each time you close the Workbook. This would be done via the Workbook_BeforeClose Event like below:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Custom Toolbar").Delete
End Sub
As long as your toolbar is attached to the Workbook, it will automatically show again when you open the Workbook. Don't use this method if the toolbar is not attached.
Next month we will look into the very popular Worksheet Events. Until then, keep Excelling!
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: