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
April 6, 2010
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.
March 9, 2010
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
January 12, 2010
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.
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.
January 11, 2010
Engineering notation
Scientific notation examples:
Engineering notation examples:
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
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.
If You have questions or contributions, feel free to leave a concise and constructive comment.
Subscribe to:
Posts (Atom)