atlas

Tom’s Tutorials For Excel: Listing Noncontiguous Data Without Blanks

Posted on April 1, 2012 by Tom Urtis

Tom’s Tutorials For Excel: Listing Noncontiguous Data Without Blanks

Here’s a formula solution to listing a noncontiguous list of items while skipping the blank cells.

In the picture, a list of items is in A1:A20, along with blank cells. In cell D1 and copied down as needed is this array formula to re-list what is in column A, skipping the blank cells:
=INDIRECT("A"&SMALL(IF(LEN($A$1:$A$20)=0,"",ROW($1:$20)),ROW(A1)))

Recall, an array formula is applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly applied.

Tagged , , , , , , , , , , , , , , | 1 Comment

Tom’s Tutorials For Excel: Customizing Your RightClick Menu to List & Run Macros

Posted on March 25, 2012 by Tom Urtis

Tom’s Tutorials For Excel: Customizing Your RightClick Menu to List & Run Macros

Download this example of a custom right-click menu to list and run your macros.

I received an intriguing request on Twitter from a follower named Brandi Leath, who asked how to customize the right-click menu to list and run a workbook’s macros. For example, a shortcut to see your macros listed, and to run a macro when you click its name, might look like this:



The first order of business is to do some housekeeping. Open your workbook and establish a reference in the VBE to Microsoft Visual Basic for Applications Extensibility 5.3:

Tools > References > Microsoft Visual Basic for Applications Extensibility 5.3 > OK


Next, you need to set Trusted Access for your VBA projects. The steps to do this in Excel versions 2003 and before are different than the steps in versions 2007 and after. See the following pictures to step you through the process depending on which version of Excel you are using.

Trusted Access to the VBA Project, Versions 2003 and before:
Tools > Macro > Security > Trusted Publishers tab > Trust access to Visual Basic Project > OK


Trusted Access to the VBA Project, Versions 2007 and after:
Developer tab > Macro Security icon > Macro Settings > Trust access to the VBA project… > OK


When building this code, three convenience items were considered:
(1) Place the custom item at the top of the right-click menu, so it is easily seen.
(2) Place a BeginGroup line below the custom item to visually separate it from other menu items.
(3) For user friendliness, show a reminder tip of this functionality when the workbook opens:


Regarding the code itself, the list of macros that would appear in the right-click menu must not include the names of macros that create the custom menu, nor any procedures that may exist in the workbook module, or any modules belonging to a worksheet, UserForm, or class.

In the downloadable workbook example are three macros for demonstration:

Sub Macro1()
MsgBox "This is Macro1.", 64, "Test 1"
End Sub

Private Sub Macro2()
MsgBox "This is Macro2.", 64, "Test 2"
End Sub

Sub Macro3()
MsgBox "This is Macro3.", 64, "Test 3"
End Sub

In the workbook module are these procedures to build and delete the custom menu:

Private Sub Workbook_Open()
MsgBox "You can right-click any worksheet cell" & vbCrLf & _
"to see and / or run your workbook's macros.", 64, "A tip:"
Run "RightClickReset"
Run "MakeMenu"
End Sub

Private Sub Workbook_Activate()
Run "RightClickReset"
Run "MakeMenu"
End Sub

Private Sub Workbook_Deactivate()
Run "RightClickReset"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "RightClickReset"
ThisWorkbook.Save
End Sub

Finally, these macros build the custom right-click menu, list the macros, and identify and run a macro if its name is selected in the custom flyout menu:

Private Sub RightClickReset()
On Error Resume Next
CommandBars("Cell").Controls("Macro List").Delete
Err.Clear
CommandBars("Cell").Reset
End Sub

