Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Application.ScreenUpdating (EXCEL 97/2000)

    Use Application.ScreenUpdating all the time (of course). Now this suddenly doesn't seem to function anymore.
    In fact, if I debug my code and step over a line that says:
    Application.ScreenUpdating=False
    And IMMEDIATELY hover my mouse over that statement I get as value TRUE....

    Is it me or am I missing something here???

    Erik Jan

    PS1. Also during normal operation of my code, sheet changes like "Worksheets(BlaBla).Activate" are visible.... (even though I explicitly switch screenupdating off.

    PS2. It's NOT a sheet-attached code that does this (like Worksheet.activate) because 1) I don't have these in this case and 2) I'm stepping through my code...

    PS3. I've been playing with EnableEvents could that be a/the cause??

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.ScreenUpdating (EXCEL 97/2000)

    Your PS3 migth be the cause. Just write a small macro saying

    Application.EnableEvents = True
    Application.ScreenUpdating = True

    and run it. Then try to debug your code again.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.ScreenUpdating (EXCEL 97/2000)

    Sorry, implemented this and thought is was fixed. Just found out it isn't.... Have NO CLUE what is happening.

    Basically it seems as if at certain points setting application.screenupdating to false doesn't seem to prevent screenupdates from still happening.

    Again if I monitor the VALUES of Application.screenupdating that doesn't make sense but that's due to the break I set. If I create
    Debug.print Application.screenupdating
    statements, the results DO make sense... but the effect is the same: it doesn't seem to work!

    EJ

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

    Re: Application.ScreenUpdating (EXCEL 97/2000)

    Just a wild guess: does the module in question contain lots of code, or hasn't it been cleaned for a while (export code, delete module, import code)?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.ScreenUpdating (EXCEL 97/2000)

    Thanks for the tip. Used Bovey's codecleaner (4.4); regretfully I didn't see an improvement...

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.ScreenUpdating (EXCEL 97/2000)

    Well... I think I've found the root-cause (but not the solution):

    I'm doing a statistical t-test, here's the comment-line and code:

    <font color=blue>'Call: Pttestm(inprng1, inprng2, [outrng], [labels], [alpha], [difference])
    pttestm xR, yR, Out, False, 0.01, 0</font color=blue>

    <font color=red>If before Application.screenupdating is False, after this call it's TRUE....</font color=red>

    I believe the "pttestm" is the regular MS F-test... now what???

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

    Re: Application.ScreenUpdating (EXCEL 97/2000)

    I reproduced your problem, ATPVBAEN sets screenupdating back to true. But if I set it to False after the PTTestm call, all is well.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.ScreenUpdating (EXCEL 97/2000)

    But I'm at that moment in my code doing things on another sheet. If screenupdating switches to TRUE (even for a millisecond if I reset things after the call) my screen WILL update and thus 'flash' briefly.

    Realize this is not really crucial but then again... I'm a perfectionist....

    I've been thinking of maybe calling the ttest 'intermediate' via the sheet-based function but I suspect that 'behind the scenes' this might still endup at the some location (ANALYS32.xll)....

    Maybe someone has this in VBA without me needing to call to ATPVBAEN ???

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

    Re: Application.ScreenUpdating (EXCEL 97/2000)

    If you're a perfectionist, you *could* call some API functions to really freeze the Excel application window. (Don't have them though)

    But that cure could be worse than the disease...

    BTW: what is wrong with the TTest worksheet function?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.ScreenUpdating (EXCEL 97/2000)

    Here's my VBA code:

    'Call: Pttestm(inprng1, inprng2, [outrng], [labels], [alpha], [difference])
    pttestm xR, yR, Out, False, 0.01, 0
    TStat = [Out].Offset(9, 1).Value
    TCrit = [Out].Offset(13, 1).Value
    TDiff = [Out].Offset(3, 1).Value - [Out].Offset(3, 2).Value
    If Abs(TStat) >= TCrit Then
    TTest = "T-test indicates a significant difference of (" & Format(TDiff, "Scientific") & _
    ") between mean-values of on-line and off-line data."
    Else
    TTest = "T-test indicates no significant difference between mean-values of on-line and off-line data."
    End If

    I tried the TTEST but don't directly see how this could operate in a similar way... (the [difference] particularly worries me)

    EJ

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

    Re: Application.ScreenUpdating (EXCEL 97/2000)

    AFAIK you can use TTest then. Check out the attached workbook. You can see the Ttest worksheet function yields the same result as the Analysis Toolpak add-in's PTTestM does.
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.ScreenUpdating (EXCEL 97/2000)

    Thanks, implemented and it works (it is a bit different tough but more than acceptable).

    Thanks again!

    EJ

  13. #13
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.ScreenUpdating (EXCEL 97/2000)

    ErikJan,

    TTEST can be used in different ways, mainly depending if you want to do a paired or unpaired t-test. You also have the choice to perform a one-sided or a two-sided t-test. Paired means that the two groups are not independent from each other e.g. you want to compare the blood pressure of a group of patients before and after treatment. Unpaired means that your two groups are completely independent e.g. you have two groups of patients, one receiving a treatment, the other group placebo (or no treatment). These are not the same patients and thus independent from each other.
    Always use two-sided tests unless you have a very good rationale not to do so. For the 'student' statistician, always use two-sided tests.

    To use TTEST, here assuming data of group 1 in A1:A10 and data of group 2 in B1:B15 (note that for an unpaired t-test the sample size of both groups does not need to be equal; for a paired t-test equal sample size is a requirement):

    = TTEST(A1:A10, B1:B15, 2, 2) is the 2-sided t-test assuming equal variances

    Unpaired tests differ depending on equality or unequality of variances, therefore, you need to check first if the variances can be considered equal. This can be done with an F-test.

    If you want some code to do an unpaired t-test, here you have a function :

    <pre>Function unpaired_ttest(XRange As Range, YRange As Range, _
    Alfa As Double, NullHyp As Double) As String
    Dim gem1 As Double
    Dim gem2 As Double
    Dim var1 As Double
    Dim var2 As Double
    Dim n1 As Integer
    Dim n2 As Integer
    Dim F As Double
    Dim df1 As Integer
    Dim df2 As Integer
    Dim df As Double
    Dim diff As Double
    Dim pF As Double
    Dim p1 As Double
    Dim p2 As Double
    Dim t As Double
    Dim tcrit1 As Double
    Dim tcrit2 As Double
    gem1 = Application.Average(XRange)
    gem2 = Application.Average(YRange)
    var1 = Application.Var(XRange)
    var2 = Application.Var(YRange)
    n1 = XRange.Cells.Count
    n2 = YRange.Cells.Count
    diff = gem1 - gem2
    'F-test for equality of variances
    If var1 > var2 Then
    F = var1 / var2
    Else
    F = var2 / var1
    End If
    df1 = n1 - 1
    df2 = n2 - 1
    pF = Application.FDist(F, df1, df2)
    If pF < Alfa Then
    df = n1 + n2 - 3
    Else
    df = n1 + n2 - 2
    End If
    t = Abs(diff - NullHyp) / Sqr(var1 / n1 + var2 / n2)
    tcrit2 = Application.TInv(Alfa, df)
    tcrit1 = Application.TInv(2 * Alfa, df)
    p1 = Application.TDist(t, df, 1)
    p2 = Application.TDist(t, df, 2)
    If p2 < 0.05 Then
    unpaired_ttest = "The difference of means is significantly different from " & _
    Str$(NullHyp) & " (two-sided p = " & Str$(p2) & ")"
    Else
    unpaired_ttest = "The Null Hypothesis 'Difference = " & Str$(NullHyp) _
    & "' is true (" & "p = " & Str$(p2) & ")"
    End If
    End Function
    </pre>


    You have to pass the data of group 1 (XRange), the data of group 2 (YRange), the value of Alfa, which is usually taken 0.05, and the null hypothesis value against which you want the difference of means to compare with; many times this is against zero. The code performs the F-test to check for equal or unequal variance and gives the two-tailed p-value. It also calculates critical t-values and one-tailed p-values but I here only put the two-tailed p-value in the output. Change the code as you wish.

    Hope this helps.

  14. #14
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.ScreenUpdating (EXCEL 97/2000)

    ErikJan,

    Maybe you are using a paired t-test (that's probably what the P means in Pttestm). You can use TTEST for this as well, taking type = 1.

    = TTEST(A1:A10, B1:B10,2,1)

    or the following VBA function:

    <pre>Function paired_ttest(XRange As Range, YRange As Range, _
    Alfa As Double, NullHyp As Double) As String
    Dim i As Integer
    Dim aantal As Integer
    Dim gem1 As Double
    Dim gem2 As Double
    Dim gem As Double
    Dim var1 As Double
    Dim var2 As Double
    Dim n1 As Integer
    Dim n2 As Integer
    Dim df As Integer
    Dim Delta() As Double
    Dim VarDiff As Double
    Dim t As Double
    Dim tcrit1 As Double
    Dim tcrit2 As Double
    Dim p1 As Double
    Dim p2 As Double

    aantal = XRange.Cells.Count
    If aantal <> YRange.Cells.Count Then
    MsgBox "Numbers not equal!!", vbOKOnly, "paired t-statistics"
    Exit Function
    End If
    ReDim Delta(aantal)
    For i = 1 To aantal
    Delta(i) = XRange.Cells(i).Value - YRange.Cells(i).Value
    Next i
    gem1 = Application.Average(XRange)
    gem2 = Application.Average(YRange)
    var1 = Application.Var(XRange)
    var2 = Application.Var(YRange)
    n1 = XRange.Cells.Count
    n2 = YRange.Cells.Count
    gem = 0
    For i = 1 To aantal
    gem = gem + Delta(i)
    Next i
    gem = gem / aantal
    VarDiff = 0
    For i = 1 To aantal
    VarDiff = VarDiff + (Delta(i) - gem) * (Delta(i) - gem)
    Next i
    VarDiff = VarDiff / (aantal - 1)
    t = (gem - NullHyp) / (Sqr(VarDiff) / Sqr(aantal))
    df = aantal - 1
    tcrit2 = Application.TInv(Alfa, df)
    tcrit1 = Application.TInv(2 * Alfa, df)
    p1 = Application.TDist(Abs(t), df, 1)
    p2 = Application.TDist(Abs(t), df, 2)
    If p2 < 0.05 Then
    paired_ttest = "The mean of differences is significantly different from " _
    & Str$(NullHyp) & " (two-sided p = " & Str$(p2) & ")"
    Else
    paired_ttest = "The Null Hypothesis Difference = " & Str$(NullHyp) & _
    " is true (" & Str$(p2) & ")"
    End If
    End Function
    </pre>


  15. #15
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.ScreenUpdating (EXCEL 97/2000)

    Jan Karel & Hans,

    Thanks to you both for helping me on the statistical details; I've implemented something along the lines that were sketcked by Jan Karel but I'll re-visit after having carefully examined the suggestions Hans made....

    But.. read on... it's not over yet...

    Remember how this all was triggered??? I'll still have to test the code that Hans supplied (if I choose to implement that) as this might very well call the faulty procedure somewhere again in the background... (even though I realize this is now called from the EXCEL-side and not from the VBA-side). So I'll have to do some more Application.screenupdating checks.

    And more...

    Even with the "pttest" thing (temporarily?) addressed, I AGAIN stumbled onto an instance where the screenupdating is switched-on. I'm investigating now to check if -again- this is not me but something else...

Posting Permissions

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