Something different today: If you are a math neophyte, you will love the add-in created by Brad Hunter, called Math Easy. Brad writes:

The product is straight forward; a "Math" button is visibly added to the Excel toolbar, performing "Addition", "Subtraction", "Average", etc. No prior Excel experience is required. Hopefully beginners can use Math Easy as a stepping-stone to learning more advanced math. Visit http://www.excelmatheasy.com to download the software, view screenshots, and gain additional information.

The price is about $5 - a great investment. Check it out.

Joe is trying to puzzle out an error on a new macro that he just wrote. The line that assigns NextRow is returning an error that says "Object Variable or With Block Variable Not Set"

Object variables are pretty cool. In the old tips at MrExcel, I would use regular variable like these:
OrigName = ActiveSheet.Name
Worksheets(OrigName).Copy

There is a better way. You can define a variable to be any object, such as a worksheet. There are several advantages to this. It is shorter to use the object variable in place of Worksheets(OrigName). Also, as you are typing code, VBA will know that the variable is a worksheet and offer the appropriate properties and methods after you type the dot. Here is the new code:
Dim WSO as Worksheet
Set WSO = Activesheet
WSO.Copy

The trick, though, is that when you have an object variable, it has to be assigned with the Set keyword. If you fail to put the Set in the code, you will get the somewhat non-intuitive "Object Variable or With Block Variable Not Set". Joe simply needs to precede his variable assignment with the word Set.

As I continue reviewing the tip of the week archives, you will see object variables being used more frequently.

Delaine asks: I need to enter a series of time values in military time. Then, I need to total them up and round to the nearest quarter hour.

To enter values in military time, select those cells and then Format > Cells. Click the Number tab. Click Time in the left listbox. Select 13:30 as the number format. You can then enter the times.

To get a total in cell A99 of all the times, you first must format cell A99 to show hours in excess of 24. Select A99, Format > Cells. Click Custom in the left listbox. In the Custom Format box type: [h]:mm

You can now enter a formula to sum the times, something like =SUM(A2:A98). Since you want to round to the nearest quarter hour, use this formula instead:
=ROUND(SUM(A2:A99)*96,0)/96

There are 96 quarter-hours in a day. Multiplying the total time by 96, rounding, and then dividing by 96 will give you the nearest quarter hour.

Ryan writes: I have a very long spreadsheet with many columns of numbers. I am looking for a way to make the header row "float" near the top of the data pane while scrolling down for each reference.

Ryan - set up you window so that the header row is the top row in the window. Then, put your cell pointer in column A, in the row just below the headers. From the menu, choose Window - Freeze Panes.

Anything visible in the window above and to the left of the cell pointer will remain visible as you scroll down the worksheet. Note that since the cell pointer was in column A, only the top row of headers will remain visible.

Paul has a column of names. Is there a function which will count the number of text entries in the column?

Yes, Paul, this is frustrating. I will bet you are a recent Lotus 1-2-3 user. In Lotus, of course, @COUNT(A1:A100) would work, but alas in Excel, the COUNT() function only counts numeric entries. These small changes are one of the frustrating challenges in switching from Lotus to Excel. It would be so much easier if Microsoft were to include a help topic entitled, "Really Minor Changes we Made to the Most Popular @Functions."

The new function in Excel to count a column that contains text entries is
=COUNTA(A1:A100)

It's not a personal vendetta against former Lotus users - merely an effort to improve the product. You can now count everything with =COUNTA() or just count numeric data with =COUNT()....

Hey - by the way - if you are switching from Lotus, the @VLOOKUP has been improved - you will have to add a 4th argument of FALSE to get the Excel VLOOKUP to work like the Lotus @VLOOKUP.....

Ketan writes I want to write an formula which calculates the max of length of the column values. For e.g. if we have a column

a
aa
aaaa
aa
aaaaaa

Then I want to insert a row at the top and formula should return the max of the length of the column values. In this case 5.

I know that this can be done by inserting a column and typing the formula =len(a1) then writing max(b1:b5), but when I have a worksheet containing large no. of columns, a single formula to calculate like this will be very helpful.

