Copy Objects Gettable Properties to Another Object

[casewolf]

I have a project where I am duplicating/saving formulas and formatting of tables (in another table) so that i can rebuild the original table later
I can have been able to get do this manually by assigning each of the properties to an array then then joining to a string (and then manually splitting and applying later)

Then I found this post/answer: https://www.mrexcel.com/board/threads/vba-is-there-a-way-to-iterate-through-every-property-of-an-excel-object.1132644/post-5478640

I got this working for getting an array of gettable properties,
but now how do I use this array to set the properties on a "new" object later? (i.e. copy Cell.Interior properties from cell A1 - then apply all those properties to another cell Y7)?
Obviously, the code below is just a mock-up and not anywhere near as complex as my real code is. In my real code I am grabbing formulas, conditional formatting, data validation and general cell formatting (NumberFormat, Interior, Font, Boarders)

Function get_Interior_properties() As String
    Dim sht As Worksheet
    Dim tbl As ListObject
    Dim col As ListColumn
    Dim rng As Range
    Dim interior As Object
    Dim popertiesInfo() As PropertyInfo
    Dim arrDumbProperties As Variant
        
    Set sht = ThisWorkbook.Sheets("foo")
    Set tbl = sht.ListObjects("bar")
    Set col = tbl.ListColumns("baz")
    Set rng = col.DataBodyRange.Cells(1)
    
    Set interior = rng.interior
    
    Set popertiesInfo = GetPropertiesInfoOfObject(interior)
    
    ReDim arrDumbProperties(LBound(propertiesInfo) To UBound(propertiesInfo))
    For i = LBound(propertiesInfo) To UBound(propertiesInfo)
        arrDumbProperties(i) = propertiesInfo(i).Value
    Next i
    
    get_Interior_properties = Join(arrDumbProperties, vbTab)

End Function

Sub set_Interior_poperties(strDumbProperties As String)
    Dim sht As Worksheet
    Dim tbl As ListObject
    Dim col As ListColumn
    Dim rng As Range
    Dim interior As Object
    Dim arrDumbProperties As Variant
        
    Set sht = ThisWorkbook.Sheets("foo")
    Set tbl = sht.ListObjects("bar")
    Set col = tbl.ListColumns("baz")
    Set rng = col.DataBodyRange.Cells(1)
    
    Set interior = rng.interior
    
    arrDumbProperties = Split(strDumbProperties, vbTab)
    
'''this is where i want to remove hardcoding and just pass a function an object and a "PropertyInfo" to auto set all the values
    interior.Color = arrDumbProperties(1)
    interior.ColorIndex = arrDumbProperties(2)
    interior.InvertIfNegative = arrDumbProperties(3)
    '....
    interior.x = arrDumbProperties(x)
'''

End Sub

 

[Jaafar Tribak]

Use VBA.CallByName with vbLet or vbSet if the Property is an object.

Edit.
Why not save a copy of your table somewhere so you can restore it later on. That would be easier than restoring each Property value for each cell in your table.

 

[casewolf]

Use VBA.CallByName with vbLet or vbSet if the Property is an object.

OMG - how did I not know about this?!?! Amazing

Also

Why not save a copy of your table somewhere so you can restore it later on. That would be easier than restoring each Property value for each cell in your table.

Because I am clearing data from tables and then allowing users to custom build the columns they want for tables to then import and process new raw data - so there needs to be a "save-able" config for dynamically buildable columns so that new tables can be built on the fly