Private Sub MakeMenu()
Run "RightClickReset"
Dim strMacroName$, strLine$
Dim objCntr As CommandBarControl, objBtn As CommandBarButton
Dim intLine%, intArgumentStart%, objComponent As Object
Set objCntr = _
Application.CommandBars("Cell").Controls.Add(msoControlPopup, before:=1)
objCntr.Caption = "Macro List"
Application.CommandBars("Cell").Controls(2).BeginGroup = True
For Each objComponent In ActiveWorkbook.VBProject.VBComponents
If objComponent.Type = 1 Then
For intLine = 1 To objComponent.CodeModule.CountOfLines
strLine = objComponent.CodeModule.Lines(intLine, 1)
strLine = Trim$(strLine)
If Left$(strLine, 3) = "Sub" Or Left$(strLine, 11) = "Private Sub" Then
intArgumentStart = InStr(strLine, "()")
If intArgumentStart > 0 Then
If Left$(strLine, 3) = "Sub" Then
strMacroName = Trim(Mid$(strLine, 4, intArgumentStart - 4))
Else
strMacroName = Trim(Mid$(strLine, 12, intArgumentStart - 12))
End If
If strMacroName <> "RightClickReset" And strMacroName <> "MakeMenu" Then
If strMacroName <> "MacroChosen" Then
Set objBtn = objCntr.Controls.Add
With objBtn
.Caption = strMacroName
.Style = msoButtonIconAndCaption
.OnAction = "MacroChosen"
.FaceId = 643
End With
End If
End If
End If
End If
Next intLine
End If
Next objComponent
End Sub

Private Sub MacroChosen()
With Application
Run .CommandBars("Cell").Controls(1).Controls(.Caller(1)).Caption
End With
End Sub

The installed code will be functional after you close the workbook and reopen it, or after you activate a different workbook in that same instance of Excel and reactivate this workbook.

Thanks to Brandi Leath for asking the question.
Here again is the link to download the workbook example.

Tagged , , , , , , , , , , , , , , | 10 Comments

Tom’s Tutorials For Excel: LOOKUP Column Header of First Non Zero Cell in a Row

Posted on March 22, 2012 by Tom Urtis

Tom’s Tutorials For Excel: LOOKUP Column Header of First Non Zero Cell in a Row

Here’s an example of identifying the column, and then the value of the header in row 4 of that column, for the first cell in a row that is not a zero.

In cell N5 and copied down as needed is the array formula
=INDIRECT(ADDRESS(4,MATCH(TRUE,B5:M5<>0,0)+1))

The “+1″ in the formula for the MATCH function is to account for the column number in the array being returned, with column A not a part of the array. So getting the grid’s actual column number along with known header row 4 produces the map to finding that intersecting cell’s month label.

Recall, array formulas are entered by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not entered by you. Excel places them automatically when the array formula is properly applied.

Tagged , , , , , , , , , , , , | Leave a comment

Tom’s Tutorials For Excel: Formatting Cells Containing Formulas, Constants, or Nothing

Posted on March 19, 2012 by Tom Urtis

Tom’s Tutorials For Excel: Formatting Cells Containing Formulas, Constants, or Nothing.

With Conditional Formatting, you can color-code cells in real time that contain formulas, or constants, or nothing at all.



Step 1
From your keyboard press Ctrl+F3 to add a new name to your workbook.

Step 2
As shown in the pictures, enter Formulas as a new name.
In the Refers to field, enter the formula =GET.CELL(48,INDIRECT("rc",0)).

The Define Name dialog box in version 2003.


The Name Manager dialog box in version 2010.

Step 3
Select the range of cells on your worksheet to be conditionally formatted for formulas. In this example, the selection started in cell A1.

Step 4
From your keyboard, press Alt+O+D to show the Conditional Formatting dialog.

Step 5
The formula rule for cells containing formulas is =Formulas.
Click the Format button for your desired color for cells with formulas.

The formula rule for cells containing constants is =AND(LEN(A1)>0,ISTEXT("rc")).
Click the Format button for your desired color for cells with constants.

Conditional Formatting dialog in version 2003


Conditional Formatting dialog in version 2010

Tagged , , , , , , , , , , , , , , | Leave a comment

Tom’s Tutorials For Excel: Quickly Summing a Table of Numbers by Rows and Columns

Posted on March 18, 2012 by Tom Urtis

Tom’s Tutorials For Excel: Quickly Summing a Table of Numbers by Rows and Columns

