Refreshing a custom Function
 

You did not post your code but perhaps using
application.volatile
as the first line might help

--
Don Guillett
SalesAid Software

"Hotbird" wrote in message
...
I have written a Function which delivers a status report to spreadsheet
cell
C5. This is the formula in cell C5:
=FQSUM(B2,A1,Historical!C1,Historical!G1). The only problem is that the
status report does not dynamically update as the parameters are adjusted
by
a Spin Button located on Form StartQN. This is called by: "StartQN.Show"
and dismissed by: "Unload StartQN". The Function string does update
after
the Form is unloaded, but not whilst the Form is active, thereby reducing
its usefulness. What I need is a line of code within the Private Sub
SpinButton1_SpinUp() (and down) procedures to force the Function to
recalculate for cell C5. Any suggestions?

Refreshing a custom Function
 

I have written a Function which delivers a status report to spreadsheet cell
C5. This is the formula in cell C5:
=FQSUM(B2,A1,Historical!C1,Historical!G1). The only problem is that the
status report does not dynamically update as the parameters are adjusted by
a Spin Button located on Form StartQN. This is called by: "StartQN.Show"
and dismissed by: "Unload StartQN". The Function string does update after
the Form is unloaded, but not whilst the Form is active, thereby reducing
its usefulness. What I need is a line of code within the Private Sub
SpinButton1_SpinUp() (and down) procedures to force the Function to
recalculate for cell C5. Any suggestions?

Refreshing a custom Function
 

"Don Guillett" wrote in message
...
You did not post your code ...


an omission easily corrected

Option Explicit

' 28th June 2004: Float Warning

Dim int2 As Integer
Dim str1 As String

Public Function FLookF(ByVal strShow As String, ByVal intSeq As Integer) As
Integer

' RETURNS FIRST QUESTION IN CURRENT ROUND
' STRSHOW IS SHOW NUMBER
' INTSEQ IS SEQUENCE NUMBER 0 TO 99

Dim int1 As Integer

If intSeq < 10 Then
FLookF = 1
Exit Function
ElseIf intSeq 89 Then
int1 = 8
Else
int1 = intSeq \ 10
End If
Open gstrWL6S & strShow & "R" & int1 & ".txt" For Input As #1
For int1 = 1 To 15
Line Input #1, str1
Next int1
Close #1
FLookF = Val(str1)

End Function
Public Function FLookL(ByVal strShow As String, ByVal intSeq As Integer) As
Integer

' RETURNS LAST QUESTION IN CURRENT ROUND
' STRSHOW IS SHOW NUMBER
' INTSEQ IS SEQUENCE NUMBER 0 TO 99

Dim int1 As Integer

If intSeq < 15 Then
FLookL = 0
Exit Function
ElseIf intSeq 94 Then
int1 = 8
Else
int1 = (intSeq - 5) \ 10
End If
Open gstrWL6S & strShow & "R" & int1 & ".txt" For Input As #1
For int1 = 1 To 16
Line Input #1, str1
Next int1
Close #1
FLookL = Val(str1)

End Function
Private Function FMult(ByVal intFirst As Integer, ByVal intLast As Integer)
As Integer

Dim varEQPR As Variant

' RETURNS ESTIMATED QUESTIONS FOR SPECIFIED ROUNDS
' INTFIRST IS FIRST ROUND NUMBER 1 TO 8
' INTLAST IS LAST ROUND NUMBER 1 TO 8

Dim int1 As Integer

If IsEmpty(varEQPR) Then
varEQPR = Array(22, 22, 21, 21, 20, 20, 17, 14)
End If
If intFirst < 1 Then
intFirst = 1
End If
For int1 = intFirst To intLast
FMult = FMult + varEQPR(int1 - 1)
Next int1

End Function
Private Function FQSum(ByVal strShow As String, ByVal intSeq As Integer,
ByVal intMQ As Integer, ByVal intFQ As Integer) As String

