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
|
Public Function CreateHTMLTable$(strRangeReference$, Optional WSName, Optional WBName, Optional HTMFilePath$, Optional HTMFileName$, _
Optional TableName$)
'converts a range to HTML format, retaining all Excel formatting, and sends in an Outlook message
'requires references to Microsoft Outlook Object Library (msoutl9olb)
' and Microsoft Scripting Runtime (SCRRUN.DLL)
'uses TestLen
|
|
Also see "cMatch and rMatch Functions". They can be used to identify column and row numbers to use for filtering, assuming there are no empty column or row headers in the filter range. (Check back soon for code to account for missing column and row headers.)
Filtering is also useful for charts because when rows and columns of data are filtered/hidden, the data series and points they contain are not displayed in charts using that data.
Option Explicit
Option Base 1
Public Const Show = True, Hide =
|
|
These functions facilitate using the extremely useful built-in 'match' function to identify column or row numbers for a supplied header. These functions accomplish the same objective as using named ranges for each column, but in an easier and better way. Header names may also be stored as public variables in your declarations module to prevent typos.
These functions can be used to identify column and row numbers to use for filtering, assuming there are no empty column or row headers in
|
|