Mod_Cells.bas

Attribute VB_Name = "Mod_Cells"
Option Explicit
Option Base 1
Function Cells_ReturnNumberOrLetters(ByVal rangeCell As Range, ByVal intReturnType As Integer) As Variant
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' this function returns a number or letter(s) from a cell
'
' Requirements:
' none
'
' Inputs:
' rangeCell
' Type: range object
' Desc: the cell to return the column
'
' intReturnType
' Type: integer
' Desc: the return type numbers or letter(s)
' 1 -> column number
' 2 -> column letter(s)
' 3 -> address (no absolutes for row or column)
' 4 -> row number
'
' Important Info:
' None
'
' Return:
' variable
' Type: variant
' Desc: the number of the column or the letters of the column or the letter of the row
' intReturnType = 1 -> long; column number
' intReturnType = 2 -> string; column letter
' intReturnType = 3 -> string; cell address
' intReturnType = 4 -> long; row letter
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'
' declare variables
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''

Dim intPosit As Integer
Dim stringCellAddress As String
Dim variantReturnValue As Variant
Const boolError As Boolean = False

' loop
Dim a As Long, b As Long, c As Long

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'
' set objects
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'
' initialize variables
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''

intPosit = 0
stringCellAddress = "tsma"

' loop
a = 1
b = 1
c = 1

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'
' begin
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''

Select Case intReturnType
    Case 1: ' column #
        variantReturnValue = rangeCell.Column
    Case 2: ' column letter
        stringCellAddress = rangeCell.Address(True, False)
        intPosit = InStr(1, stringCellAddress, "$")
        variantReturnValue = Left(stringCellAddress, intPosit - 1)
    Case 3: ' cell address
        variantReturnValue = rangeCell.Address(False, False)
    Case 4: ' row #
        variantReturnValue = rangeCell.Row
    Case Else ' do nothing
End Select

' error handling example
'On error goto <error ID Label>:
'If …. Then
'
'Else
'<resume code label>:
' on error goto 0
'End if

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'
' error handling
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''

If boolError = True Then
'<error ID label>:
'    error handling code
'    Resume <resume code label>: ' goto <resume code label> to resume code
Else ' do nothing
End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'
' end
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''

' return value
Cells_ReturnNumberOrLetters = variantReturnValue

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'
' reset objects
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''’’'''
End Function
Sub Cells_Format(wks_worksheet As Worksheet, Optional bool_clear_contents As Boolean = False)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' this subroutine formats the worksheet to a generic format
'
' Inputs
' wks_worksheet
' Type: worksheet object
' Desc: the worksheet to be formatted
'
' Return
' Type: None
' Desc: None
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' declare

' loop variables

' initialize

' start
With wks_worksheet.Cells
    If bool_clear_contents = True Then .ClearContents Else ' do nothing
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .IndentLevel = 0
    .ShrinkToFit = False
    .MergeCells = False
    .Font.Bold = False
    .Font.Italic = False
    .Font.Name = "Arial"
    .Font.Size = 12
    .Font.Color = RGB(0, 0, 0)
    .Font.Strikethrough = False
    .Font.Subscript = False
    .Font.Superscript = False
    .Font.Underline = False
    .Interior.ColorIndex = xlColorIndexNone
    .EntireColumn.Hidden = False
    .NumberFormat = xlGeneral
End With
End Sub
Function Cells_IsString(rangeCell As Range) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' this function returns True or False of the cell passed to the function is a string, false if not a string
' if the cell value is not empty, not a number, does not contain an error and not a date then it is
' assumed to be a string
'
' requirements:
' none
'
' Inputs:
' rangeCell
' Type: range
' Desc: this is one cell to test if the value is a string
'
' Important Info:
' none
'
' Return:
' boolIsString
' Type: boolean
' Desc: True if string/ False if not a string
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
' declare
Dim boolIsString As Boolean, boolNotEmpty As Boolean, boolNotNumber As Boolean, boolNotError As Boolean
Dim boolNotDate As Boolean
 
' initialize
boolIsString = False
boolNotEmpty = False
boolNotNumber = False
boolNotError = False
boolNotDate = False
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' start
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
' check if cell is empty
If IsEmpty(rangeCell) = False Then boolNotEmpty = True Else ' do nothing
 
' check if cell is a number
If IsNumeric(rangeCell.Value) = False Then boolNotNumber = True Else ' do nothing
 
' check if cell has an error
If IsError(rangeCell.Value) = False Then boolNotError = True Else ' do nothing
 
' check if cell has an error
If IsDate(rangeCell.Value) = False Then boolNotDate = True Else ' do nothing
 
' logic check
If boolNotEmpty = True And boolNotNumber = True And boolNotError = True And boolNotDate = True Then
    boolIsString = True
Else ' do nothing
End If
 
' return value
Cells_IsString = boolIsString

End Function