Monthly Archives: December 2019

VBA

A cell value in a message box

You have a very large Excel sheet. At the end of rows and columns are SUM formulas.

Cell AO42 contain the total sum.

Whenever you change any cell within range, the value in AO42 will change. To see the value in AP42, you should each time to scroll right and down to see the value.

You change the cell value in H8, but you would like to know immediately how much is the grand total in AO42. One option is to scroll down right to AO42. If you need to constantly fine tune numbers, it is time consuming to scroll right and down after each change.

However, if you have a macro to bring the value in message box.

Like this. When you assign a shortcut key to macro, you can check the grand total by pressing shortcut key.

VBA:

Sub z_tot()

Dim tul

On Error GoTo Fehler

tul = Round(Range(“ao42”).Value, 2)

MsgBox “The total sum is: ” & tul & “.”, _

vbOKOnly, “Own macro”

Exit Sub

Fehler:

    MsgBox “Error somewhere.”, vbOKOnly, ” Own macro”

    Exit Sub

End Sub

On error statement tracks whether the cell does not contain any reasonable value. If the function in AO42 does not return any value, the ROUND cannot round and VBA hits an error. With on error, the macro just brings a message box.

In case you want to see the row or column total instead of the grand total, you should scroll downwards or to right to see the row or the column total.

The macros below return the row and column totals based on the active cell. The active cell means the cell where the cursor is. If you have changed a cell and you would like to track the column or row total, do not select another cell, but keep the cursor in the cell where you made the change.

Sub z_col_tot()

Dim tul

Dim zr

Dim zv

Dim zs

zs = ActiveCell.Address

zr = ActiveCell.Row

ActiveCell.Offset((42 – zr), 0).Select

zv = ActiveCell.Address

Range(zs).Select

On Error GoTo Fehler

tul = Round(Range(zv).Value, 2)

MsgBox “The total sum for column is: ” & tul & “.”, _

vbOKOnly, “Own macro”

Exit Sub

Fehler:

    MsgBox “Error somewhere.”, vbOKOnly, ” Own macro”

    Exit Sub

End Sub

To see the row total

Sub z_row_tot()

Dim tul

Dim zc

Dim zv

Dim zs

zs = ActiveCell.Address

zc = ActiveCell.Column

ActiveCell.Offset(0, (41 – zc)).Select

zv = ActiveCell.Address

Range(zs).Select

On Error GoTo Fehler

tul = Round(Range(zv).Value, 2)

MsgBox “The total sum for row is: ” & tul & “.”, _

vbOKOnly, “Own macro”

Exit Sub

Fehler:

    MsgBox “Error somewhere.”, vbOKOnly, ” Own macro”

    Exit Sub

End Sub

Functions

Extra space

To have extra spaces in Excel cell is quite common issue. Especially when data is downloaded from another system to Excel, some additional digits can be found in Excel.

I have had issues with leading spaces. Here are some hints how I have tackled the space issues.

First thing is to check how many digits the cell contains as the initial spaces are not visible. The function LEN is for this purpose.

The formulas, which are worth trying first, are TRIM and CLEAN. SUBSTITUTE is to change a specific character to another. If you want to use REPLACE, you need to know which digit, like the first one, is space.

One option to delete leading space or two leading spaces, is to write IF sentence. If the first two digits are spaces, then take digits from the 3rd onward. Number of digits to be captured is the total length of the cell minus two. If cell contains only one leading space, then Excel should capture digits starting from the second one, and count the last digit by taking the total length minus one. If neither of IF statements, the first two digits are spaces or the first digit is space, is true, then IF is returning the cell value unmodified.

=IF(LEFT(C18;2)=”  “;MID(C18;3;(LEN(C18)-2));IF(LEFT(C18;1)=” “;MID(C18;2;(LEN(C18)-1));C18))

The issue with space is that space is not in fact a space. Each digit is Excel has a CHAR number. Like capital A is char(65). Function CODE returns the char number.

In Excel there is char(160), which is like space but not exactly space. Space + abc is not similar to char(160) + abc. Similar formulas with work with the first case might not work with the second one.

The best solution so far I have found with extra spaces have been nested function to link several function and hopefully some of the works. When you create a customized formula, you don’t have to write whole chain every time from the start till end.

Function z_space2(z_cell)

z_space2 = WorksheetFunction.Trim(WorksheetFunction.Clean(WorksheetFunction.Substitute(z_cell, Chr(160), ” “)))

End Function