Free OzGrid Excel Newsletter Issue 4
If you would like to subscribe to our Newsletter - CLICK HERE
in this issue:
How to use the INDIRECT function to read a cell address.
How to use Edit Links and use Find across multiple Worksheets.
How to use Choose in VBA with an array.
Hi All
The most important issue this month for OzGrid is the change of our Domain name from ozgrid.com to microsoftexceltraining.com. The reason for the change of Domain name is to try and boost our position in search engine listings (once we start submitting). We still own the Domain ozgrid.com and are in the process of having it automatically forward people to microsoftexceltraining.com. This should be a very simple process, but for reasons beyond our control it is proving to be real headache! To ensure that you reach the new Web-site please use
http://www.microsoftexceltraining.com. It is quite possible that if you reach our site via a search engine or your Favourites you will be viewing our old site. We will keep all of you informed on any updates.
I have added some new content to the Web-site. The first of these is the creation of a VBA Index. I have also added various snippets of new code to the pages. The second is a page called "About OzGrid and Us" the link to this page can only be found on the "Home" page ie;
http://www.microsoftexceltraining.com. This page states the reason for the Web-site, about OzGrid Business Applications as a business and some information about my family and myself. I have also started a question and answer board specifically for Excel users. This page can only be accessed via our "Home" page, "About OzGrid and Us" page and the "SEARCH" page. The intent of this page is to allow other Excel users to ask and answer questions. To date there has only been one question, which I have answered. I will be answering questions on this board initially, but will opt out at a later stage.
The other big news for anybody interested in Excel or VBA for Excel training is the creation of our "Private Web-site Training". This type of training is ideal for small to large groups who all have access to the Internet. What will happen will be that a password will be provided to the group that they then use to access the "Private Web-site". Once there, they will be able to read, download and/or print their training. The lessons for the chosen training will be updated at a nominated interval. This method of training will be at a considerable discount to other types of group training. For details and cost please email myself at:
ozgrid@microsoftexceltraining.com
I have been receiving a huge amount of questions from excel users lately, I do try to reply to them all but unfortunately it is not humanly possible to reply to each and every one. If you have sent in a question and I have not answered you, I apologise. What I will suggest is that if you are an OzGrid newsletter subscriber, send your question to:
ozgrid@microsoftexceltraining.com and I will give it first preference. Please don't read this as I will answer it, I'm just saying your chances will be better. Your chances will also be much greater if the question is short and to the point. I receive many questions that are very long and involved and quite frankly I do not have the time to provide free answers to these questions. As I have stated in the past I must put my paying clients first.
One last thing before we get to the reason you all subscribed! We will be slowly changing our email adddress from
drhawley@geo.net.au to
ozgrid@microsoftexceltraining.com This will be coming about very slowly though as most of our work is the result of past clients passing on our email address
Ok, let's move on to the good stuff - Excel! This month I have received two requests from Excel users on how they can use the text stored in a Worksheet cell as a reference in an Excel formula. The simple answer to this is to use the INDIRECT function, this will let Excel know that the text in a cell should be seen as a cell address and not just text. Let's say you need to sum a range of cells based on a range that has been entered into cell A1.
In cell A1 put B1:B10
In the cells B1:B10 put any numbers
In any cell put this formula: =SUM(INDIRECT(A1))
As you will see, Excel will sum the range of cells in B1:B10. Change the text in A1 to B1:B5 and the SUM formula will change to reflect the new range. If your cells to sum are on another Worksheet just change B1:B10 to Sheet2!B1:B10 or whatever you Sheet name is.
There are two important issues that you should be aware of with the INDIRECT function.
It cannot be used to reference another Excel Workbook that is closed
Any cell reference contained within it will always be seen as Absolute. This means no matter where we copy the formula =SUM(INDIRECT(A1)) it will always be referencing cell A1
As we have mentioned referencing closed Workbooks lets' look at the simple way to change all external links within a Workbook.
Go to Edit>Links (if it is greyed out you don't have any external links)
Click the link you wish to change.
Click the "Change Source" button and locate the new Workbook to reference and Ok
Then click Ok again.
Excel will now change all formulas to reference the chosen Workbook.
Should you wish to only change some of the formulas (not all!)
Select a cell that contains the external formula
Highlight the File path and sheet name in the Formula bar, eg; 'L:\Daves\[Book2.xls]Sheet1'!
Push Ctrl+C then push Enter.
Select the range of cells you wish to change the reference in and go to Edit>Replace.
Select in the Find what box and hold down the Backspace to ensure you do not have a space.
Push Ctrl+V.
Do the same in the Replace with box and then edit the file path to the new one needed.
Click Replace All
Excel will now replace the reference only in the selected range. The use of Copy and Paste ensures we don't' have any typos!
Another point on the Find and Replace function in Excel is that it can be used on all Worksheets (or nominated ones). The reason I say this is because of the number of times I have seen VBA code written to use the Find and Replace across numerous Worksheets. Try this instead.
Select your first sheet you wish to use your Find and Replace on. Then holding down the Ctrl key, select the others. This will group the sheets.
Push Ctrl+H (or go to Edit>Replace) and type in your text to Find. Then you text to Replace it with.
Push Replace all. Then select any sheet to ungroup your Worksheets.
Hardly worth all the bother of writing VBA code for is it?
Let's now look at some VBA code for all the coders out there! You may or may not be aware that you can use the Choose function in your VBA code. This can be very helpful when you need to store an array of data within the VBE. After last weeks bagging of Loops I thought I would show you how Loops can be used correctly. Sub UsingChoose()
Dim i As Integer
Dim sMytext As String
For i = 1 To 8
sMytext = Choose(i, "dog", "cat", "rat", "mouse", "pig", "sheep", "bird", "horse")
Cells(i, 1) = sMytext
Next i
End Sub
This code will Loop 8 times and each time parse a different text String to our variable sMytext. It will then place the text being stored in our variable to a cell in Column A of the Active Worksheet. Once you run the code you will see immediately how it works. As with any VBA code the limits that a method like this is bound to is mainly restricted to your own ability to Step outside the box! Let's now suppose you need to refer to this array of values a lot throughout your entire VBA project. You obviously do not want to be typing this list over and over, or even copying and pasting it. When this is the case we can parse our list of values to a Variant via the use of the Array function.
'Make available to ALL modules.
Public sMytext As Variant
Sub UsingChoose()
Dim i As Integer
'Parse the values to our Variant with the Array function.
sMytext = Array("dog", "cat", "rat", "mouse", "pig", "sheep", "bird", "horse")
'Loop through our array of values. Start at zero as this is the _
default for the first value in an array. We add one to the Integer "i" _
as we need to start at 1 and finish at 8
For i = 0 To 7
Cells(i + 1, 1) = sMytext(i)
Next i
End Sub
The benefit here is that our variant "sMytext" will store the array of values parsed to it until the Workbook is closed. It will also be available to any Module within the Workbook. I hope you all get something useful out of this month's newsletter. Until next month... Until next month, keep Excelling!
Dave Hawley
DavidHawley@ozgrid.com
Cảm ơn bạn, mình đang tham khảo trên link: 