Here are a couple of shortcuts to quickly sum the totals for rows and columns in a data table.

The first pair of pictures shows what you have and what you want.
The second pair of pictures shows the familiar SUM formulas.
The third pair of pictures shows quicker methods using AutoSum or Alt+=.





Tagged , , , , , , , , , , , , , , , | Leave a comment

Tom’s Tutorials For Excel: Listing Unique Items From Multiple Ranges

Posted on March 14, 2012 by Tom Urtis

Tom’s Tutorials For Excel: Listing Unique Items From Multiple Ranges

Here’s a method for looping through possible named ranges, and if they exist, compiling a unique list of items found among all those ranges.

I have the word “possible” here as a design wrinkle to demonstrate an option of convenience. On a sheet you can keep hidden, you can maintain a list of named ranges that truly exist, while including names of ranges that don’t exist yet but which you’re thinking of including in the future. This first picture shows such a sheet, with a simple list of named ranges.



For your worksheet with the actual named ranges, the below macro lists all unique items in column J that were collected from each named range. This next picture is an example of three named ranges that are populated with many repeated items.


Sub ListUniqueItemsFromMultipleRanges()

'Turn off ScreenUpdating.
Application.ScreenUpdating = False

'Declare variables.
Dim RangeCell As Range, cell As Range
Dim xRow As Long, varCell As Variant

'The list will go into column J which is column 10.
'Clear that column to make sure you start with a clean slate.
Columns(10).Clear

'Put a header in cell J1 and bold the cell.
With Range("J1")
.Value = "Unique List"
.Font.Bold = True
End With

'Start the unique list in row 2 of column J.
xRow = 2

'Loop through each range name listed on the zzzNamedRangeList sheet.
'Some names will be valid, others will not be.
'This gives you an easy way to add named ranges to the list,
'to be included in the future, not necessarily today.
For Each RangeCell _
In Sheets("zzzNamedRangeList").Range("A1").CurrentRegion

'Test if the named range actually exists.
On Error Resume Next
If Not ActiveWorkbook.Names(RangeCell.Value) Is Nothing Then
If Err.Number <> 0 Then
Err.Clear

Else

'The named range exists. Loop through each cell.
For Each cell In Range(RangeCell.Value)

If Len(cell.Value) > 0 Then
'Determine of the item in the cell already exists in the
'unique list being compiled in column J.
'If it does not, add it to the next empty row in the list.
varCell = Application.Match(cell.Value, Columns(10), 0)
If IsError(varCell) Then
Err.Clear
Cells(xRow, 10).Value = cell.Value
xRow = xRow + 1
End If
End If
Next cell

End If
End If

Next RangeCell

'Optional, sort the unique list in column J by ascending order.
Range("J1").CurrentRegion.Sort _
Key1:=Range("J2"), Order1:=xlAscending, Header:=xlYes

'Turn on ScreenUpdating.
Application.ScreenUpdating = True
End Sub

Tagged , , , , , , , , , , , , , , | Leave a comment

Tom’s Tutorials For Excel: Summing The First Nth Numbers in a Mixed List

Posted on March 13, 2012 by Tom Urtis

Tom’s Tutorials For Excel: Summing The First Nth Numbers in a Mixed List

Crazy requests can arise when dealing with crazy data. Here you need to sum the first three numbers that appear in a list that contains numbers, text, and empty cells.

The array formula in pink-colored cell E2 is
=SUM(SUM(OFFSET(A1,SMALL(IF(ISNUMBER(A2:A100),ROW(A2:A100)),{1,2,3})-1,)))



Recall, array formulas are entered by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not entered by you; Excel places them automatically when the array formula is properly applied.

Tagged , , , , , , , , , , , , , , | 3 Comments

Tom’s Tutorials For Excel: Couple of Concatenation Conundrums

Posted on March 12, 2012 by Tom Urtis

Tom’s Tutorials For Excel: Couple of Concatenation Conundrums

Here are two examples of concatenating cells for whacky but not unheard-of situations.