Yes, Ketan this formula would be very helpful. There are a whole class of super-formulas in Excel that can solve problems like this one in a single cell. Veteran MrExcel readers know this is my very favorite tip. It will move you to the front of the pack, ahead of 95% of other Excel users. You can read the complete details about CSE Formulas. Assuming your column of data is in A2:A2000, you would use this formula:
=MAX(LEN(A2:A2000))
You must hold down Ctrl, Shift and then hit Enter. I call these CSE formulas as a reminder that the magic key sequence is Ctrl + Shift while hitting Enter.

These formulas are very powerful and allow you to replace an entire column of formulas with a single formula.

Karthik asks: I am working on a event code for highlighting the active cell, basically show change the background color of the active cell. I used the change event and this changes the value of the active cell only when the value has changed.

What a cool question. The trick is to use the Worksheet_Selection change event instead of the Worksheet_Change event. This special event handler macro has to be entered on the code pane associated with your particular worksheet. A discussion of how to find this in the project pane is at this archived tip.

You would then enter the following code:

Public OldRng As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not OldRng Is Nothing Then
        OldRng.Interior.ColorIndex = xlNone
    End If
    Target.Interior.ColorIndex = 6
    Set OldRng = Target
End Sub

This program uses a public variable called OldRng to remember the last range that was selected so that the color can be reset back to white when you select a new range.

P.K. asks: I have a worksheet where I input some values. Upon clicking a macro button, I want the values or its calculated results to be appended to a different sheet or file.

Good question. Let's say the values are in cells C10 and E12 of Sheet1 and you want them appended to the next row in Sheet2.

The real trick here is the code to find the next row on Sheet2. Starting at the last row in Sheet2 (A65536) and then using .End(xlup) will find the last row with data in column A. Adding 1 to that row will point you to the next row for data on Sheet2. Use this code:

Sub CopyThem()

    NextRow = Worksheets("Sheet2").Range("A65536").end(xlup).Row +1

    Worksheets("Sheet2").Cells(NextRow, 1).Resize(1, 2).value = Array( _

        Worksheets("Sheet1").Range("C10").value, _

        Worksheets("Sheet1").Range("E12").value)

End Sub

Charlie asks Is there a way in VBA to remove a worksheet which will bypass the "The selected sheets will be permanently deleted. Click OK" prompt"

This particular message is one that can be suppressed by using the Application.DisplayAlerts property. Use the following code:

Application.DisplayAlerts = False
Worksheets("MySheet").Delete
Application.DisplayAlerts = True

Felipe writes: I need to find the elapsed time between two moments. Each moment has a date and a time. I need to know how much time was elapsed, in minutes or in hours and minutes.

If the times are in cells A1 & B1 and you want the elapsed time in C1, follow these steps:
In cell C1, set up a custom number format of [h]:mm
Enter this formula in C1: =MAX(A1:B1)-MIN(A1:B1)

The square brackets around the h will insure that times over 24 hours are still reported as hours instead of days.

Also, the result of a date/time calculation can never be negative. I've used the Max and Min to make sure the earlier time is always subtracted from the later time. You could also use =ABS(B1-A1) or simply make sure that B1 will always be greater than A1.

Gary writes: When I copy cell(s) to a another page, then I link them, I get "0" in the empty cells were there is no data. All the cells are formatted the same. Do you know what the problem could be?
Gary is using Edit Copy, then Edit - PasteSpecial - Link Cells feature. The problem is not with the Paste Link. It is bahaving by design. If you have a range of cells, say A1:G10 and you copy, paste link, it pastes links for all the cells, even the blank ones on the theory that you may someday fill in the blank cells.

The real problem is that if you enter this simple formula in Excel, =Z1 and cell Z1 is empty, the answer to that formula is 0 instead of a blank. This can be horribly frustrating.

There is no perfect solution to this problem, but here are two ideas:

1. Make the original cells with no data contain a blank cell instead of an empty cell. For each cell that had no data, put a single apostrophe - this character: ' in the cell. It will not show up in the cell, but it will signal Excel that this is a blank cell, not an empty cell. The result of the link will then be blank.

2. One problem could arise: This causes the cell to no longer be considered numeric. If that empty cell is used in any formulas, you may find that the result of the formula is now #VALUE!. If this is the case and you need to leave the cell as empty, then you can use this formatting trick on the copied cells. If your current numeric format is 0.00, then set up a custom number format of
0.00;0.00;
The last semi-colon will cause cells with zero values to appear as blanks. To set up a custom number format, highlight the cells, then Format - Cells. Click the Number tab. In the Category listbox, select Custom. In the Type: box, enter the custom number format shown above.

