ExcelTips
INDEX
LINKS
Add an Index Sheet
Returning the Left-most Characters
Selecting Cells of a Specific Color
Conditionally Displaying a Message Box
Calculating Fractions of Years
Flipping Data flipping Rows flipping Columns
Removing Hyperlinks without a Macro
beleggingen vergelijken + TW is niet hetzelfde als =TW()
comparing Workbooks
Shortening Zip Codes
Grabbing the MRU List
HIGHLIGHTING CELLS CONTAINING SPECIFIC TEXT
Conditional formatting for errant phone numbers
Defeating Automatic Date Parsing
Calculating Fractions of Years
Pasting without updating references
Linking comment to multiple cells
Automatically Printing a Range
Macro Runs Slowly
Alerts About Approaching Due Dates
Cannot Double-Click to Open a Workbook
Using GEOMEAN With a large List
Counting Precedents an Dependents
Top resources for Office 2007
Converteer van 200
LINKS:
Voeg een werkblad toe aan het begin van je werkboek en geeft de naam Index.
Rechtsklikken op de tab en kies programmeercode weergeven.
Voeg er de onderstaande code aan toe
Druk [Alt][Q] en sla het werkboek op.
De volgende keer dat je het werkboek zal opeent
Dan zal de Index pagina de linken bevatten naar ieder wekblad in het werkboeek.
Iedre werkblad zal een link terug bevatten naar de Index pagina.
Deze staat voor dit voorbeeld in A2, maar kan een ander blanke cel voor alle werkbladen
Opmerking : daarna kan je de programmeercode van het Index blad verwijderen als je wilt.
===============================
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim M As Long
M = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With
For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
M = M + 1
With wSheet
.Range("H1").Name = "Start" & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("A2"), Address:="", SubAddress:="Index", TextToDisplay:="Back to Index"
End With
Me.Hyperlinks.Add Anchor:=Me.Cells(M, 1), Address:="", SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet
End Sub
For instance, the following formula returns the three left-most characters in cell A7:
=LEFT(A7,3)
If the value in A7 is not text, then LEFT still treats it as if it is. Thus, if A7 contains 12345, then the above formula returns the text value 123.
#################################################################
Is there a way to select cells containing a specific color. Accomplishing the task is easy if you are using Excel 2003. Just follow these steps:
Press Ctrl+F to display the Find tab of the Find and Replace dialog box.
Make sure there is nothing in the Find What box.
Click Format. (You may need to click Options to see the Format button.) Excel displays the Find Format dialog box.
Make sure the Patterns tab is displayed. From the colors available, choose the color you want to find.
Click OK to close the Find Format dialog box.
Click Find All. The Find and Replace dialog box expands to show the addresses of all the cells formatted with the color you specified in step 5. Click one of the cell addresses in the bottom of the dialog box. Excel selects the cell within the actual worksheet.
Press Ctrl+A. All of the addresses within the dialog box are selected.
Click Close. All the cells of the desired color are selected.
If you are not using Excel 2003, the only way to select cells of a particular color is to use a macro. Consider the macro shown here:
Sub SelectColoredCells() Dim rCell As Range Dim lColor As Long Dim rColored As Range 'Select the color by name (8 possible) 'vbBlack, vbBlue, vbGreen, vbCyan, 'vbRed, vbMagenta, vbYellow, vbWhite lColor = vbBlue 'If you prefer, you can use the RGB function 'to specify a color 'lColor = RGB(0, 0, 255) Set rColored = Nothing For Each rCell In Selection If rCell.Interior.Color = lColor Then If rColored Is Nothing Then Set rColored = rCell Else Set rColored = Union(rColored, rCell) End If End If Next If rColored Is Nothing Then MsgBox "No cells match the color" Else rColored.Select MsgBox "Selected cells match the color:" & _ vbCrLf & rColored.Address End If Set rCell = Nothing Set rColored = Nothing End Sub
To use the macro, select a range of cells before running it. The macro then steps through each selected cell and compares its color with whatever color you specify in lColor. If a match is found, then the cell is added to a selection set. When completed, the macro selects only those matching cells, and then exits.
If you would like to find out other macro-based solutions, you can refer to the following article at the Microsoft Knowledge Base:
http://support.microsoft.com/?kbid=142122
###############################################################################
Conditionally Displaying a Message Box
You may have a need to display a message box whenever specific information is placed in a specific cell by the user. Fortunately, using the Change event for a worksheet can help you to figure out when something has been placed in a cell.
For instance, let's say that you wanted to display a message whenever the information in cell C3 is changed. The following, added to the code window for a specific worksheet, will do the trick:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$3" Then MsgBox "Changed It!" End If End Sub
The Change event is called, and passes the cell range to the routine. In this case, the range is assigned to the Target variable. The address of this range is then checked, and if it is equal to C3, (has to be noted in absolute terms, such as $C$3), then the message box is displayed.
One of the types of data that Excel allows you to store is, of course, dates. At some point you may wish to perform some calculations with the dates in your worksheet. It is not uncommon to need to figure out the percentage of a year represented by the difference between two dates. Excel allows you to calculate this easily using the YEARFRAC worksheet function. This function is part of the Analysis ToolPak provided with Excel.
To use the function, all you need to do is provide two dates and a value that specifies how Excel should calculate the fractional year:
=YEARFRAC(DateOne, DateTwo, Basis)
The dates used by YEARFRAC can be either static dates, or they can be references to cells that contain dates. The Basis value ranges between 0 and 4, with 0 being the default. The following are the different meanings for the Basis:
You should note that if the YEARFREQ function does not work on your system, it means you have not installed or enabled the Analysis ToolPak. To enable it, follow these steps:
Choose Add-Ins from the Tools menu. Excel displays the Add-Ins dialog box.
Make sure the Analysis ToolPak option is selected.
Click on OK.
If you did not see an Analysis ToolPak option in step 2, it means that you did not install the option when you first installed Excel. You can rerun the Excel Setup program and choose to install the option. You must then enable the add-in, and you can use the function.
Many people use Excel as a simple database manager, entering information in different rows of a worksheet. As you are working with your data tables, you may come across a need to reverse the order of the rows in the table. Thus, if you have a table with ten rows, the rows would go from ten to one instead of one to ten.
There is no intrinsic function in Excel that allows you to flip data in this manner. However, you can use the sorting capabilities of Excel to accomplish the same thing by following these general steps:
Insert a new column immediately to the left of your data table.
In the cells of the new column, enter the numbers 1 through however many rows there are in your table.
Select the rows that make up your data table.
Choose Sort from the Data menu. Excel displays the Sort dialog box.
In the Sort By drop-down list, indicate you want to sort by your newly created column.
Click Descending as the type of sort.
Click on OK. Excel reorders your data in the reverse order of what it was.
If you have to do a lot of data flipping on a daily basis, using the above steps can get rather tiring. In this case, you may want to create a macro to do the job for you. The following macro, FlipRows, will do the trick:
Sub FlipRows() Dim vTop As Variant Dim vEnd As Variant Dim iStart As Integer Dim iEnd As Integer Application.ScreenUpdating = False iStart = 1 iEnd = Selection.Rows.Count Do While iStart < iEnd vTop = Selection.Rows(iStart) vEnd = Selection.Rows(iEnd) Selection.Rows(iEnd) = vTop Selection.Rows(iStart) = vEnd iStart = iStart + 1 iEnd = iEnd - 1 Loop Application.ScreenUpdating = True End Sub
In order to use this macro, all you need to do is select the rows you want flipped and run it. The macro will not change your data, other than flipping the rows. In other words, it will not add any columns of information.
An interesting feature of this approach is that you can quickly adapt it to flipping columns of data. All you need to do is change all occurrences of the word "Rows" to "Columns." Thus, the following becomes the new macro:
Sub FlipColumns() Dim vTop As Variant Dim vEnd As Variant Dim iStart As Integer Dim iEnd As Integer Application.ScreenUpdating = False iStart = 1 iEnd = Selection.Columns.Count Do While iStart < iEnd vTop = Selection.Columns(iStart) vEnd = Selection.Columns(iEnd) Selection.Columns(iEnd) = vTop Selection.Columns(iStart) = vEnd iStart = iStart + 1 iEnd = iEnd - 1 Loop Application.ScreenUpdating = True End Sub
Again, simply select the columns you want to flip and then run the macro.
Looking for a quick, easy way to remove hyperlinks without a macro? Believe it or not, you can accomplish this by using the Paste Special features of Excel. Follow these steps:
In a blank cell, enter the number 1.
Select the cell and press Ctrl+C. The cell contents (1) are now copied to the Clipboard.
Hold down the Ctrl key as you click each hyperlink you want to remove.
Choose Paste Special from the Edit menu. Excel displays the Paste Special dialog box.
Select the Multiply radio button.
Click OK. All the hyperlinks are removed, but the text of the hyperlinks remain.
Delete the cell you created in step 1.
We hebben onverwacht een erfenisje van 10.000 euro gekregen en wensen die nu gepast, maar risicoloos te beleggen. Onze uiteindelijke keuze gaat tussen twee banken: bank A biedt een jaarlijkse interest van 5%, berekend per maand. Bank B biedt dezelfde opbrengst, maar berekend per kwartaal. Wie van de twee is nu de beste?
De bedoeling is de beste toekomstige waarde (kortweg TW) van het basisbedrag (of de huidige waarde: HW) te weten te komen. Daarvoor bestaat volgende formule:
TW = HW * (1 + i)t
Daarin staat i voor de interest per periode en t is het aantal perioden. Nu is het kwestie om de twee overwogen gevallen foutloos in dit stramien te gieten. Laten we ons houden aan een vergelijking over één jaar. Wie daar al als beste uitkomt, zal dat ook wel over de daarna volgende jaren blijven. Bank A berekent per maand. Die 5% op jaarbasis is dus eigenlijk 5% / 12 elke maand, en het aantal termijnen is 12. Voor bank B wordt dit respectievelijk 5% / 4 (want er zijn vier kwartalen in één jaar) en 4.
De basisgegevens voor onze vergelijking van twee spaarformules.
We vullen deze gegevens in op rijen 4 en 6. De formule in cel B7 moeten we dan zorgvuldig plannen, zodat we ze in één beweging ook naar cel C7 voor Bank B kunnen kopiëren. Vandaar dat we de verwijzing naar het basisbedrag in cel B1 volledig absoluut maken (na het intikken of het klikken in die cel eenmaal op F4 drukken). Om een machtsverheffing aan te geven, moeten we het kapje (^) gebruiken. Maar als we op de overeenkomstige toets (Shift+6) drukken, dan gebeurt er niets. Windows wacht namelijk het volgende ingetikte karakter af om te weten of dat kapje niet er bovenop dient gezet te worden (in combinatie met klinkers een vaak voorkomend symbool in het Frans: ê, â, ô, …). Wij hebben enkel het kapje zélf nodig, dus drukken we daarna op de spatiebalk.
Rechts zien we welke formules gebruikt werden voor Bank A.
En verrassend genoeg stellen we vast dat de berekeningsfrequentie wel degelijk een ingrijpende rol speelt: Bank A gaat ons bijna 2,20 euro méér opleveren per jaar. Waardoor weer maar eens bewezen is dat kleine lettertjes altijd erg zorgvuldig dienen gelezen te worden! Zeker in de financiële wereld.
In een eerdere Exceltip hadden we het over de toekomstige waarde, of kortweg TW. Die staat voor de waarde die een basisbedrag gaat worden, als het belegd wordt tegen een bepaalde intrest over een aantal perioden. Er bestaat echter ook zoiets als een =TW() functie in Excel. Maar die staat niet echt voor hetzelfde. Dit vraagt om wat duiding…
De functie =TW() is van toepassing op een investering die we van plan zijn om op periodieke basis aan te vullen met een vast bedrag. Zeg maar de situatie van het bijvoorbeeld maandelijks sparen van precies hetzelfde bedrag. De functie kan tot vijf argumenten hebben. De eerste drie zijn om begrijpelijke redenen verplicht: het rentepercentage per termijn, het aantal termijnen dat we van plan zijn om dit vol te houden, en dan ofwel de betaling die per termijn zal worden verricht (kortweg ‘bet’), ofwel de huidige waarde van de belegging (‘hw’). Laten we hw weg, dan gaat Excel uit van de waarde nul en moet het argument bet gebruikt worden. Tenslotte is er nog een typegetal 0 of 1, naargelang de betaling aan het einde of aan het begin van elke periode gaat uitgevoerd worden.
Om de argumenten van =FW() goed te begrijpen, halen we er liefst wat hulp bij.
Er moeten nog een paar extra conventies afgesproken worden, voordat we de praktische werking van =TW() ten volle kunnen begrijpen. Zo geldt voor alle argumenten dat de bedragen die we betalen (denk maar aan stortingen op een spaarrekening) als negatieve getallen weergegeven worden. Bedragen die we ontvangen (zoals dividenden), dienen positief te zijn. Ook is er enige voorzichtigheid geboden met de eenheden bij de rente en het aantal termijnen: redeneren we bijvoorbeeld per maand, gaat het daarbij om een belegging gespreid over 8 jaar, en is de jaarrente 5%, dan dienen we in de functie respectievelijk 0,42% (zijnde 5% gedeeld door 12) en 96 (zijnde 8 maal 12) te gebruiken.
We sparen maandelijks 100 EUR, dus vermelden we dit als een negatief getal in =TW().
Willen we nu bijvoorbeeld te weten komen wat een maandelijkse spaarformule van € 100,-, betaald aan het einde van elke maand, en recht hebbend op een jaarrente van 5%, ons na vier jaar gaat opleveren, dan geeft de formule ons een eindbedrag van € 5.301,49. Opgelet: het gaat om sparen! Dit zijn dus stortingen, vandaar dat we de € 100,- als een negatief getal hebben ingegeven.
Werkt op: alle versies van Excel
Is there was a way to compare the contents of two Excel workbooks. Unfortunately, there is no built-in comparison feature, as there is in Word to compare two documents. There are third-party programs available that can help you out, and a quick search of the Web can help to locate such programs.
Depending on your needs, there can be an easier way. If the worksheets in each workbook are laid out the same, and you just want to find differences between values in the cells of each worksheet, then you can use formulas to compare worksheets. Try the following steps:
Create a new workbook called Compare.xls.
In cell A1 of the first worksheet in Compare.xls, enter the following formula:
=IF([WB1.xls]Sheet1!A1<>[WB2.xls]Sheet1!A1,"Different","")
Copy the formula from A1 into all the other cells that represent the range you want to compare. For instance, if you want to compare A1:G12 in both worksheets, then you would copy the formula from A1 into the full range of A1:G12.
These steps assume that the worksheets you want to compare are both named Sheet1, and they are in WB1.xls and WB2.xls, respectively. If you have other sheets in WB1.xls and WB2.xls to compare, you can use similar formulas in other sheets of Compare.xls.
When done, any cell that has the word "Different" in it represent cells that are different in the ranges being compared. Thus, if C7 had "Different" in it, then there is a difference between the cell C7 of Sheet1 in WB1.xls and cell C7 of Sheet1 in WB2.xls.
If you are comparing only numeric values between the two worksheets, you could use a different formula in step 2, above:
=[WB1.xls]Sheet1!A1-[WB2.xls]Sheet1!A1
The result is a worksheet that subtracts the values in one workbook from the other, which results in the numeric differences.
Shortening Zip Codes
If you need to truncate ZIP Codes quite often, you may be more interested in a macro-based approach.
The following macro will do the trick:
Sub ZIPShorter() For Each cell In Selection cell.Value = Left(cell.Value, 5) Next End Sub
All you need to do is select the cells containing the ZIP Codes, and then run the macro.
Grabbing the MRU List
Sub MostRecent()
Dim J As Integer
For J = 1 To Application.RecentFiles.Count
Cells(J, 1) = Application.RecentFiles(J).Name
Next J
End Sub
HIGHLIGHTING CELLS CONTAINING SPECIFIC TEXT
You can use the conditional formatting feature in Excel to help draw attention to cells that contain specific text in which you are interested. For instance, if you have a range of cells and you want to know which ones contain the letters "shawn," then you can do the following:
Select the range of cells.
Choose Conditional Formatting from the Format menu. Excel displays the Conditional Formatting dialog box.
In the drop-down Condition list, choose "Formula Is".
In the formula box, enter the following formula. (Make sure you replace A1 with the cell address of the cell in the upper-left corner of the range selected in step 1.)
=NOT(ISERR(SEARCH("Shaw",A1)))
Click on Format. Excel displays the Format Cells dialog box.
Using the controls in the dialog box, specify a format that you want used for those cells that contain the specified text. For instance, you may want bold text in a red typeface.
Click on OK to close the Format Cells dialog box.
Click on OK to close the Conditional Formatting dialog box.
You can make this approach even more general-purpose in nature by specifying a cell that contains what you want to search for. For instance, if you type "Shaw" in cell F7, then you could replace the formula in step 4 with the following:
=NOT(ISERR(SEARCH($F$7,A1)))
Now, you can search for something different just by changing the characters in cell F7.
CONDITIONAL FORMATTING FOR ERRANT PHONE NUMBERS
If you use Excel to store a list of phone numbers, you may want a way to determine if any of the phone numbers in your list are outside of a specific range. For instance, for your area, only phone numbers in the 240 exchange (those beginning with 240) may be local calls. You might want to highlight all the phone numbers in the list that do not begin with 240, and therefore would be long distance.
The way that you do this depends on whether your phone numbers are stored as text or as formatted numbers. If you enter a phone number with dashes, periods, parentheses, or other non-numeric characters, then the phone number is considered a text entry. If you format the cells as phone numbers (Format | Cells | Number tab | Special | Phone Number), then the phone number is considered a number and formatted for display by Excel.
If your phone numbers are text entries, then use these steps to apply the desired conditional formatting:
Select the cells containing the phone numbers. (For the sake of this example, I assume that the first cell you select is A3.)
Choose Conditional Formatting from the Format menu. Excel displays the Conditional Formatting dialog box.
Use the Condition drop-down to choose Formula Is. (Click here to see a related figure.)
In the formula area, to the right of the drop-down list used in step 3, enter the following formula, replacing A3 with the address of the active cell selected in step 1:
=LEFT(A3,3)<>"240"
Click Format to display the Format Cells dialog box. (Click here to see a related figure.)
Set the formatting options to highlight the errant phone numbers, as desired.
Click OK to dismiss the Format Cells dialog box. The formatting you specified in step 6 should now appear in the preview area for the condition.
Click OK.
These steps will even work if the phone numbers are numeric, but you may want to use a different approach if the phone numbers were entered as numbers.
Select the cells containing the phone numbers. (For the sake of this example, I assume that the first cell you select is A3.)
Choose Conditional Formatting from the Format menu. Excel displays the Conditional Formatting dialog box.
Use the Condition drop-down to choose Cell Value Is.
Use the next drop-down to choose Not Between.
Set the lower and upper boundaries of the condition to 2400000 and 2409999, respectively. You are specifying that the condition is met if the number is not between 2400000 and 2409999, which is the range of acceptable phone numbers. (Click here to see a related figure.)
Click Format to display the Format Cells dialog box.
Set the formatting options to highlight the errant phone numbers, as desired.
Click OK to dismiss the Format Cells dialog box. The formatting you specified in step 7 should now appear in the preview area for the condition.
Click OK.
If you want to convert your textual phone numbers to numeric phone numbers, so that you can use this last method of conditional formatting, you need to "clean up" your list of numbers. In other words, you need to remove all non-numeric characters from the phone numbers. You can do this using Find and Replace to repeatedly remove each non-numeric character, such as dashes, periods, parentheses, etc. Once the phone numbers are clean, you can format them as phone numbers (using the Format | Cells sequence mentioned earlier in this tip) and use the conditional formatting just described.
Creating Worksheets with a Macro
Excel lets you create new worksheets in a number of different ways. What if you want to create a new worksheet and name it all in one step? The easiest way to do this is with a macro. The following is an example of a macro that will ask for a name, and then create a worksheet and give that worksheet the name provided.
Sub AddNameNewSheet1() Dim Newname As String Newname = InputBox("Name for new worksheet?") If Newname <> "" Then Sheets.Add Type:=xlWorksheet ActiveSheet.Name = Newname End If End Sub
This macro works fine, as long as the user enters a worksheet name that is "legal" by Excel standards. If the new name is not acceptable to Excel, the worksheet is still added, but it is not renamed as expected.
A more robust macro would anticipate possible errors in naming a worksheet. The following example code will add the worksheet, but keep asking for a worksheet name if an incorrect one is supplied.
Sub AddNameNewSheet2() 'Remember where we started 'Not needed if you don't want to return to where you started but want 'to stay on the New Sheet Dim CurrentSheetName As String CurrentSheetName = ActiveSheet.Name 'Add New Sheet Sheets.Add 'Make sure the name is valid On Error Resume Next 'Get the new name ActiveSheet.Name = InputBox("Name for new worksheet?") 'Keep asking for name if name is invalid Do Until Err.Number = 0 Err.Clear ActiveSheet.Name = InputBox("Try Again!" _ & vbCrLf & "Invalid Name or Name Already Exists" _ & vbCrLf & "Please name the New Sheet") Loop On Error GoTo 0 'Go back to where you started 'Not needed if you don't want to return to where you started but want 'to stay on the New Sheet Sheets(CurrentSheetName).Select End Sub
Defeating Automatic Date Parsing
Excel is normally pretty smart when it comes to importing data, but sometimes the automatic parsing it uses can be a real bother. For instance, you may import information that contains text strings, such as "1- 4- 9" (without the quotes). This is fine, but if you do a Replace to get rid of the spaces, Excel automatically converts the resulting string (1-4-9) to a date (1/4/09).One potential solution is to copy your information to Word and do your searching and replacing there. The problem with this solution is that when you paste the information back into Excel, it will again be parsed as date information and automatically converted to the requisite date serial numbers.The only satisfactory solution is to make sure that Excel absolutely treats the resulting strings as just that--strings--and not as dates. This can be done in one of two ways: just make sure that the original text begins with either an apostrophe or a space. This can be ensured by using the Replace feature of Excel (depending on the data you have to work with) or by using the Replace feature of Word (which is much more versatile).With an apostrophe or space at the beginning of the cell entry, you can remove additional spaces or characters from the cell contents. If the result is text that looks like a date, Excel will not parse it as such because the leading apostrophe or space forces treatment as text.Another way to perform the task is to follow these steps. (Assume that the original data is in the range A2:A101).
Insert a column to the right of the original data.
In cell B2, enter the following formula:
=SUBSTITUTE(A2," ","")
Copy the contents of B2 into the range B3:B101.
Select the cells in the range of B2:B101.
Press Ctrl+C to copy the range to the Clipboard.
Select cell A2.
Choose Paste Special from the Edit menu. Excel displays the Paste Special dialog box.
Make sure the Values option is selected.
Click on OK.
Delete column B.
These steps work because the output of the SUBSTITUTE function is always treated as text. When you copy and paste text values, they are treated as text with no additional parsing done by Excel.
Calculating Fractions of Years
One of the types of data that Excel allows you to store is, of course, dates. At some point you may wish to perform some calculations with the dates in your worksheet. It is not uncommon to need to figure out the percentage of a year represented by the difference between two dates. Excel allows you to calculate this easily using the YEARFRAC worksheet function. This function is part of the Analysis ToolPak provided with Excel.
To use the function, all you need to do is provide two dates and a value that specifies how Excel should calculate the fractional year:
=YEARFRAC(DateOne, DateTwo, Basis)
The dates used by YEARFRAC can be either static dates, or they can be references to cells that contain dates. The Basis value ranges between 0 and 4, with 0 being the default. The following are the different meanings for the Basis:
You should note that if the YEARFREQ function does not work on your system, it means you have not installed or enabled the Analysis ToolPak. To enable it, follow these steps:
Choose Add-Ins from the Tools menu. Excel displays the Add-Ins dialog box.
Make sure the Analysis ToolPak option is selected.
Click on OK.
If you did not see an Analysis ToolPak option in step 2, it means that you did not install the option when you first installed Excel. You can rerun the Excel Setup program and choose to install the option. You must then enable the add-in, and you can use the function.
Pasting Without Updating References
Subscriber Alistair Gordon asked if there is a way to copy or move a selection within a worksheet without Excel changing all the references within the selection. The answer, of course, is that it depends. (Don't you just love that about Excel?) Let's take a look at how you can both copy and move selections in Excel.
If you are copying a selection, then Excel will update all relative references within the selection when you paste it. The solution, of course, is to make sure that all the references within the selection are absolute before doing the copy and paste. Making the changes to the formulas by hand is tedious. You can use the following macro to convert all the formulas in the selection to their absolute equivalent:
Sub ConvertToAbsolute() Dim c As Variant Application.ScreenUpdating = False For Each c In Selection c.Value = Application.ConvertFormula(c.Formula, _ xlA1, , xlAbsolute) Next c Application.ScreenUpdating = True End Sub
Once this macro is run, you can copy and paste the selection without Excel doing any updating to references. Once the pasting is done, you can change the references in the selection (and in the original range, if it still exists) by selecting the range and applying this macro:
Sub ConvertToRelative() Dim c As Variant Application.ScreenUpdating = False For Each c In Selection c.Value = Application.ConvertFormula(c.Formula, _ xlA1, , xlRelative, c) Next c Application.ScreenUpdating = True End Sub
This macro will change all formulas in the selected range to their relative equivalent. Remember that this will affect all formulas--which means that if the formulas in the range contained both relative and absolute references, when this macro is done, they will all be relative.
If you are moving a selection, then Excel does not update cell references in the move. You can move either by selecting the range and using the keyboard (pressing Ctrl+X to cut and then Ctrl+V to paste the selection) or the mouse (dragging the selection to a new location). In either case, Excel leaves the references in the selection exactly the same--relative or not--without updating.
So far I have discussed what Excel does with the references in the selection being copied or moved. What about references to the information in the selection? If you are copying, then Excel leaves references pointing to the original range. If you are moving a selection, then Excel updates references to that selection, regardless of whether they are relative or absolute. If you don't want the information updated during a move, then the solution is to make a copy of the range and then delete the original.
Linking Comments to Multiple Cells
Summary: Do you need a comment to apply to more than one cell? Built-in comments are only applicable to a single cell, but you can create a workaround that uses text boxes, if desired. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, and Excel 2003.)
When you insert a comment into a worksheet, that comment is associated with a single cell. There may be times when you want to have a single comment associated with two or more cells, however. Unfortunately, Excel doesn't provide this capability--there is a strict one-to-one correspondence between comments and cells.
You can, however, use a workaround--create your own comments. You can do this using a text box to contain your comment, and then draw lines between the text box and whatever cells the comment applies to. If you normally want your comments hidden, then you will need to use a macro that takes care of making the text box and lines visible or invisible.
For instance, assume that you create a comment in a text box named Text Box 1. Further, assume that you have two lines leading from the text box to the cells to which the comment applies. The first line, named Line 1, leads to cell C15. The second line, named Line 2, leads to cell F7. You could add the following macro to the worksheet's object:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Shapes("Text Box 1").Visible = False Shapes("Line 1").Visible = False Shapes("Line 2").Visible = False If Target.Address = "$C$15" Then Shapes("Text Box 1").Visible = True Shapes("Line 1").Visible = True End If If Target.Address = "$F$7" Then Shapes("Text Box 1").Visible = True Shapes("Line 2").Visible = True End If End Sub
Anytime a selection is made on the worksheet, the three objects are hidden. If cell C15 is selected, the textbox and the line appropriate line are made visible. Similarly, if cell F7 is selected, the textbox and its line are made visible.
Automatically Printing a Range
Subscriber Werner van Blerk asked if there was a way to have Excel automatically print the contents of a cell range at a given time each day. He has a worksheet that is always open, logging input from a different program. At a particular time each day he wants to automatically print a range that contains summary information.
There are a couple of approaches you could use to this problem, including using Windows Scripting to handle the printing. However, since the workbook is always open, you don't have to resort to that. Instead, you can rely on the native macro capabilities of Excel.
The solution considered here requires two macros. The first is one that runs whenever the workbook is first opened. It sets up the correct event handler to trigger the actual macro that does the printing.
Private Sub Workbook_Open() 'Schedule the printing Application.OnTime TimeValue("17:00:00"), "PrintMe" End Sub
This particular marco sets the OnTime method to be triggered whenever 5:00 p.m. is reached. To specify a different time of day, simply change the time (using 24-hour notation) in the macro. When 5:00 p.m. rolls around, Excel will run the PrintMe macro:
Private Sub PrintMe() Sheets(2).PrintOut 'Reschedule the printing Application.OnTime TimeValue("17:00:00"), "PrintMe" End Sub
This macro does nothing but print the second sheet in the workbook and then reset the OnTime method to again be triggered at 5:00 p.m. the next day. If you want a different data range to be printed, simply change the object used with the PrintOut method in the first line of the macro.
Macro Runs Slowly, but Steps Quickly
Subscriber Fredric Goodman wrote about a problem he was having with a macro. When he is running the macro in the VB Editor using F8 (stepping through the macro), it completes in just a few minutes. When he runs the macro outright, it seems to take forever to run, often taking 20 minutes or more to execute. Even though Fredric's workbook is huge (46 MB), the time differential between the two methods of running is bothersome.
Problems like this can be baffling, and they often take some heavy-duty analysis in order to figure out. A good place to start is to add some "timer code" in your macro. Add a small routine that saves a time value, and another routine that compares that saved value to the current time and displays the difference. At the beginning of a section of code you want to analyze, you call the first routine (which saves the start time), and then at the end of the section of code you call the second routine. In that way, you can determine which portions of your code are taking the longest time to execute. These are the code sections you then focus on, so you can figure out what they are doing that is taking so long.
Another thing to make sure is that you add these two lines at the beginning of your macro:
Application.ScreenUpdating = False
Application.EnableEvents = False
These turn off screen updating, which can slow down a running macro, and disables events. This last line is included so that changes done by the macro in your worksheet won't trigger Excel's recalculation routines. If your macro is making a lot of changes in the data in the worksheet, and a full recalculation is triggered after each change, then with such a large workbook, lots and lots of time can be spent just doing the recalc. At the end of your macro, you reverse the effect of the two lines you added:
Application.EnableEvents = True
Application.ScreenUpdating = True
Alerts About Approaching Due Dates
Subscriber Jonathan Roberts developed a worksheet that tracks due dates for various departmental documents. He wondered if there was a way for Excel to somehow alert him if the due date for a particular document was approaching.
There are several ways that this can be done in Excel, and you should pick the method that is best for your purposes. The first method is to simply add a column to your worksheet that will be used for the alert. Assuming your due date is in column F, you could place the following type of formula in column G:
=IF(F3<(TODAY()+7),"<<<","")
The formula checks to see if the date in cell F3 is earlier than a week from today. If so, then the formula displays "<<<" in the cell. The effect of this formula is to alert you to any date that is either past or within the next week.
Another approach is to use the conditional formatting capabilities of Excel. Follow these steps:
Select the cells that contain the document due dates.
Choose Conditional Formatting from the Format menu. Excel displays the Conditional Formatting dialog box. (Click here to see a related figure.)
Make sure the first drop-down list is "Cell Value Is." (This should be the default.)
Make sure the second drop-down list is "Less Than."
In the formula area, enter "=TODAY()" (without the quote marks).
Click the Format button. Excel displays the Format Cells dialog box. Using the Color drop-down list, choose the color red.
Click OK to close the Format Cells dialog box.
Click Add. The Conditional Format dialog box expands to include a second condition.
Make sure the first drop-down list for the second condition is "Cell Value Is." (This should be the default.)
Make sure the second drop-down list is "Less Than."
In the formula area, enter "=TODAY()+7" (without the quote marks).
Click the Format button. Excel displays the Format Cells dialog box.
Using the Color drop-down list, choose the color blue.
Click OK to close the Format Cells dialog box. Click OK to close the Conditional Formatting dialog box.
This is a two-tiered format, and you end up with two levels of alert. If the due date is already past, then it shows up as red. If the due date is today or within the next seven days, then it shows up in blue.
Subscriber Frank D'Urso wrote that he was having a problem with Excel not opening a workbook whenever he would double-click on it in Windows. Excel would start, but the workbook would not load, and Excel would report that it couldn't find the file that was double-clicked.
This sounds like a problem with the file associations in Windows. The first thing to try is to force Excel to reconstruct all the proper association information. You do that by following these steps:
Make sure that Excel is not running (exit the program).
Click the Start button to displays the Start menu.
From the Start menu, choose Run. Windows displays the Run dialog box. (Click here to see a related figure.)
In the Open box, enter the full path name to your Excel program, followed by the /regserver switch. If the full path name includes spaces, surround the full path name by quote marks. The following is an example of what you can enter in the Open box (your path may be different):
"c:\Program Files\Microsoft Office\Office\Excel.exe" /regserver
Click OK.
Now try double-clicking on your workbook again. It should open as you expect. If it doesn't, try this:
In Excel, choose Options from the Tools menu. Excel displays the Options dialog box.
Make sure the General tab is selected. (Click here to see a related figure.)
Make sure the Ignore Other Applications check box is selected.
Click on OK.
If you still cannot double-click on a workbook to open it, try these steps:
Get out of Excel completely.
Open a My Computer window.
Choose Tool | Folder Options. You should see the Folder Options dialog box.
Make sure the File Types tab is displayed. (Click here to see a related figure.)
In the list of registered file types, find and select the entry for XLS (Microsoft Excel Worksheet).
Depending on your version of Windows, click on either the Edit or the Advanced button. You should see the Edit File Type dialog box.
In the list of actions, choose Open.
Click the Edit button. Windows displays the Editing Action dialog box. (Click here to see a related figure.)
The Application Used to Perform Action box contains the actual command line used to start Word. Double-check the line to make sure there are no strange extra filenames at the end of the command line.
Make sure the Use DDE check box is selected, and that the DDE Message box is set to the following:
[open("%1")]
Click on OK.
Close all the other open dialog boxes.
Using GEOMEAN With a Large List
Subscriber Ken Urban wrote about a problem he was having with the GEOMEAN function. When he attempts to use the function on a large number of values (3,500 rows of data), he gets a #NUM error value returned.
The GEOMEAN function is used to return the geometric mean of a series of values. The GEOMEAN of n numbers is the nth root of the product of the numbers. For example, if the series of values contains four values (A through D), then the product of those numbers is A * B * C * D, and the GEOMEAN is the fourth root of that product.
The #NUM error would be return if any of three conditions were met: any of the values was equal to zero, any of the values was negative, or the limits of Excel were exceeded. It is likely that it is this last condition that Ken is running into, particularly if any of his 3,500 values are large.
Since GEOMEAN finds the product of the 3,500 numbers (multiplies them all by each other) and then takes the nth root, the product may easily be too large for Excel. The largest positive number in Excel is 9.99999999999999 * 10^307 (in scientific notation this is written as 9.99999999999999E+307) If the product gets larger than this number you will get a #NUM error for the function.
The solution is to use logs to do the calculation. This is easiest to understand when you look at a transformation of the GEOMEAN function:
GEOMEAN = (X1*X2*X3*...*Xn)^ (1/n) ln(GEOMEAN) = ln((X1*X2*X3*...*Xn)^ (1/n)) ln(GEOMEAN) = (1/n) * ln(X1*X2*X3*...*Xn) ln(GEOMEAN) = (1/n) * (ln(X1)+ln(X2)+ln(X3)+...+ln(Xn)) ln(GEOMEAN) = average(ln(X1)+ln(X2)+ln(X3)+...+ln(Xn)) GEOMEAN = exp(average(ln(X1)+ln(X2)+ln(X3)+...+ln(Xn)))
If you follow through the above, you see that GEOMEAN is equivalent to the exponent of the average of the logs of the values. You can calculate the desired result by using the following array formula instead of the GEOMEAN function:
=EXP(AVERAGE(LN(A1:A3500)))
This assumes that the desired values are in the range A1:A3500. Since it is an array formula, you must enter it into a cell by using Ctrl+Shift+Enter.
Counting Precedents and Dependents
Because Excel allows you to create formulas that refer to other cells, it stands to reason that cells can be dependent on each other. In fact, Excel has two technical terms that are used to define the relationship between cells: precedents and dependents. Precedents are those cells on which a formula is based. Thus, if cell A5 contains the formula =A3 + A4, then both A3 and A4 are precedents for cell A5. Dependents are the reverse of precedents. Thus, in this example, cell A5 is a dependent of cells A3 and A4. You can use the auditing tools in Excel to graphically depict these relationships between cells, as described in other issues of ExcelTips.
What if you want to know how many dependents and precedents there are in a worksheet, however? There is no Excel command that displays this information. You can use a macro to calculate and display this information, however. The following macro will do just that:
Sub CountDependentsPrecedents() Dim ws As Worksheet Dim lDep As Long Dim lPre As Long On Error GoTo err For Each ws In Worksheets ws.Select lDep = 0 lPre = 0 lDep = Range("a1:iv65536").Dependents.Count lPre = Range("a1:iv65536").Precedents.Count MsgBox "Worksheet: " & ActiveSheet.Name & vbCr & _ "Dependents: " & lDep & vbCr & _ "Precedents: " & lPre Next ws Exit Sub err: Resume Next End Sub
When you run this macro, it steps through each worksheet in your workbook and displays the number of dependents and precedents in each.
Top resources for Office 2007
Mini-glossary: Office 2007 terms you should know
A quick-glance guide to the Office 2007 editions
10 things you should know about Office 2007 compatibility issues
10 Word 2007 features you can skip
10 ways you can tweak Word 2007 to fit your working style
10+ tips for boosting your Word 2007 productivity
10 key enhancements in Excel 2007
10 key enhancements in Access 2007
10 key enhancements in Outlook 2007
10 key enhancements in PowerPoint 2007