Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Store a range of formatting in a variable? (Excel VBA)

    A post logged a week or so ago has triggered another question in me for the guru's.

    Question:
    Is it possible to store the formatting of a range in a variable, remove the formatting to apply an action on the unformatted data, and then apply the formatting to the data again after the action has taken place. If so, what would the code look like that can do this?

    My Q is again focussed on learning VBA techniques. I am aware that I could copy the sheet, remove formatting...print or do whatever with it and then delete the sheet...!
    I am trying to learn more about collections/arrays. I dunno if this Q fits in there, but never-the-less... advice and learning is the name of the game. Please be patient with me...but I believe the lounge is for this purpose too!

    TIA
    Regards,
    Rudi

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store a range of formatting in a variable? (Excel VBA)

    I'd turn this around I guess: Get the data out, do the work and then get the data back in , leaving the formatting in place the whole time.
    To keep the formatting of a range, the simplest way is to copy/pastespecial the formatting to a hidden sheet and when done, copy/pastespecial it back. Otherwise you would have to start writing code that steps through all (formatting related) properties of the range object. A daunting task.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store a range of formatting in a variable? (Excel VBA)

    Ok. I see your point. Could you be more specific when you say: Get the data out, do the work and then get the data back in , leaving the formatting in place the whole time. Are you refering to copy/paste as text or are you refering to storing the range of data in a variable.
    Tx
    Regards,
    Rudi

  5. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store a range of formatting in a variable? (Excel VBA)

    Yes, I see your point. You are confirming what Pieterse says...to rather leave the formatting where it is and work with the data instead. Thanx. This is clear!
    Regards,
    Rudi

  6. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Store a range of formatting in a variable? (Excel VBA)

    A cell has many formatting aspects. There is no single "format" object that holds all these aspects, so you'd have to decide which ones you want to store / restore:
    - Borders (top, left, bottom, right, 2 diagonals, and for each:color, line style, weight)
    - Font (name, size, color, bold, italic, underline, shadow, ...)
    - Conditional formatting (up to three conditions, each with various formatting options)
    - Horizontal and vertical alignment, indentation, text wrapping.
    - Interior color / pattern.
    - Number format.
    - Merged with other cells.
    And probably more.

    Copying the range or sheet is MUCH easier!

  7. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store a range of formatting in a variable? (Excel VBA)

    Data into a variable, but it depends on your operation, sometimes it is done best using standard Excel functionality, sometimes VBA manipulation is needed.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store a range of formatting in a variable? (Excel VBA)

    I don't know exactly how this might fit in your scenario, but you might get some value from assigning a (temporary) Style to your selection, then reapplying it when desired. As mentioned, a Style will only "capture" certain aspects of the formatting though, so YMMV. Look for "Styles Collection Object" in XL VBA help.

    Alan

  9. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store a range of formatting in a variable? (Excel VBA)

    Now that is a *very* good idea!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store a range of formatting in a variable? (Excel VBA)

    I've found Styles useful for other purposes, in custom workbooks. In particular, to "tag" various ranges. The "tag" style can look identical to Normal (or any other style) but have a different name. Selective operations can then be performed on cells only with this "tag" style. It is susceptible to user "fiddling" though... or maybe there's a simple way to mask it all from such meddlesome priests.

    Alan

  11. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store a range of formatting in a variable? (Excel VBA)

    Thanx Alan, I would never have thought of that!
    Regards,
    Rudi

  12. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store a range of formatting in a variable? (Excel VBA)

    I am having a hard time trying to figure out what you want. When you refer to the .Value property of a cell you always refer to the value with no formatting. If you set the .Value property, you set the value and the cell formatting is then applied. Could you be a little more specific about exactly what you are trying to do. Any format that can be set can also be accessed to save the current format.
    Legare Coleman

  13. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Store a range of formatting in a variable? (Excel VBA)

    You can define both fixed size and variable size array variables.

    Example 1:

    Dim arrText(1 To 10) As String
    Dim i As Integer
    For i = 1 To 10
    arrText(i) = Range("A" & i).Text
    Next i

    Example 2:

    Dim arrNumbers(1 To 2, 1 To 4) As Long
    Dim i As Integer, j As Integer
    For i = 1 To 2
    For j = 1 To 4
    arrNumbers(i, j) = 10 * i + j
    Next j
    Next i

    Example 3:

    Dim arrVar() As Variant
    Dim n As Integer
    n = 3
    ReDim arrVar(1 To n)
    arrVar(1) = "Rudi"
    arrVar(2) = 37
    arrVar(3) = #06/27/2005#

  14. #13
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store a range of formatting in a variable? (Excel VBA)

    Hi Legare,
    I know its not the most common thing to do; that is: to store formatting in a variable...and I suppose Jan Karel, and Hans convinced me of that in their replies. As I mentioned in my opening post...I have little experience with arrays...and I was under the impression that one would have to store multiple formating in an array variable of some kind. I guess in hindsight, this was not too much of a logical and achievable question. I see now that its easier to retain formatting in a style (tx Alan), or in a temp. sheet and store the value of a cell(s) in a variable. I am "reprogrammed" now...thanks to the lounge!

    PS: Is there something like an array variable? (Assuming that the array will contain data of the same type!). I am still not quite in tune with this. When you say "array"...is this only for formulas, or can arrays apply to variables as well?
    TX
    Regards,
    Rudi

  15. #14
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store a range of formatting in a variable? (Excel VBA)

    Morning Hans,

    I trust you had a great weekend and that you are keeping well!

    Thanx for your reply! As usual, it is concise and clear and explains my Q well!

    I have read up on the REDIM statement you have in Example #3. The help in Excel VBA says that you use REDIM to allocate elements to the variable. This I assume is different to "n=3" where this assigns only the VALUE 3 to the variable. REDIM creates an array base...something like "3 cells" to hold 3 different values! I think I get this now. However in your code you have a (1 to n) assigning and in the help files they use a For Loop. Is these two approaches similar...or do they differ for reasons I do not grasp yet?

    This is the examples in the VBA help:

    Dim MyArray() As Integer ' Declare dynamic array.
    Redim MyArray(5) ' Allocate 5 elements.
    For I = 1 To 5 ' Loop 5 times.
    MyArray(I) = I ' Initialize array.
    Next I

    The next statement resizes the array and erases the elements.

    Redim MyArray(10) ' Resize to 10 elements.
    For I = 1 To 10 ' Loop 10 times.
    MyArray(I) = I ' Initialize array.
    Next I

    The following statement resizes the array but does not erase elements.

    Redim Preserve MyArray(15) ' Resize to 15 elements.

    Tx
    Regards,
    Rudi

  16. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Store a range of formatting in a variable? (Excel VBA)

    ReDim tells VBA what the size of the array is. This can be done several times; unless ReDim is followed by Preserve, the existing array elements will be erased by a ReDim.

    You can specify both the lower bound and upper bound of an array:

    Dim A(3 To 6) means that the array will have elements A(3), A(4), A(5) and A(6)

    or you can specify only the upper bound. In that case, the lower bound is 0, unless you have specified Option Base 1 at the top of the module, then it is 1:

    Dim A(2) is equivalent to Dim A(0 To 2), it specifies that the array will have elements A(0), A(1) and A(2).

    The For ... Next loops in the examples are used to fill the array elements.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •