This is not code you'll use often, and you definitely won't find it anywhere else, but if you've ever wanted to use an Excel chart in Word with the ability to edit the chart text, this is the best way to do it. Suppose for example that you or someone else has created a chart that has a figure number below the chart title, but you won't know what figure number to use until the entire Word document is completed.
The same approach works for PowerPoint too, but it's much simpler to implement. Just |
Excel and programming aren't all about crunching numbers and making money (for your company). Sometimes the best way to learn is to try something amusing. Paste this code into the ThisWorkbook object of a blank workbook, save and close the workbook, and then re-open it or send it to someone else. You can also make it your own by changing the behavior. For example, try changing the way colors are assigned, or try a normally distributed scalar in place of the rnd() function, or make the code |
|
This is to overcome a bug in Excel that prevents the .calculate method from working while code is running. If this seems expensive for the CPU, it's negligible relative to calc'ing a large complex wb or ws unncessarily.
Note this code assumes formulas are line-item style – meaning they don't reference other rows on the sheet. If your formulas don't conform to that style, then you will need to determine the cells that calculate last in a workbook or on a given sheet. You could use code to
|
|
Public Function RememberCursorLocation() As Boolean
On Error Resume Next
Selection.Collapse Direction:=wdCollapseStart
If ActiveDocument.Bookmarks.Exists("CursorLocation") Then ActiveDocument.Bookmarks("CursorLocation").DELETE
ActiveDocument.Bookmarks.Add name:="CursorLocation", Range:=Selection.Range
End Function
Public Function ReturnToCursorLocation() As Boolean
If
|
One of the main culprits behind workbook bloat occurs when Excel thinks a sheet's used range is bigger than it actually is. This code will test each sheet of the active workbook and either fix the problem automatically or simply alert you to it. It can be adapted for automation as well, but be aware that the code as it appears below ignores cell formatting when determining the number of rows in use. It also assumes your used range starts in the first row, but if that's not true it will not |
This particular code was written for use in an email attachment that was essentially part of the UI for a larger application, but it can also be used for a main UI. Externally storing settings for a main UI makes it possible for mutliple users to use the same file or copies of the same file to ease development. Some developers prefer to store settings in the registry, but having had bad experiences with registries, I prefer to store settings in a workbook located in a hidden folder.
This goes in |
|