Option Compare Database
Option Explicit
Option Base 1
'this code is for exporting Access databases to text files (which are compatible with version control software, such as MS SourceSafe)
' and for rebuilding the database from said text files
'converting to and rebuilding from text files is an important way of identifying, preventing, and repairing corruption
'this code will export everything needed to fully rebuild files,
' including references, linked tables, as well as all standard Access objects
Read more...
Add comment
|
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 |
This example of the code was for a shared comment history used in mutliple workbooks – also see "Requesting, Storing, And Retrieving User Settings" under the User Interfaces category of this site.
Option Explicit
Option Base 1
'the code in this module requires a reference to zaksCollection.dll
'the dll can be found here: http://www.zaks.demon.co.uk/code/cpts/coll/
Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" (ByVal nBufferLength&, ByVal lpBuffer$) As Long
Private
|
Most Excel programmers will at least occasionally need to automate something in Word, Outlook, PowerPoint, or Project as part of their Excel applications. This post addresses all of the standard components of MS Office that include VBA (there is no VBA for Visio). I suggest you read all sections of this post even if you're currently only interested in one of the applications, because there are comments in some sections that apply to other sections as well.
Access
Access is by far the |
|
This is useful for hiding the VBE after code stops (do to a "Stop" statement, because the user interrupted the code, or because of an error).
Public gvTestVBEAccess As Boolean
Public Function HideVBE()
On Error Resume Next
If fTestVBEAccess Then Application.VBE.MainWindow.Visible = False
End Function
Private Function fTestVBEAccess() As Boolean
'returns true if the project isn't locked
'uses gvTestVBEAccess public boolean for sake of speed
|
|