From this weekend's mailbag:

Carla writes: how do you copy & paste a subtotaled table onto a worksheet?

Let's say you have a thousand-line table. With clever subtotaling, you create a 30-line summary. You want to copy this summary onto a worksheet containing other information. It's a one-page summary report with data from all over heck n gone.

With ordinary copy-and-paste, the whole entire damn table gets copied. I've been re-tying the information most laboriously. There must be a shortcut!

Yes, Carla there is. It involves the use of the Goto-Special dialog and then selecting Visible Cells Only. All the details are in the tip of the archive. See Use Goto - Special - Visible Cells to copy just the visible cells. This is a great tip for copying the results of using Data - Subtotals.

Troy asks: Is there a way to convert a database of phone numbers that look like this
123/456-7890 or 123-456-7890
to (123) 456-7890?

Yes - Assuming your phone numbers are in A2:A4001, enter this formula in a blank B2:
="("&Left(A2,3)&") "& Mid(A2,4,8)
Then copy down to B2:B4001.

Before deleting column A, you will want to change the formulas in column B to values. Highlight B2:B4001. Use Ctrl+c to Copy, then Edit - PasteSpecial - Values - OK to change the formulas to values.

Arun asks, all Excel functions - list them please
How do people end up with Excel, but not the help files? You can download a dictionary of Excel functions here:
http://homepage.ntlworld.com/noneley/

During college, I was on a academic track that required me to take four semesters of physics. For a year and a half, I took in the theories of physics and had everything down pretty well. Then, at the start of the 4th semester, I walked into the auditorium at Nieuwland Hall on the campus of Notre Dame for the final course in the series, Quantum Physics.

The professor walked up to the board, wrote the familiar equation that was the basis for Newtonian physics, "F=ma". This stands for Force = Mass x Acceleration. Then, he dropped a bomb shell. "Everything that we have taught you over the last 3 semesters is wrong. F does not equal ma." He went on to say that F=ma works for the world of bowling balls but falls apart when you get to electron-sized particles. When you get to that size particle, the equation becomes F=ma + some other thing. I never learned that part, I was too in shock that everything they had taught me was wrong.

Well, MrExcel readers, some of the things I taught you a long time ago are outdated. Working as a full-time Excel consultant, I am learning faster, easier ways of doing things all the time. Some of the weekly tips from the Tip001.shtml through Tip020.shtml were written before I learned a lot of tricks. It is somewhat embarrassing to go back and realize there are incredibly faster ways to handle certain coding situations.

When I wrote tip009.shtml about user forms in 1999, I had done a total of one user form in my life. Now, I've done 100's of them and actually have a little more insight on the matter.

So, starting today, I am launching a concerted effort to modernize the old tips of the week. I will go sequentially, and will add the "Last Reviewed on " tag after the original published date. I'll update any links, try to make things less ambiguous, and add any new insights that a few more years of experience have provided.

Renato from Italy asks: How can I open a workbook in VBA choosing from a list. When I record a macro, it hard-codes the name of the selected file in the macro.

There is a command in VBA called GetOpenFileName. It displays the File Open box. You can naviagate to a directory, select the file and then click Open. At this point, the command does not open the file, it merely passes the name back to your program. Here is an example of the code in use:

Sub TestIt()
NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
End If
End Sub

Roger asks: How can I get a macro to run before a file is closed or saved?
The macro needs to be entered on the code pane associated with "ThisWorkbook". For a detailed view of how to do this, check out Tip 055, which discussed a Before_Print macro. Follow those instructions, but from the right dropdown, select either BeforeClose or BeforeSave.

Ken writes: I have a worksheet event handler that loops, at least 16 times. What is going on?
Ken's handler was simple - if the entry was non-numeric it would change the entry to upper case. Here is the problem. When he changed the value of the entry to upper case, that is another worksheet change and the event would fire again. Every time the change event would fire, Ken would change the worksheet and the macro was getting called recursively, until the call stack ran out of memory.

The solution is to temporarily stop events from running while you change the value to upper case. You can do this by changing the value of Application.EnableEvents to False. Here is the corrected macro:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
If Not IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End If
End Sub

