+ Reply to Thread
Results 1 to 18 of 18

Two-dimensional arrays

  1. #1
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Two-dimensional arrays

    Hi,

    I have a question that I have not been able to find an answer to so far by searching forum posts and websites. Can we (should we) replace a search of all cells in a range (e.g. ActiveSheet.UsedRange) with a two-dimensional array (if I understand this correctly)?

    Here is the situation:

    1) For each cells in a range (in case being all used cells in the activeworksheet), search for a word (e.g. Excel) in the cells string

    2) For each instance of the found word, change the formatting for this word (e.g. .characters.font) in the string

    3) Loop through the whole string in each cell

    4) Loop through all cells in the assigned range

    This is slow because it looks at each cell separately and then the next cell then next cell, etc. Can we (should we) put the activeSheet.UsedRange into an array and have excel check each part of the array then write back to Excel?

    Your thoughts and opinions are highly appreciated (even if you think I have no idea what I am talking about ).

    Thanks.

    abousetta
    Last edited by abousetta; 06-10-2011 at 01:41 AM.

  2. #2
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,230

    Re: Two-dimensional arrays

    That would speed things up, imperceptibly.

    The slow part of that process is the writing the formatting to the cells.
    Applying RichText to a cell is slow, and it has to be done cell by cell.
    It is inherently slow.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Two-dimensional arrays

    Thanks @mikerickson. Do you know of any threads/ posts or sites that exemplify how to accomplish this. I would like to attempt doing this myself before burdening others.

    Thanks again.

    abousetta

  4. #4
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Two-dimensional arrays

    I've been working with 1D and 2D arrays for the past month or so. Calculations, comparisons, and whatever, seem to be a lot faster. A good person to discuss this with is snb, he's always using arrays.
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  5. #5
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Two-dimensional arrays

    Thanks @Modred. snb has written some nice arrays in the past and I hope this thread catches his attention. I am hoping to speed this process up because it is really slow right now and doesn't seem to be using the full capacity of the computer (memory, cpu) since it is checking only one cell at a time.

    abousetta

  6. #6
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: Two-dimensional arrays

    Thanks to Morded it 'caught' my attention.
    Please post an example, so I won't have to fabricate one myself; in that case the answer may be closer to what you are looking for.



  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,174

    Re: Two-dimensional arrays

    Using FIND rather than looping and testing all cells in the range would speed things up.
    Cheers
    Andy
    www.andypope.info

  8. #8
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Two-dimensional arrays

    Hi snb,

    Thanks for having a look at this. Attached is a simple mock workbook. What I normally do is ask the macro to loop each cell one by one. What I would like to be able to do is provide the macro with the lower boundary of the array (upper boundary is not known as it changes).

    Thanks.

    abousetta
    Attached Files Attached Files
    Last edited by abousetta; 06-05-2011 at 12:52 PM. Reason: Replaced attachment

  9. #9
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Two-dimensional arrays

    Hi Andy Pope,

    Thank you for that. Would it be possible to only perform actions on set of cells that are not adjacent by using the FindAll function? From my limited understanding of arrays, I thought that the range must be continuous (non-interupted), but of course I am probably wrong.

    abousetta

  10. #10
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Two-dimensional arrays

    FYI you can't do the replacement in the array - you have to do it in cell.
    Remember what the dormouse said
    Feed your head

  11. #11
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: Two-dimensional arrays

    If you want to multiply the numeric cells with 100:

    Please Login or Register  to view this content.
    I didn't see any formatting wishes in your file.

  12. #12
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Two-dimensional arrays

    Hi snb,

    You are right. My example is not reflective of the problem. Attached is an example with a before and after sheet. The user is asked to put a word(s) for formatting. In the example the word inputted is "hip" and the macro is asked to format all words with the word hip by making it bold and red.

    You will see that the macro loops each cell; one by one. Is there a way to Read/Write Large Blocks of Cells in a Single Operation. I read about this in the Excel VBA Performance Coding Best Practices and they pointed to a webpage on John Walkenbach's website, but I still don't understand the concept.

    Thanks.

    abousetta
    Attached Files Attached Files

  13. #13
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: Two-dimensional arrays

    to elaborate on the previous suggestion:

    Please Login or Register  to view this content.

  14. #14
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Two-dimensional arrays

    Another way:

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  15. #15
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Two-dimensional arrays

    Actually, there's no reason to split the text:
    Please Login or Register  to view this content.

  16. #16
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Two-dimensional arrays

    Hi shg,

    Thank you again for this. It took me a while, but I think I understand the concept much better now. Even so, I am having trouble merging this with another form of the macro that allows the user to choose the font specifications from a userform. The most recent version was kindly modified by romperstomper in this thread. I have tried to merge the two approaches, but am having difficulty since the former uses an object as part of the byval and you are using an array (if I understand the problem correctly). So I tried to merge both codes into one macro but its compling.

    Thanks.

    abousetta
    Attached Files Attached Files

  17. #17
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Unhappy Re: Two-dimensional arrays

    Hi,

    Well, more head-scratching but a bit closer; unfortunately not there yet. This now can loop through the lists provided in the userform and can change the text font if it is hard-coded into the code. What I just can't do is get the user-defined code to get to the code...

    Any suggestions?

    abousetta

    Please Login or Register  to view this content.

  18. #18
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Two-dimensional arrays

    OK. I took the opposite approach and it seems to work so far. I replaced the original cell loop and replaced with the array. I don't see much of a difference in running time but this may be because my sample was small.

    Any thoughts?

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1