The first example is alternating cells in column being concatenated in successive cells.
The formula in cell B4 and copied down to cell B21 is:
=OFFSET(A1,ROW()-1,)&IF(ISBLANK(OFFSET(A1,ROW()-1,)),""," ")
&OFFSET(A2,ROW()-1,)
Note that I broke the single-line formula into two lines here, for browser readability.



The second example is a running concatenation of successive cells into one single cell.
The formula in cell B5 and copied down to cell B13 is
=TRIM(B4&" "&A5&",").

Tagged , , , , , , , , , , , , , | Leave a comment

Tom’s Tutorials For Excel: Why are my numbers adding up wrong?

Posted on March 8, 2012 by Tom Urtis

Tom’s Tutorials For Excel: Why are my numbers adding up wrong?

Have you seen or asked yourself this kind of question before:

Why doesn’t my list of positive and negative numbers sum perfectly to zero like it should?

Setting the option for Precision as Displayed might solve the issue superficially, but getting a true mathematical result of what you want might not be possible, depending on what the actual underlying values are of the numbers you visually see. Formatting a cell has no influence on a cell’s actual value. What looks like 25% (or .25) could really be .2500000000000001 depending on the data you’re working with. Excel supports the calcultaion of numbers up to 15 decimal places and rounds a number after that.

What is happening in this case is not an Excel bug, but rather a computer science issue. Here is some technical background.

Excel stores numeric values as Double Precision Floating Point numbers, or “Doubles” for short. These are 8-byte variables that can store numbers accurately to approximately 15 decimal places. Regardless of formatting, the underlying value is calculated with the full 15 decimal places.

Virtually no computer can store most fractional numbers with total accuracy. Computers use the IEEE (Institute of Electrical and Electronic Engineers) standard for floating point numbers. This standard provides a way to store fractional numbers in the limited space of an 8-byte number. For many decimalized / fractionalized numbers, some approximation must be made. Excel’s internal storage of the number is not affected by the way the number is formatted in the worksheet cell.

Here are two examples of how a correct calculation can look incorrect:

Example 1

If a cell contains the formula =1/3, Excel always treats this value as 0.3333…, regardless of how many decimal places you choose to display on the worksheet. Even if you choose to format the value to appear as “0.3″, Excel still retains the complete number as the value of the cell. This can cause situations in which it may appear that Excel is making an error in calculation, when really it is not. For example, suppose you have the formula =1/3 in each of the three cells A1:A3. Formatting these cells for one decimal point would show “0.3″ in each cell. Adding these three cells together with the SUM function will give the result 1.0 although visually 0.3 + 0.3 + 0.3 equals 0.9. Regardless of how you have the cells formatted for display, Excel uses the underlying value when doing calculations. In the example, you are not really adding 0.3 + 0.3 + 0.3, but rather 0.333333333333333 + 0.333333333333333 + 0.333333333333333, whose sum is almost but not quite 1.0.

Example 2

Enter the following list of numbers in A1:A6
-528.81
-92.54
621.35
-44.39
-7.77
52.16

Now add up those numbers in another cell with the formula =Sum(A1:A6).
The answer you get is 1.13687E-13 which is the same as
0.000000000000113686837721616.
The correct answer should be zero. This is a floating point rounding error — not a bug, not an incorrect result, just the way finite precision digital arithmetic works.

In Excel, this is happening in binary. Just as a computer stores integers as binary numbers, it stores fractional numbers as binary fractions.

Computers store an integer (whole number) value as (x*1 + x*2 + x*4 + x*8 + x*16 etc) where x is the state of the bit. If the bit is on, x=1. If the bit is off, x=0. In this notation, any integer can be stored exactly. For example, the number 13 is stored in binary as 1101 which indicates, reading from left to right:
[1 times 8] plus [1 times 4] plus [0 times 2] plus [1 times 1] equals 13.

