Disable Excel events

How to accelerate the refresh of data connections in a large Excel workbook which has macros triggered by events: disable the events before refreshing the connections, then re-enable the events.

Sub refresh_data()
'disables events, updates data connections, then enables events.

Application.EnableEvents = False
'code to execute with events disabled:
ActiveWorkbook.RefreshAll
Application.EnableEvents = True

End Sub

Faster iterative calculation of a worksheet with circular references

Sub fastcalc()

  'disable screen updating:
  Application.ScreenUpdating = False

  For i = 1 To 100
    Selection.Calculate 'recalculate only the selected range
  Next i

  'enable screen updating:
  Application.ScreenUpdating = True

End Sub

When text cells don't act like text cells

I've got a table of data in which I want one column to contain only text values of numbers. For example, I want values like 4700, 33, 2.7, 0.01, etc., to be represented not as numbers but as text. This is desired because I use a macro that expects to find text values in that column.

So I formatted the whole column of data as text, but found that the macro still didn't see the values as text.

Turns out that this is because once the numbers had been entered into the cells, that caused the cells to be set to numeric format, and this format was not affected by my subsequent attempt to set the cells' format to text.

To get around this, I cleared the cells, set their format to text, then entered the values.

Engineering notation

Scientific notation examples:

100:	1E2
1000:	1E3
10,000:	1E4
100,000:	1E5
1,000,000:	1E6
10,000,000:	1E7

Engineering notation examples:

100:	100
1000:	1E3
10,000:	10E3
100,000:	100E3
1,000,000:	1E6
10,000,000:	10E6

This custom cell format provides engineering notation with a variable number of decimal places:
##0.#E+0

This custom cell format provides engineering notation with 2 decimal places:
##0.00E+0

Purpose of this blog

...is to collect and present solutions, tips, and notes of interest regarding Microsoft Excel. Some of the material may be original, some borrowed (with references given).

If You have questions or contributions, feel free to leave a concise and constructive comment.