|
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
|
|
Storing worksheets and other objects in public variables is a great way to speed up event macros, subs called by your application's users, and even some worksheet UDFs (user-defined functions). That's because public variables are persistent even when code stops executing (as long as you don't use the "End" statement). This is not totally reliable, but either all public variables will remain set or none will, so testing whether gvTest remains set is sufficient. I call the PopGVs sub with
|
This function returns ranges for header rows, data table, etc., without knowing the full name of the named range it's searching for. It supports multiple RowHeader, ColumnHeader, and TableData ranges on a single sheet by identifying the desired range via ColNum or RowNum (or both in the case of TableData ranges).
For example, you might have two tables and name the header row of one 'RowHeader1' and the header of the other 'RowHeader1'. To write one event macro that will manipulate either
|
|
|  | |
|