VBA Snippets
Sample_RegionalBreakdownByDay
Public Sub Sample_RegionalBreakdownByDay(ByVal objCell As Excel.Range, _
Optional ByVal dbLowestValue As Double = 0, _
Optional ByVal dbHighestValue As Double = 100, _
Optional ByVal iNoOfDecimals As Integer = 0)
Dim lrowcount As Long
Call Cell_OffsetFormat(objCell, 0, 0, "Regional Breakdown by Day", True, xlHAlignLeft)
Call Cell_OffsetFormat(objCell, 1, 1, "Region 1", False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 1, 2, "Region 2", False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 1, 3, "Region 3", False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 1, 4, "Region 4", False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 2, 0, "Monday", False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 3, 0, "Tuesday", False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 4, 0, "Wednesday", False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 5, 0, "Thursday", False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 6, 0, "Friday", False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 7, 0, "Saturday", False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 8, 0, "Sunday", False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetInsertRandomNumbers(objCell.Offset(2, 1), 6, 3, dbLowestValue, dbHighestValue, iNoOfDecimals)
End Sub
Sample_RegionalBreakdownByYear
Public Sub Sample_RegionalBreakdownByYear(ByVal objCell As Excel.Range, _
Optional ByVal dbLowestValue As Double = 0, _
Optional ByVal dbHighestValue As Double = 100, _
Optional ByVal iNoOfDecimals As Integer = 0)
Call Cell_OffsetFormat(objCell, 0, 0, "Regional Breakdown of Downloads by Year", True, xlHAlignLeft)
Call Cell_OffsetFormat(objCell, 1, 1, "Region 1", False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 1, 2, "Region 2", False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 1, 3, "Region 3", False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 1, 4, "Region 4", False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 2, 0, VBA.Year(VBA.Now) - 1, False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 3, 0, VBA.Year(VBA.Now) - 2, False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 4, 0, VBA.Year(VBA.Now) - 3, False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 5, 0, VBA.Year(VBA.Now) - 4, False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetInsertRandomNumbers(objCell.Offset(2, 1), 3, 3, dbLowestValue, dbHighestValue, iNoOfDecimals)
End Sub
Sample_RegionalBreakdownInJanuary
Public Sub Sample_RegionalBreakdownInJanuary(ByVal objCell As Excel.Range, _
Optional ByVal dbLowestValue As Double = 0, _
Optional ByVal dbHighestValue As Double = 100, _
Optional ByVal iNoOfDecimals As Integer = 0)
Dim lrowcount As Long
Call Cell_OffsetFormat(objCell, 0, 0, "Regional Breakdown of Downloads in January", True, xlHAlignLeft)
Call Cell_OffsetFormat(objCell, 1, 1, "Region 1", False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 1, 2, "Region 2", False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 1, 3, "Region 3", False, Excel.XlHAlign.xlHAlignRight)
Call Cell_OffsetFormat(objCell, 1, 4, "Region 4", False, Excel.XlHAlign.xlHAlignRight)
For lrowcount = 1 To 31
Call Cell_OffsetFormat(objCell, 1 + lrowcount, 0, VBA.CDate(VBA.DateAdd("d", lrowcount - 1, "01/01/2012")), _
False, xlHAlignRight, "dd-mmm")
Next lrowcount
Call Cell_OffsetInsertRandomNumbers(objCell.Offset(2, 1), 30, 3, dbLowestValue, dbHighestValue, iNoOfDecimals)
End Sub
Sample_SalesByCountryHorizontal
Public Sub Sample_SalesByCountryHorizontal(ByVal objCell As Excel.Range, _
Optional ByVal dbLowestValue As Double = 0, _
Optional ByVal dbHighestValue As Double = 100, _
Optional ByVal iNoOfDecimals As Integer = 0)
Call Cell_OffsetFormat(objCell, 0, 0, "Sales by Country (horizontal)", True, xlHAlignLeft)
Call Cell_OffsetFormat(objCell, 1, 0, "France", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 1, 1, "Germany", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 1, 2, "Japan", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 1, 3, "UK", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 1, 4, "Australia", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 1, 5, "Other", False, xlHAlignRight)
Call Cell_OffsetInsertRandomNumbers(objCell.Offset(2, 0), 0, 5, dbLowestValue, dbHighestValue, iNoOfDecimals)
End Sub
Sample_SalesByCountryVertical
Public Sub Sample_SalesByCountryVertical(ByVal objCell As Excel.Range, _
Optional ByVal dbLowestValue As Double = 0, _
Optional ByVal dbHighestValue As Double = 100, _
Optional ByVal iNoOfDecimals As Integer = 0)
Call Cell_OffsetFormat(objCell, 0, 0, "Sales by Country (vertical)", True, xlHAlignLeft)
Call Cell_OffsetFormat(objCell, 1, 0, "France", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 2, 0, "Germany", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 3, 0, "Japan", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 4, 0, "UK", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 5, 0, "Australia", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 6, 0, "Other", False, xlHAlignRight)
Call Cell_OffsetInsertRandomNumbers(objCell.Offset(1, 1), 5, 0, dbLowestValue, dbHighestValue, iNoOfDecimals)
End Sub
Sample_SalesByYearAndMonthNumbers
Public Sub Sample_SalesByYearAndMonth(ByVal objCell As Excel.Range, _
Optional ByVal dbLowestValue As Double = 0, _
Optional ByVal dbHighestValue As Double = 100, _
Optional ByVal iNoOfDecimals As Integer = 0)
Call Cell_OffsetFormat(objCell, 0, 0, "Sales by Year and Month", True, xlHAlignLeft)
Call Cell_OffsetFormat(objCell, 1, 1, VBA.Year(VBA.Now) - 1, False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 1, 2, VBA.Year(VBA.Now) - 2, False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 1, 3, VBA.Year(VBA.Now) - 3, False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 1, 4, VBA.Year(VBA.Now) - 4, False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 2, 0, "January", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 3, 0, "February", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 4, 0, "March", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 5, 0, "April", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 6, 0, "May", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 7, 0, "June", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 8, 0, "July", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 9, 0, "August", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 10, 0, "September", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 11, 0, "October", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 12, 0, "November", False, xlHAlignRight)
Call Cell_OffsetFormat(objCell, 13, 0, "December", False, xlHAlignRight)
Call Cell_OffsetInsertRandomNumbers(objCell.Offset(2, 1), 11, 3, dbLowestValue, dbHighestValue, iNoOfDecimals)
End Sub
Sample_SalesByYearAndMonthText
vba
Sample_SalesOnFirstDayOfEveryMonth
Public Sub Sample_NumberOfDownloadsOnFirstDayOfEveryMonth(ByVal objCell As Excel.Range, _
Optional ByVal dbLowestValue As Double = 0, _
Optional ByVal dbHighestValue As Double = 100, _
Optional ByVal iNoOfDecimals As Integer = 0)
Call Cell_OffsetFormat(objCell, 0, 0, "Number of Downloads on the First Day of Every Month", True, xlHAlignLeft)
Call Cell_OffsetFormat(objCell, 1, 0, VBA.DateSerial(VBA.Year(VBA.Now) - 1, 1, 1), False, xlHAlignRight, "dd-mmm")
Call Cell_OffsetFormat(objCell, 2, 0, VBA.DateSerial(VBA.Year(VBA.Now) - 1, 2, 1), False, xlHAlignRight, "dd-mmm")
Call Cell_OffsetFormat(objCell, 3, 0, VBA.DateSerial(VBA.Year(VBA.Now) - 1, 3, 1), False, xlHAlignRight, "dd-mmm")
Call Cell_OffsetFormat(objCell, 4, 0, VBA.DateSerial(VBA.Year(VBA.Now) - 1, 4, 1), False, xlHAlignRight, "dd-mmm")
Call Cell_OffsetFormat(objCell, 5, 0, VBA.DateSerial(VBA.Year(VBA.Now) - 1, 5, 1), False, xlHAlignRight, "dd-mmm")
Call Cell_OffsetFormat(objCell, 6, 0, VBA.DateSerial(VBA.Year(VBA.Now) - 1, 6, 1), False, xlHAlignRight, "dd-mmm")
Call Cell_OffsetFormat(objCell, 7, 0, VBA.DateSerial(VBA.Year(VBA.Now) - 1, 7, 1), False, xlHAlignRight, "dd-mmm")
Call Cell_OffsetFormat(objCell, 8, 0, VBA.DateSerial(VBA.Year(VBA.Now) - 1, 8, 1), False, xlHAlignRight, "dd-mmm")
Call Cell_OffsetFormat(objCell, 9, 0, VBA.DateSerial(VBA.Year(VBA.Now) - 1, 9, 1), False, xlHAlignRight, "dd-mmm")
Call Cell_OffsetFormat(objCell, 10, 0, VBA.DateSerial(VBA.Year(VBA.Now) - 1, 10, 1), False, xlHAlignRight, "dd-mmm")
Call Cell_OffsetFormat(objCell, 11, 0, VBA.DateSerial(VBA.Year(VBA.Now) - 1, 11, 1), False, xlHAlignRight, "dd-mmm")
Call Cell_OffsetFormat(objCell, 12, 0, VBA.DateSerial(VBA.Year(VBA.Now) - 1, 12, 1), False, xlHAlignRight, "dd-mmm")
Call Cell_OffsetInsertRandomNumbers(objCell.Offset(1, 1), 11, 0, dbLowestValue, dbHighestValue, iNoOfDecimals)
End Sub
Sample_SalesOnTwentyRandomDays
Public Sub Sample_NumberOfDownloadsOnTwentyRandomDays(ByVal objCell As Excel.Range, _
Optional ByVal dbLowestValue As Double = 0, _
Optional ByVal dbHighestValue As Double = 100, _
Optional ByVal iNoOfDecimals As Integer = 0)
Dim irandomnumber As Integer
Dim ldateserial As Long
Call Cell_OffsetFormat(objCell, 0, 0, "Number of Downloads on Twenty Random Days in a Year", True, xlHAlignLeft)
irandomnumber = 0
irandomnumber = Number_Random(irandomnumber, 18, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 1, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
irandomnumber = Number_Random(irandomnumber + 3, 36, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 2, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
irandomnumber = Number_Random(irandomnumber + 3, 54, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 3, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
irandomnumber = Number_Random(irandomnumber + 3, 72, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 4, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
irandomnumber = Number_Random(irandomnumber + 3, 90, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 5, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
irandomnumber = Number_Random(irandomnumber + 3, 108, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 6, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
irandomnumber = Number_Random(irandomnumber + 3, 126, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 7, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
irandomnumber = Number_Random(irandomnumber + 3, 144, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 8, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
irandomnumber = Number_Random(irandomnumber + 3, 162, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 9, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
irandomnumber = Number_Random(irandomnumber + 3, 180, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 10, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
irandomnumber = Number_Random(irandomnumber + 3, 198, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 11, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
irandomnumber = Number_Random(irandomnumber + 3, 216, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 12, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
irandomnumber = Number_Random(irandomnumber + 3, 234, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 13, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
irandomnumber = Number_Random(irandomnumber + 3, 252, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 14, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
irandomnumber = Number_Random(irandomnumber + 3, 270, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 15, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
irandomnumber = Number_Random(irandomnumber + 3, 288, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 16, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
irandomnumber = Number_Random(irandomnumber + 3, 306, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 17, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
irandomnumber = Number_Random(irandomnumber + 3, 324, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 18, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
irandomnumber = Number_Random(irandomnumber + 3, 342, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 19, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
irandomnumber = Number_Random(irandomnumber + 3, 364, 0)
ldateserial = VBA.DateAdd("d", irandomnumber, VBA.DateSerial(VBA.Year(VBA.Now - 1), 1, 1))
Call Cell_OffsetFormat(objCell, 20, 0, ldateserial, False, xlHAlignRight, "dd-mmm")
Call Cell_OffsetInsertRandomNumbers(objCell.Offset(1, 1), 19, 0, dbLowestValue, dbHighestValue, iNoOfDecimals)
End Sub
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top