Andy from the U.K. asks today's most interesting question. I have a VBA routine in one workbook that creates several other workbooks. I would like to be able to dynamically add a hyperlink in each new workbook that will point back to the workbook that generated the new workbooks.
Andy - this is a cool idea. Without the benefit of seeing your code, I can imagine something like this would work:
Sub Testit()
Creator = ActiveWorkbook.FullName
.... Andy's code to create the new workbook....
ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), Address:= _
Creator, TextToDisplay:="Click Here to Return to " & Creator
End Sub

Long-time MrExcel readers may recall the old Challenge of the Month contest. I've posted a new Challenge of the Month, the first in several years. It is a tough one. Anyone is welcome to take a stab at the problem to win some cool prizes. Check it out.

Yesterday I mentioned that Chris from New Zealand had a problem using the free Delete External Links utility from Microsoft. I pointed him to a download from a German Excel site. Chris wrote back today with a report on the add-in. Here is his report:

I have tried the German version. It is only 20KB versus the 168KB of the Microsoft version. It is a very simple add-in and doesn't have the bells and whistles of the other one. However it clears the error message I found with the MS one. It also clears links very simply as well.

How it works:
A dialogue box appears asking what types of files you want to search for links ( such as .xls). You need to be actually in the file to be searched. I tried all sorts of ways of searching for links such as D:\*.xls, and C:\*.xls when it didn't appear to clear all links. This showed me all links in a 50 sheet workbook, gave me the option of deleting each one and showed the complete path of each external link one at at time. It showed me links that I didn't even know existed. It worked efficiently, and on saving the workbook and opening again, all external links are now GONE!!.

There is one flaw in the German add-in. It could not find a series in a chart that referred to an external server ( O:) drive. However, even the MS addin had trouble with this one until I chose the Aggressive mode for charts and then it solved the problem.

Thanks to Chris for reviewing this add-in. If you are having problems getting rid of external links, check out this cool add-in from German Excel guru Hans W. Herber.

David asks I read your tip about a macro to save a workbook with a filename found in cell A1. How can I change the name of the worksheet only, with text located in cell A1.

Use this macro:

Public Sub RenameSheet()
NewName = Range("A1").Value
ActiveSheet.Name = NewName
End Sub

Chris had downloaded the free Microsoft add-in to find and remove links to other workbooks. It worked fine, but then he started getting an error message: "Routine Location Error Code: 2". I could not find anything at the Microsoft site, but a German-language Excel message board mentioned a similar problem. Without an answer from Microsoft, it is hard to say why this happens on some computers and not others. If you find that you are experiencing this problem, you should uninstall the add-in on the Tools > Add-ins dialog.

Main wrote to ask if there is a keyboard shortcut for moving to other tabs in a workbook. I always use Ctrl + PgUp to move to a tab to the left of the current worksheet, and Ctrl+Pgdn to move to a tab to the right.

Paul asks, I've got for example the following link: =Sheet2!$B$4. Is it possible to make the sheetname (Sheet2) variable e.g. referring to a cell with the name of the sheet?

Paul - the =Indirect() function should help you in this regard. Anywhere that you would normally type a cell reference, you can use the Indirect function. Inside the parentheses, you can use concatenation characters to build a valid cell reference. For example, in cell E4, I typed "Sheet2". In another cell, I typed this formula:
=Indirect(E4&"!B4")
The formula correctly returned the contents of cell B4 on Sheet2. The downside of using this method is that since the argument B4 is text, Excel will not update this formula if you ever cut and paste B4 to a new location.

That's all for today - I've got a lot of consulting work to get done today. Sorry to all who I did not have time to answer. If you did not see your question answered, try posting it to the message board - many loyal readers help with questions on the board - it is a great way to get answers quickly.

Charlie from Cleveland writes, Assume you are miraculously beating the best team in baseball 2 games to 1 in a best of five series. Your ace pitcher starts to look weary in the 6th inning and then gets in trouble in the 7th inning. Can you show me a formula in Excel to tell if I should pull him out of the game?

Sure, Charlie, it would take a simple If formula. These come in real handy the next time you have a chance to pull off an incredible upset. Try a simple formula like this in cell A1:

=IF(Bartolo="Weary","Absolutely Pull Him Out of the Game","Pull Him Out of the Game Anyway")