Fractional numbers are stored in a similar manner. In the binary system, fractional numbers are stored as the sum of a series of fractions: (x*1/2 + x*1/4 + x*1/8 + x*1/16 and so on). Unlike integers however, not every fractional value can be stored exactly accurately. For example, it is impossible to store the number 1/10 (which is 0.1) in 8-byte or any length binary notation. A close approximation is (0*1/2 + 0*1/4 + 0*1/8 + 1*1/16 + 1*1/32 etc). Computers carry this operation to the equivalent of 15 decimal places. Even with this accuracy, many numbers are represented as an approximation of their true or analytic value. Floating point numbers can come extremely close to representing that number, but there will always be some very small error.

Again, it’s important to note that these limitations on fractional numbers are not really errors at all, nor are they bugs in the programs. These are well-known and well-documented limitations of the floating point arithmetic systems in almost every software package and hardware device, including Excel and the computers Excel is being run on.

A final example:

Create a new workbook in Excel.

In cell A1, enter 67.
In cell A2, enter 89.
In cell A3, enter the formula =A1/A2.

Right-click cell A3 and choose Format Cells.
On the Number tab, in the Category listbox, select Number.
In the Decimal Places spinner, enter the maximum (30), and click OK.
Widen column A as much as necessary to see the number.

Note that cell A3 shows:
0.752808988764045000000000000000

The correct answer would continue infinitely. For example, here are just the first 30 correct significant digits:
0.752808988764044943820224719101

In addition to being unable to return numbers with more than 15 significant digits, Excel is unable to accept operands with more than 15 significant digits. For example:

In cell A4, enter 123456789123456789.
Right-click cell A4 and choose Format Cells.
On the Number tab, in the Category listbox, select Number.
(Optional) In the Decimal Places spinner, enter 0, and click OK.
Widen column A as much as necessary to see the number.

Note that cell A4 shows:
123456789123456000

Excel truncated the number to 123456789123456000 — it didn’t even round it correctly to 123456789123457000.

Where this especially comes into play is that some calculations have literally infinite decimal place values, meaning that the calculation for those numbers would never be 100% accurate arithmetically no matter what calculation medium (computer or otherwise) is used.

The bottom line is that the calculations produced by Excel for your cells are as good as Excel will be able to produce, and VBA (if you were to use that) would be able to programmatically calculate, within the computer environment of digital arithmetic.

Tagged , , , , , , , , , , , , , , , | Leave a comment

Tom’s Tutorials For Excel: Preventing a Workbook From SaveAs

Posted on March 7, 2012 by Tom Urtis

Tom’s Tutorials For Excel: Preventing a Workbook From SaveAs

When you want to keep the name of your workbook the same, without the ability to save it as another name, here’s an example of how to kinda sorta accomplish that.

“Kinda sorta”? Three things in life are certain: death, taxes, and programming code that falls short of perfection. There is nothing VBA can do to stop someone from going into their computer’s directory such as Windows Explorer, and manually changing the workbook’s file name without opening it.

From the Excel interface, there are two common methods to SaveAs, seen in the next two pictures.

Hitting the F12 key on the keyboard.


Clicking the SaveAs item on the toolbar or File menu.


With the below code, using those common interface methods will produce this message:



To install the code, from your keyboard press Alt+F11 which takes you to the Visual Basic Editor. From there, press Ctrl+R to make sure the Project – VBAProject window is visible.

Find your workbook (in this example it is named Book1), right-click on ThisWorkbook and left-click to select View Codeas shown in the next picture.

Paste the below BeforeSave event procedure into the workbook module as shown.

When you are done, press Alt+Q to close the VBE and return to your worksheet. Save the workbook. Test the code by attempting to SaveAs the workbook, and you’ll see the Message Box that is pictured above.

As a precautionary measure, lock the VBE with a password if you don’t want other users to wander into the VBE and change the code.




Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Select Case True

Case SaveAsUI
MsgBox "Sorry, you may not ''Save as'' this workbook." _
& vbCrLf & vbCrLf & _
"You may only save it, keeping its original name.", _
vbExclamation, "''Save as'' not allowed."
Cancel = True

Case Else
Exit Sub

End Select
End Sub
Tagged , , , , , , , , , , , , , | Leave a comment