Results 1 to 15 of 15

20020221, 14:37 #1
 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 sheetattached 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??

20020221, 17:04 #2
 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.

20020613, 12:28 #3
 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

20020613, 12:41 #4
 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.jkpads.com
Professional Office Developers Association

20020614, 08:12 #5
 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...

20020614, 08:26 #6
 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 rootcause (but not the solution):
I'm doing a statistical ttest, here's the commentline 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 Ftest... now what???

20020614, 10:07 #7
 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.jkpads.com
Professional Office Developers Association

20020614, 11:05 #8
 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 sheetbased 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 ???

20020614, 11:57 #9
 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.jkpads.com
Professional Office Developers Association

20020614, 13:00 #10
 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 = "Ttest indicates a significant difference of (" & Format(TDiff, "Scientific") & _
") between meanvalues of online and offline data."
Else
TTest = "Ttest indicates no significant difference between meanvalues of online and offline 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

20020617, 06:43 #11
 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 addin's PTTestM does.
Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20020619, 08:08 #12
 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

20020619, 12:23 #13
 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 ttest. You also have the choice to perform a onesided or a twosided ttest. 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 twosided tests unless you have a very good rationale not to do so. For the 'student' statistician, always use twosided 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 ttest the sample size of both groups does not need to be equal; for a paired ttest equal sample size is a requirement):
= TTEST(A1:A10, B1:B15, 2, 2) is the 2sided ttest 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 Ftest.
If you want some code to do an unpaired ttest, 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
'Ftest 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) & " (twosided 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 Ftest to check for equal or unequal variance and gives the twotailed pvalue. It also calculates critical tvalues and onetailed pvalues but I here only put the twotailed pvalue in the output. Change the code as you wish.
Hope this helps.

20020619, 12:36 #14
 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 ttest (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 tstatistics"
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) & " (twosided p = " & Str$(p2) & ")"
Else
paired_ttest = "The Null Hypothesis Difference = " & Str$(NullHyp) & _
" is true (" & Str$(p2) & ")"
End If
End Function
</pre>

20020620, 09:38 #15
 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 revisit 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 EXCELside and not from the VBAside). 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 switchedon. I'm investigating now to check if again this is not me but something else...