+ Reply to Thread
Results 1 to 3 of 3

Optimizing Code

Hybrid View

  1. #1
    Registered User
    Join Date
    01-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    30

    Optimizing Code

    Hi,

    I have a worksheet where I calculate the Z score of around 4 million cells. To speed it up I have a column of all row averages and standard deviations (ThisWorkbook.numofcolumns+2 and +3). It is still really slow so I put a loading screen. The loading screen is updated by the variable Percent. I know when setting formulas that I should do the full range at once for speed but I could not figure out how to update the loading screen at the same time (the only thing I could think of was a modeless userform offscreen with a timer). I was wondering if someone could help me a) speed it up and/or b) update the variable Percent while it calculates the Z score. Thanks!

    With ThisWorkbook.Worksheets("Z score")
        
        For i = 2 To ThisWorkbook.numofcolumns + 1
    
        .Range(.Cells(2, i), .Cells(ThisWorkbook.numofrows + 1, i)).FormulaR1C1 = "=(('Gene Expression'!RC-'Gene Expression'!RC" & ThisWorkbook.numofcolumns + 2 & ")/'Gene Expression'!RC" & ThisWorkbook.numofcolumns + 3
      'Update loading screen
        Percent = (i + 50) / (ThisWorkbook.numofcolumns + 200)
        With Progress
                .FrameProgress.Caption = Format(Percent, "0%")
                .LabelProgress.Width = Percent * (.FrameProgress.Width - 10)
            End With
    
        DoEvents
        
        Next i
    Last edited by tucanj; 02-04-2013 at 10:07 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,227

    Re: Optimizing Code

    Hi tucanj,

    If you supply a small sample of your data we could see what you are doing and be a lot more help. You can attach a sample file by clicking on Go Advanced below the message area and then on the Paper Clip Icon above the Advanced Message Area. You should also give an answer or two on how and where you want to see those z scores.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    01-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    30

    Re: Optimizing Code

    Hope that helps. Unfortunately had to cut it down by a lot due to size restrictions. I want the Z scores in a new worksheet called "Z scores"
    Attached Files Attached Files

+ 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