' RETURNS QUESTION SUMMARY REMARKS
' STRSHOW IS SHOW NUMBER
' INTSEQ IS SEQUENCE NUMBER
' INTMQ IS MAIN GAME QUESTIONS
' INTFQ IS FLOAT QUESTIONS

Dim intLQR8 As Integer ' LAST QUESTION OF ROUND 8
Dim intPOLQ As Integer ' PREDICTED OR LAST QUESTION
Dim intQU As Integer ' QUESTIONS USED
Dim int1 As Integer
Dim strRPred As String ' ROUND PREDICTED FOR TROUBLE

' ESTIMATE LAST QUESTION OF ROUND 8

If intSeq 84 Then
intLQR8 = FLookL(strShow, 85)
ElseIf intSeq Mod 10 -1 And intSeq Mod 10 < 5 And intSeq 9 Then
intLQR8 = FLookF(strShow, intSeq) + FMult((intSeq + 5) \ 10, 8) - 1
Else
intLQR8 = FLookL(strShow, intSeq) + FMult((intSeq + 5) \ 10, 8)
End If

' DETERMINE QUESTIONS USED SO FAR

If intSeq Mod 10 -1 And intSeq Mod 10 < 5 And intSeq 9 And intSeq <
85 Then
intQU = FLookF(strShow, intSeq) - 1
Else
intQU = FLookL(strShow, intSeq)
End If

' PREDICT TROUBLE

strRPred = ""
For int1 = 1 To 8
If (intSeq + 5) \ 10 int1 Then

' LAST QUESTION OF LAST COMPLETE ROUND

intPOLQ = FLookL(strShow, 5 + int1 * 10)
ElseIf intSeq Mod 10 -1 And intSeq Mod 10 < 5 And intSeq 9 And
intSeq < 85 Then

' PREDICTION: QUESTION SELECTED TO BEGIN NEXT ROUND PLUS ESTIMATES

intPOLQ = FLookF(strShow, intSeq) + FMult((intSeq + 5) \ 10,
int1) - 1
Else

' PREDICTION: LAST QUESTION OF LAST ROUND PLUS ESTIMATES

intPOLQ = FLookL(strShow, intSeq) + FMult((intSeq + 5) \ 10, int1)
End If

' MAIN GAME TROUBLE

If intMQ intQU Then
If Len(strRPred) = 0 And intPOLQ intMQ Then
strRPred = "Round " & int1
End If
Else

' FLOAT TROUBLE

If Len(strRPred) = 0 And intPOLQ intMQ + intFQ Then
strRPred = "Round " & int1
End If
End If
Next int1

' ASSEMBLE 9 REMARKS

If intQU + 1 intMQ + intFQ Then
FQSum = "Float - all used during Game Play."
End If
If intSeq < 85 Then
If intSeq < 3 Then
FQSum = ""
ElseIf intMQ = 0 Then
FQSum = "Main Questions not yet loaded."
ElseIf intMQ intQU + 1 And intMQ + 1 intLQR8 Then
FQSum = "Main Questions - " & intLQR8 - intQU & "/" & _
intMQ - intQU & ": Sufficient."
ElseIf intMQ intQU Then
FQSum = "Main Questions - " & intLQR8 - intQU & "/" & _
intMQ - intQU & ". Float may be used in " _
& strRPred & "."
ElseIf intQU + 1 intMQ And intMQ + intFQ + 1 intLQR8 Then
FQSum = "Using Float Questions - " & intLQR8 - intQU & "/" & _
intMQ + intFQ - intQU & ": Sufficient."
ElseIf intMQ + intFQ intQU Then
FQSum = "Using Float Questions - " & intLQR8 - intQU & "/" & _
intMQ + intFQ - intQU & _
". May run out in " & strRPred & "."
End If
ElseIf intMQ + 1 intQU Then
FQSum = "Summary - " & intMQ - intQU & "/" & _
intMQ & " Main Questions unused."
ElseIf intQU < 1 + intMQ + intFQ Then
FQSum = "Summary - " & intMQ + intFQ - intQU & "/" & _
intFQ & " Float Questions unused."
End If

End Function