Clever Geek Handbook

Storing Range attributes as an object?

I am having problems with how I have developed this little report that I am doing. Is it possible to create a variable for Range object in Excel VBA to apply formatting to another range? Here's my example:

I am creating a dictionary from Microsoft Scripting Runtime Library:

Dim d as Scripting.Dictionary

With this, I add labels, values ​​and (tries 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 will be the string location for the field. Thus, if changes need to be made to the report I am creating, the only thing that needs to be changed is the key to that particular value in the dictionary. I have successfully saved the label for the value and the value itself, but I also want to keep the formatting for that range (bold, alignment, borders, etc.).

I get the error "91": "Object variable" or "Block variable error does not set" on the line immediately after the rng declaration. I am wondering if it is not possible to have a common range that does not have room in the sheet, or if some of my syntax is off.

Any help would be greatly appreciated! :)

+2


source to share


4 answers


Is it possible to create a variable for a Range object in Excel VBA, for the purpose of applying formatting to a different range?

I am wondering if it is not possible to have a generic Range that does not take place in the sheet ...

The short answer is no.



Quick answer: I suggest creating a format worksheet that can be hidden or very hidden that contains ranges or name ranges with the required formatting. This allows you to select a .Copy

"formatted" range and then use the range .PasteSpecial xlPasteFormats

.

I don't like overwriting the custom clipboard, but it's difficult to programmatically copy the formatting from 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 the formatting without touching the code.

+1


source


Good question! Unfortunately, I don't think you can keep a range that hasn't been initialized with an existing range of cells in your sheet. I can imagine several options:

  • Use a hidden sheet to store range information.
  • Store range information manually, in multiple member variables


Option 1 may be the easiest, even though it sounds like overkill to have an extra sheet. I present one hidden worksheet defined specifically for this purpose.

Option 2 can be simplified if you only need to keep track of a few properties of a range (such as border and color).

+1


source


You're right - it's impossible to have a shared Range object. You must "Set" your range variable to some actual range in order to read and write your properties.

But if you "give" your rng property, then it looks like you already have a range reference. Why do you have a Let rng property if you are not going to use that property in the Get statement.

+1


source


How about this solution?

Create a class with

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

    ...

  • Value

  • Save the formatting of the range as format text.

Then you can create a range 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
+1


source






More articles:


All Articles