Tag Archives: Excel

  1. Pages:
  2. 1
  3. 2

VBA Snippet – GetTimezoneOffset()

Posted on at 6:00 PM by
Reply

One of the things I think is amazing about VBA is the fact that it is so incomplete in some ways. For instance, did you know that there is no native way to get the local timezone offset. Yesterday I was working on a VBA SOAP plugin for Excel and realized this horrible fact. Of course, my first instinct was to Google the answer. Unfortunately, not even Google could provide a satisfactory (meaning short) solution. Therefore, I came up with the following VBA function:

Public Function GetTimezoneOffset() As Integer
  Dim ie As New InternetExplorer
  ie.Visible = False
  ie.Navigate "about:blank"
  While ie.readyState <> READYSTATE_COMPLETE And Not ie.Busy
  Wend
  ie.Document.parentWindow.execScript "n = (new Date).getTimezoneOffset()"
  GetTimezoneOffset = ie.Document.parentWindow.n
  ie.Quit
End Function

Of course, there are downsides to this solution. The first (being minor) is the fact that you have to include a reference by going to Tools > References… and then selecting “Microsoft Internet Controls”. The second is the fact that every time this function is called, an instance of Internet Explorer is created and then quickly (speed depending on the computer) destroyed. Other than that, the function will work. Its purpose is to return the amount of minutes behind GMT (or UTC) time the computer is. This is the equivalent of doing the following in JavaScript:

var offset = (new Date).getTimezoneOffset();

Quick Excel Tip – Quickly AutoFit Columns & Rows

Posted on at 6:00 PM by
Reply
  1. Click on the top-left rectangle above the row numbers and to the left of the column names so that all of the cells in the worksheet will be selected.
  2. Move the mouse over the line between two column names (column headers) and double-click it. This will AutoFit all of the columns in the worksheet.
  3. Move the mouse over the line between two row numbers (row headers) and double-click it. This will AutoFit all of the rows in the worksheet.

Quick Excel Tip – Quickly Select Cells In A Range

Posted on at 6:00 PM by
Reply

One of the things that I still find funny is when I ask a user to select all of the values in a column (not the column itself) and then user starts from the top and drags the selection until they reach the end. Of course, for a couple hundred rows, this doesn’t take too long, but if you have thousands or tens of thousands, this can take a while. Therefore a quick tip to select a large range of non-empty cells in a column, you can do the following:

  1. Select the top-most cell in the desired range.
  2. Hold down CTRL (or if using a Mac COMMAND) and SHIFT on the keyboard and then press the down arrow key. This will select all of the cells in the range as long as there are no empty cells in the range.

If the range does contain empty cells, instead of dragging the selection from the top to the bottom, you can do the following:

  1. Select the top-most cell in the desired range.
  2. Scroll down to the end of range (which is usually the end of the sheet) by using the scroll bar.
  3. Press SHIFT and then select the last cell in the range.

If you know the address of the last cell, you can do the following:

  1. Select the top-most cell in the desired range.
  2. Type the address of the final cell of the range into the address box and press SHIFT + ENTER.

If you know the addresses of both the top-left and the bottom-right cells, you can enter those address delimited by a colon into the address box and then press enter. For example, entering A2:D8192 into the address box will select the cells from A2 to D8192.

There are probably other cool tricks as well, so if you have any to share with the world, you can post them in a comment. If your’s is good enough, I will definitely expand this article to include it and then give you credit. ;)

Excel – Coalesce Function?

Posted on at 6:00 PM by
1

Unfortunately, after googling for a while, I was unable to find a COALESCE function or something similar that natively exists within Excel. Therefore, I decided to write a quick-and-dirty formula that would approximate it. I used the following array formula to pull the first non-blank value from cells C2 to C10 in a worksheet:
=INDEX($C$2:$C$10,MATCH(FALSE,ISBLANK($C$2:$C$10),FALSE))

As you can see, I used three Excel functions get my desired result. It would be nice to have less, but you can’t expect everything from this poor man’s database. :D Anyway, the ISBLANK function does exactly what it sounds like it does: it indicates whether or not the specified cell is blank. Since this is an array formula, ISBLANK($C$2:$C10) evaluates to an array of 9 TRUE/FALSE values. The MATCH function searches for the first parameter in the specified array (the second parameter) and evaluates to the position of the first match. The reason I supplied FALSE as a third parameter was to make sure that if every value was blank, a #N/A error would be returned. The INDEX function takes an array as the first parameter, and the position in the array to evaluate to as the second parameter.

In order to make the the formula evaluate as an array formula, you must use the CTRL + ENTER key combination to instead of simply pressing ENTER after inputting the formula. If you are using a Mac, the key combination is COMMAND + ENTER.

In conclusion, this is not the optimal solution, but it works and it doesn’t require a UDF (AKA macro).

Concatenating A Range In Excel

Posted on at 6:00 PM by
Reply

One of the things that Excel is missing is a way of joining a range of cell values with a common delimiter such as a comma. Here is an example of a range of cells that would be nice to be able to join together with a comma:

A B
1 This
2 is
3 a
4 strange
5 and
6 weird
7 sentence.

In order to join all of the values in the range A1 to A7 without a UDF (user-defined function) you can do the following:

  1. Enter =$A$1 into cell B1.
  2. Enter =CONCATENATE($B1," ",$A2) into cell B2.
  3. Re-select cell B2 and then use the CTRL + C key combination to copy it.
  4. Select cells B3 to B7 and then use CTRL + V key combination to paste the formula into those cells.
A B
1 This This
2 is This is
3 a This is a
4 strange This is a strange
5 and This is a strange and
6 weird This is a strange and weird
7 sentence. This is a strange and weird sentence.

Voilà! Now your desired concatenation of cells A1 to A5 can be seen in cell B5. It is important to note that this could have been done with much more than just five cells as well. If it bothers you having cells B1 to B4 filled with progressive listings of the values in column A, you can do the following:

  1. Select and copy cell B5.
  2. Select the cell into which you would like to paste the value.
  3. Go to the menu and select Edit and then Paste Special….
  4. In the dialog box, choose the paste Values option and then click OK.
  5. Clear the cells that you no longer want to be filled.
  1. Pages:
  2. 1
  3. 2