Storing Range attributes as an object?

Question

I'm having trouble with the way I designed this little report I'm making. Is it possible to create a variable for a Range object in Excel VBA, for the purposes of applying the formatting to another Range? Here is my example:

I'm creating a dictionary from the Microsoft Scripting Runtime library:

Dim d as Scripting.Dictionary

With this I'm adding labels, values, and (trying to add) Ranges.

Dim rng as Range    

rng.Font.Bold = True
d.Add 1, Field("test1", 12345, rng)
rng.Font.Bold = False
d.Add 2, Field("TestTwo", "Testing field", rng)
rng.HorizontalAlignment = xlCenter
d.Add 3, Field("threeeee", 128937912, rng)

Dim key As Variant

For Each key In d.keys
    Range("A" & key).value = d(key).Label
    Set Range("B" & key).value = d(key).rng

Next key

Here is my Field function:

Private Function Field(Label As String, val As Variant, rng As Range) As cField

    Dim f As New cField
    f.Label = Label
    f.val = val
    Set f.rng = rng
    Set Field = f

End Function

And here is my cField class:

Option Explicit

Dim mVarValue As Variant
Dim mStrLabel As String
Dim mRng As Range

Property Let val(ByVal val As Variant)
    mVarValue = val
End Property

Property Get val() As Variant
    val = mVarValue
End Property

Property Let Label(ByVal val As String)
    mStrLabel = val
End Property

Property Get Label() As String
    Label = mStrLabel
End Property

Property Let rng(ByVal val As Range)
    Set mRng = val
End Property

Property Get rng() As Range
    Dim a As Range
    a.value = mVarValue
    Set rng = a
End Property

The idea is that the key in the dictionary is going to be the row location for the field. This way if changes need to be made to the report I'm making, the only thing that needs to be changed is the key for that particular value in the dictionary. I have been successful storing the label for the value, and the value itself, but I also want to store the formatting for that Range (bold, justification, borders, etc...).

I get a 'Run-time error '91': Object variable or With block variable not set' error on the line immediately following the rng declaration. I'm wondering if its not possible to have a generic Range that doesn't have a location on a sheet, or if somehow my syntax is off.

Any help would be greatly appreciated! :)

Answer 1

Good question! Unfortunately, I don't think you can store a range that hasn't been initialized to an existing range of cells on your worksheet. I can think of a couple of options:

1. Use a hidden worksheet to store the range information
2. Store the range information manually, in a handful of member variables

Option 1 could be the easiest, despite the fact that it sounds like overkill to have an extra sheet kicking around. I'm picturing one hidden worksheet, defined specifically for this purpose.

Option 2 might be simplified if you only need to keep track of a couple of range properties (borders and color, for example).

Answer 2

You are correct - it is not possible to have a generic Range object. You have to 'Set' your range variable to some actual range to be able to read and write its properties.

But if you're "Letting" your rng property, then it seems you should already have a reference to a range. Why do you have a Property Let rng if you're not going to use that property in the Get statement.

Answer 3

Is it possible to create a variable for a Range object in Excel VBA, for the purposes of applying the formatting to another Range?

I'm wondering if its not possible to have a generic Range that doesn't have a location on a sheet...

The short answer is no.

The quick answer is...I suggest creating a "format" worksheet, which can be hidden or very hidden, that contains ranges, or Named Ranges, with the formatting you need. This allows you to range.Copy the "formatted" range then use range.PasteSpecial xlPasteFormats.

I dislike overwriting the user's clipboard, but it is difficult to programmatically copy the formatting of one range to another. I use this method in numerous solutions because it is flexible, maintainable, reusable, and does not rely on complex code. Moreover, I can visually change formatting without touching code.

Answer 4

How about this solution?

Create a class with

• the range address as text, ie."$A$3:$A$11,$A$18:$A$24,$D$29".

• The value

• Save the formatting of the range as a format-text.

Then you could create the range by Range(RangeAdressAsText) and use something like the following

Private Sub ApplyFormatting(r As Range, ByVal f As String)

On Error GoTo ErrHandler:

f = UCase$(f)
Dim IterateRange As Range
Dim Formatarray() As String
Formatarray = Split(f, " ")
Dim i As Integer

With r
    For i = LBound(Formatarray) To UBound(Formatarray)
        Select Case Formatarray(i)
            Case "BOLD"
                .Font.Bold = True
            Case "ITALIC"
                .Font.Italic = True
            Case "TOP"
                .VerticalAlignment = xlTop
            Case "BOTTOM"
                .VerticalAlignment = xlBottom
            Case "UNDERLINE"
                .Font.Underline = True
        End Select
    Next i
End With

Erase Formatarray

Exit Sub ErrHandler: LogInformation Format(Now, "yyyy-mm-dd hh:mm:ss") & " - " & ": @ ApplyFormatting in xlPrinter " & " - " & Err.Number & " - " & Err.Description & " - " & Err.Source & " - " & Err.LastDllError

End Sub