Databases Reference
In-Depth Information
Dates are problematic because there are so many different ways to express them. Is a four-digit
or two-digit year desired? The following subroutine allows a column to be set as the date type to
a format specified by the developer.
Sub FormatDate(ByVal wkbook, ByVal wksht, ByVal Col, ByVal
ddmmyy As String)
'Macro Sets a column to Type Date with custom format
Workbooks(wkbook).Worksheets(wksht).Columns(Col).Select
Selection.NumberFormat = ddmmyy
Columns(Col).EntireColumn.AutoFit
End Sub
There are many different kinds of formats that can be applied to cells, rows, and columns in
Excel. Once a type of format has been set up on one cell, it can be a real time-saver to copy the
format to another cell or range of cells. The next subroutine allows the developer to do this.
Sub CopyCellFormat(fromstartRow, fromstartCol, fromendRow,
fromendCol, fromSheet, _
fromBook, tostartRow, tostartCol, toendRow, toendCol,
toSheet, toBook)
'Copy Formats Only from One Range to Another Range
Workbooks(fromBook).Worksheets(fromSheet).Select
Workbooks(fromBook).Worksheets(fromSheet).range(Cells(fromstar
tRow, fromstartCol), Cells(fromstartRow, fromstartCol)).Copy
Workbooks(toBook).Worksheets(toSheet).Select
Workbooks(toBook).Worksheets(toSheet).range(Cells(tostartRow,
tostartCol), Cells(toendRow, toendCol)).PasteSpecial
Paste:=xlFormats
End Sub
Whenever reporting values, it is a good idea to set the precision for the numbers being reported.
If a value can only be accurately calculated to two decimal places, it is meaningless (and misleading)
to display a number with eight decimal places. The following two subroutines allow the precision
to be set for both a cell and a column.
Sub SetColPrecision(ByVal wkbook, ByVal wksht, ByVal ColNumber,
ByVal decplaces)
'Set the number of Decimal Places for a particular Column in
a Worksheet
Dim numforstr As String, ii As Integer
ActivateWorkbook (wkbook)
Sheets(wksht).Activate
Columns(ColNumber).Select
'This used to work?
'Selection.NumberFormat = decplaces
numforstr = "0."
For ii = 1 To decplaces
numforstr = numforstr & "0"
Next ii
