Results 1 to 3 of 3

Optimizing Code

Threaded 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.

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