|
If you've ever developed a complex spreadsheet that was driven at least in part by event macros, you'll see the usefulness of this code immediately. If not, then you may not want to use SubStart and the related subs (yet), but you may still want to use the SetApp and TestApp functions. "SetApp(All,TurnOn)" is an easy way to reset all Excel application settings, for example.
SubStart may be used only in your main procedures (including some event macros), or it may be used in all of your
|
This will delete all code from one or more vb components (ie, worksheet and workbook objects and vba modules). It could be altered to delete specific procs, but this is useful for such things as deleting one or more modules of code and/or event macros from a file while converting it to an email attachment.
Private Sub DeleteCode(VBComponentNames)
'can use 'ALL' (a global constant, "ALL") for argument to delete code from all components (excel objects and modules)
On Error |
|
Public Function TestLen(inString As Variant) As Boolean
'returns false when len = 0, an input is missing, or an variable is empty, instead of throwing an error
'returns true when input is an error (usually caused by a cell with an error, not an empty cell)
On Error Resume Next
If IsMissing(inString) Then Exit Function
If IsError(inString) Then TestLen = True: Exit Function
If (Len(CStr(inString)) > 0&) Then TestLen =
|
Private Sub FixUsedRanges(SheetNames)
'trim unused rows to speed up and reduce size of file
'uses LastRow and LastCol functions (see here)
'uses myUBound function
Dim tempStr$, i&, j&, iStop&, SheetName$, tempArray, tempInt&
If IsArray(SheetNames) Then
tempArray = SheetNames
iStop = myUBound(tempArray)
|
Private Sub RemoveFormulas(SheetNames, Optional WBName$, Optional HeaderRowNum&)
'remove formulas from all but one row from one or more sheets to make the ss smaller and faster
'uses TestLen, myUbound, and StringReference functions (see here)
'uses LastRow and LastCol functions (see here)
Dim hdr As Range, TempData, i&, iStop&, SheetName$, tempArray, tempStr$
If Not TestLen(WBName) Then WBName =
|
|
Option Explicit
Option Base 1
Option Compare Text
'clipboard functions:
Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long
Public Declare Function CloseClipboard Lib "user32" () As Long
'IsAlphabetic function:
Public Declare Function IsCharAlpha Lib "user32" Alias "IsCharAlphaA" (ByVal cChar As Byte) As Long
'clipboard functions:
Public Function
|
Public Sub HideCols(SheetNames, Optional WBName$, Optional HeaderRowNum&)
'hide columns for a given sheet or array of sheets
'requires headers of hidden columns end with "_"
'uses myUBound function
Dim hdr As Range, i&, j&, iStop&, sName$, tempArray, tempBool As Boolean
If Not TestLen(WBName) Then WBName = ThisWorkbook.Name
If IsArray(SheetNames) Then
|
|
Public Sub HideSheets(bHide As Boolean, Optional CalledByVBA)
'make sure hidden worksheets are hidden, or expose all hidden sheets if hide = false (this is just an example)
Dim ws
'hide or show hidden sheets
For Each ws In wsHidden
ws.Hidden = bHide
Next
'show UI sheets (in case user hid any)
For Each ws In
|
|
Public Function AddToListArray(inArray, inElement, Optional PutAtBeginning As Boolean)
'only for one-dimensional array (usually a list), but could be extended
Dim tempArray(), i%
If PutAtBeginning Then
ReDim tempArray(LBound(inArray) To UBound(inArray))
tempArray(LBound(inArray)) = inElement
For i = LBound(inArray) To
|
|
Public Sub RefreshPivotTables(Optional ByVal WBName$, Optional ByVal WSName$)
Dim ws, pt: If Not TestLen(WBName) Then WBName = ThisWorkbook.Name
If TestLen(WSName) Then
For Each pt In Workbooks(WBName).Sheets(WSName).PivotTables
pt.RefreshTable
Next
|
|
|  | |
|