VBA to Create Sample Budget Data

Yesterday I was working with one of our partners to create a proof of concept using Dodeca and one of the issues that came up was creating some sample budget data to go with the actual numbers obtained from the customer. The partner wanted the budget numbers to be within the range of actual +10%/-15%. Not a problem, so I dug deep into my Excel experience and came up with the following VBA algorithm in about 10 minutes.. I *think* it did what I wanted so I thought I would share it:

Option Explicit

Private Const MIN_VAR_PERCENT As Double = -0.15
Private Const MAX_VAR_PERCENT As Double = 0.1

Sub AdjustTargets()
    Dim oRange As Range
    Dim oCell As Range
    Dim vNumber As Variant
    Dim iPlaces As Integer

    ''' use the selected cells
    Set oRange = Selection

    ''' loop the selected cells
    For Each oCell In oRange
        ''' if the cell contains a number
        If IsNumeric(oCell.Formula) Then
            ''' grab the number
            vNumber = oCell.Formula

            ''' get the number of decimal places
            If InStr(CStr(vNumber), ".") > 0 Then
                iPlaces = Len(CStr(vNumber)) - InStr(CStr(vNumber), ".")
            Else
                iPlaces = 0
            End If

            ''' compute a random value between the bounds and
            oCell.Formula = Round((vNumber * (1 + MIN_VAR_PERCENT)) + (Rnd() * (MAX_VAR_PERCENT - MIN_VAR_PERCENT)) * vNumber, iPlaces)
        End If
    Next
End Sub

The HTML rendering on the blog is sometimes not very good, so here is a screenshot of the VBA window (click on the picture to see a full sized version).












To use the VBA, select a range in an Excel workbook and run the macro.