Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Substitute Formulas with code (Excel 2003)

    hello,
    i

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Substitute Formulas with code (Excel 2003)

    Can I ask why you want to do this? VBA is slower that built-in formulas, so your workbook performance will suffer.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Substitute Formulas with code (Excel 2003)

    It's possible to change formulas using Edit | Replace or using VBA code, but it's not clear to me what you want to replace, and what you want to replace it with.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Substitute Formulas with code (Excel 2003)

    the problem is not the performance but the size of my files.
    In the Mastermap i have the resourcepool of my company with workers calendars, the whole projectplanning and now i should
    make a worksheet, where i make an offer for the estimated amount of mandays (from the deadline) to the end. - so I want to replace it
    with single values (size is up to 50 MB now...).
    Stefan

  5. #5
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Substitute Formulas with code (Excel 2003)

    well, i want to replace everything in the workseet ETC count with values; the base sheet is Data ETCs.

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

    Re: Substitute Formulas with code (Excel 2003)

    If you want to replace formulas with their values, you can copy a range, then paste special with the Values option. If you want to replace formulas with their values in code, you can use something like

    Selection.Value = Selection.Value

    or

    With Worksheets("ETC count").UsedRange
    .Value = .Value
    End With

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Substitute Formulas with code (Excel 2003)

    What I usually do in cases like this would be to copy the top row of formulas to an empty row (eg in your example copy row5 to row 1. This preserves a copy of all the formulas.

    Then select all the A5:Kwhatever and copy, paste-special values. this will convert all the formulas to the values and reduce the file size and speed up performance. If you ever need to regenerate the data or need to extract new values, you can copy row 1 to the appropriate rows, then copy- pastespecial - values again...

    Steve

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

    Re: Substitute Formulas with code (Excel 2003)

    Well, that is what *I* say too, most of the time. But Charles Williams (http://www.decisionmodels.com, not sure if he has put an article on his site though) has proven that in fact UDF's can be faster than some built-in formulas.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Substitute Formulas with code (Excel 2003)

    There is an exception to every rule! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> (including this one)
    These days I tend to go for legibility and adaptability rather than outright performance in most cases (except for some of my larger workups) and UDFs can be much better in that regard! I would be interested to see that article though if you know where I can find it (haven't been on the DM site for a while but will check it out shortly to see if it's there)
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Substitute Formulas with code (Excel 2003)

    Hi Hans,
    thanks for the answer, but i was looking for n other solution. in the meantime i made a workaround on it.
    My problem is, that some colleagues of mine should do my work whenever i am not at work. So it should
    be a more or less very automatic workaround with few work by hand...
    thanks again,
    stef.

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

    Re: Substitute Formulas with code (Excel 2003)

    I suggested two VBA solutions in my reply. You could use these in a macro.

Posting Permissions

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