Congratulations to all Seattle Mariner fans. Although MrExcel would have enjoyed watching our Wahoos advance to the ALCS, I have to admit that this is Seattle's year, and it would not have been right for them to have not advanced for a chance at the Pennant. Best of luck in the ALCS.

Good Monday Morning!

Since most of the MrExcel readers read the page at work, I'll start with an introduction here. I was reading the local newspaper and they had an article about the trendy, new, easy-to-use Weblog technology. Basically, it is easy to create a daily posting to your web site, without having to go through the hassles of FTP, etc.

It sounded interesting. On Saturday, I signed up with Blogger, and am now typing this article into my browser. With a click of the button, it will be added to the MrExcel site. I already answer a few e-mails every day, it should be easy to copy and paste them into the Weblog and create a daily Q&A from the MrExcel mailbag.

Sunday was a slow mail day, so here are a couple questions from Friday:

Ricardo asked is there a way to get the date and time automatically by creating a formula?

This is an easy one - use =NOW() to get the current date and time. Use =TODAY() to get the current date.

Carol asks "I have a column of numbers that I want to add. But I want subtotals for each person who may have more than one number. Is there a way to add each individual person's numbers without selecting each group for each person and manually adding.

Yes, the feature is called Data - Subtotals. You will find the Subtotals command on the Data menu. Select your data, then choose Data - Subtotals. In the Subtotal dialog box, you will answer three questions:

At each change in: Name

Use function: Sum

Add Subtotal to: Amount


For more details and an image about Data Subtotals, read method #4 in Tip 035.

--------
There you have it, weblog entry #3. The downside is that information here will be in a totally random LIFO sequence - one day it may be simple functions, the next day it may be VBA. Add this page to your favorites, check back a every couple of days and let's see if anything interesting develops from this new technology.

Until tomorrow....

From this morning's mailbag:
I want to add years to a given date. For instance I want to add 5 years to 16th Nov.2001. How can I do that?

There are a lot of cool functions for dealing with dates in Excel. One of the most versatile functions is the =DATE() function. It accepts three arguments, Year, Month, and Day. You can, of course have calculations for those arguments and the function is incredibly flexible. For example, as expected:

=DATE(2001,10,14) will return October 14, 2001.

=DATE(2001,10,14+7) will return October 21, 2001

Here are some more complicated situations that Excel handles with ease:
=DATE(2001,10,14+30) will return November 13, 2001

=DATE(2001,10+6,14) will return April 13, 2002

Three other simple functions for dealing with dates are =YEAR() which will return the year from a given date, =MONTH() which will return the month number of a date, and =DAY() which will return the day of the month of a given date. You can combine all four of these functions together to solve today's problem. Assuming that you have a date in cell A1. The following formula is one simple way to come up with a date one year later.

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

Bob wrote with a similar problem. He has a column of dates in column A. Each date is the first of the month. Bob would like a formula that would extend the series. You can change the formula to:

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))


In Bob's case, you would not need a formula. If he has 1-Jan-2001 in A2 and 1-Feb-2001 in A3, he can highlight A2:A3, then click the fill handle in the lower right corner of the selection, drag down, and the series should correctly extend.

Hello Mr Excel readers!

Since I have shown that it is nearly impossible to keep up with the tips of the week, I am going to try something insanely ambitious - a tip of the day! Unlike the Tips of the Week which come with several screen prints, etc. This quickie tip of the day will answer some reader's question that may be of general interest.

Let's reach into the mailbag to see who our lucky first reader will be....

With apologies to Brigitte (question too complicated) and Marie (I never answer questions which are *obviously* typed verbatim from your school homework assignment), the first lucky reader is Judy who asks: "I would like to know how you link dialog box from VB Editor to the worksheet. So when I open Excel, the dialog box that I've created from VB Editor will show up first thing."

OK Judy, here is the answer:
When a workbook is opened in Excel, the Workbook_Open macro is automatically run. This macro has to be located in the code pane associated with This Workbook in the VBA editor. If your dialog box is called frmCust, then this macro will do the trick:

Private Sub Workbook_Open()

    frmCust.Show

End Sub

If you need help finding the code pane associated with ThisWorkbook, then this page will help: http://www.mrexcel.com/tip055.shtml

OK, that was not too painful. The obvious problem here is that when I fire off an answer in an e-mail and it is wrong, then I only appear foolish to one person. This new medium allows me to appear foolish to many people at once. Sounds like fun!