Hi,

I'm running Office 2003 SP1.

I got a range of cells which refers to a function in a module. I'm getting
the following error when pointing to the exclaimation mark:
"A value used in the formula is of the wrong data type."
However, when I select one of this cell, and go into edit mode (I usually
press F2), then press Enter without modifying anything, the error
disappear, and I get a value from the function.
My problem is that I have a range(AE21:AI136) which pretty much used the
same formula, I can't expect user to go through each cell and fix it like the
way I had, so does anyone know how can I fix this?

Regards
Augustus

Augustus,

What happens if you press F9 or use application.calculatefull ?
It would help if you could show the function.

Tim.

"augustus" wrote in message
...
Hi,

I'm running Office 2003 SP1.

I got a range of cells which refers to a function in a module. I'm
getting
the following error when pointing to the exclaimation mark:
"A value used in the formula is of the wrong data type."
However, when I select one of this cell, and go into edit mode (I
usually
press F2), then press Enter without modifying anything, the error
disappear, and I get a value from the function.
My problem is that I have a range(AE21:AI136) which pretty much used
the
same formula, I can't expect user to go through each cell and fix it
like the
way I had, so does anyone know how can I fix this?

Regards
Augustus

I tried both F9 and application.calculatefull, only application.calculatefull
work.
The code is as follow, I hope it is not too long:

Function CableExtension(StrandSize As Byte, Pan1 As Byte, EndType1 As Range,
StressEnd1 As Range, StressEnd2 As Range, EndType2 As Range, _
Pan2 As Byte, Span As Single, SlabBeamConst As Byte, Length As Single,
PigsDick As Boolean) As Variant
''''''''''determine extension''''''''''
Dim sngLengthForLookUpTable As Single
Dim bytDoubleLive As Byte
Dim bytLow As Byte, bytHigh As Byte
Dim sngLengthDifference As Single
Dim sngLow1 As Single, sngLow2 As Single, sngLow3 As Single
Dim sngHigh1 As Single, sngHigh2 As Single, sngHigh3 As Single
Dim bytNotMultipleOf2 As Byte
Dim sngIntermediateLength As Single
'''''Checking input'''''
If 0 = Length Then
CableExtension = ""
Exit Function
End If
If Length 60 Or Length <= 0 Then
CableExtension = "Cable length must be between 0 < Length <= 60m."
Exit Function
End If
If Span < 4 Or Span 12 Then
CableExtension = "Span must be 4 <= Span <= 12."
Exit Function
End If
If SlabBeamConst 3 Or SlabBeamConst < 1 Then
CableExtension = "Please review Slab/Beam/Const again"
Exit Function
End If
'''''\\\\\\\\\\\\\\'''''
'''''Determine length to be used for calculation because of different type
of end'''''
If (0 < WorksheetFunction.Sum(EndType1, EndType2)) And (2
WorksheetFunction.Sum(StressEnd1, StressEnd2)) Then 'single live with dead
coupler/swaged end
sngLengthForLookUpTable = (Length - 0)
bytDoubleLive = 1
ElseIf PigsDick = True Or ((0 = WorksheetFunction.Sum(EndType1,
EndType2)) And (2 WorksheetFunction.Sum(StressEnd1, StressEnd2))) Then
'single live without dead coupler/swaged end, and pig's dick
sngLengthForLookUpTable = (Length - 0.5) '-0.5 because of
dead-end, assumed that stressed couldn't be transfer at the end
bytDoubleLive = 1
ElseIf (0 = WorksheetFunction.Sum(EndType1, EndType2) And 2 <=
WorksheetFunction.Sum(StressEnd1, StressEnd2) And PigsDick = False) Then
'double live
sngLengthForLookUpTable = Length / 2
bytDoubleLive = 2
Else
CableExtension = "Other combination of ending in existence. Please
see IT personnel."
End If
bytLow = CByte(WorksheetFunction.RoundDown(sngLengthForLook UpTable, 0))
bytHigh = bytLow + 1
sngLengthDifference = sngLengthForLookUpTable - bytLo
'''''\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\'''''
'''''Lookup table for extension'''''
If Span Mod 2 = 0 Then 'eg, 4,6,8,10,12
sngLow3 = LookUpExtensionTable(StrandSize, Span / 2, SlabBeamConst,
bytLow)
sngHigh3 = LookUpExtensionTable(StrandSize, Span / 2, SlabBeamConst,
bytHigh)
sngIntermediateLength = ((sngHigh3 - sngLow3) * sngLengthDifference
+ sngLow3) * bytDoubleLive
ElseIf WorksheetFunction.RoundUp(Span, 0) Mod 2 = 0 Then 'eg,
5.1,6.7,
Span = WorksheetFunction.RoundUp(Span, 0)
'get extension for interpolation
sngLow1 = LookUpExtensionTable(StrandSize, Span / 2, SlabBeamConst,
bytLow)
sngHigh1 = LookUpExtensionTable(StrandSize, Span / 2, SlabBeamConst,
bytHigh)
sngLow2 = LookUpExtensionTable(StrandSize, (Span - 2) / 2,
SlabBeamConst, bytLow)
sngHigh2 = LookUpExtensionTable(StrandSize, (Span - 2) / 2,
SlabBeamConst, bytHigh)
'linear interpolation to get span in odd number, ie 5, 7, 9, 11
sngLow3 = (sngLow1 + sngLow2) / 2
sngHigh3 = (sngHigh1 + sngHigh2) / 2
sngIntermediateLength = ((sngHigh3 - sngLow3) * sngLengthDifference
+ sngLow3) * bytDoubleLive
ElseIf WorksheetFunction.RoundUp(Span, 0) Mod 2 = 1 Then 'eg,
4.1,6.7,
Span = WorksheetFunction.RoundUp(Span, 0) - 1
sngLow3 = LookUpExtensionTable(StrandSize, Span / 2, SlabBeamConst,
bytLow)
sngHigh3 = LookUpExtensionTable(StrandSize, Span / 2, SlabBeamConst,
bytHigh)
sngIntermediateLength = ((sngHigh3 - sngLow3) * sngLengthDifference
+ sngLow3) * bytDoubleLive
Else
CableExtension = "Error: Calculating extension."
End If
'''''\\\\\\\\\\\\\\\\\\\\\\\\\\'''''
'''''Checking for Pan and double live'''''
CableExtension = ExtensionPan(Pan1, StressEnd1, StressEnd2, Pan2,
sngIntermediateLength)
'''''\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\'''''
''''''''''\\\\\\\\\\\\\\\\\\\''''''''''
End Function

What is "LookUpExtensionTable" ?

If this another function then your first function may not calculate
automatically if anything changes which would affect the output of
LookUpExtensionTable.

AFAIK a custom function only recalculates when one of its inputs
changes.

Tim.



"augustus" wrote in message
...
I tried both F9 and application.calculatefull, only
application.calculatefull
work.
The code is as follow, I hope it is not too long:

Function CableExtension(StrandSize As Byte, Pan1 As Byte, EndType1
As Range,
StressEnd1 As Range, StressEnd2 As Range, EndType2 As Range, _
Pan2 As Byte, Span As Single, SlabBeamConst As Byte, Length As
Single,
PigsDick As Boolean) As Variant
''''''''''determine extension''''''''''
Dim sngLengthForLookUpTable As Single
Dim bytDoubleLive As Byte
Dim bytLow As Byte, bytHigh As Byte
Dim sngLengthDifference As Single
Dim sngLow1 As Single, sngLow2 As Single, sngLow3 As Single
Dim sngHigh1 As Single, sngHigh2 As Single, sngHigh3 As Single
Dim bytNotMultipleOf2 As Byte
Dim sngIntermediateLength As Single
'''''Checking input'''''
If 0 = Length Then
CableExtension = ""
Exit Function
End If
If Length 60 Or Length <= 0 Then
CableExtension = "Cable length must be between 0 < Length <=
60m."
Exit Function
End If
If Span < 4 Or Span 12 Then
CableExtension = "Span must be 4 <= Span <= 12."
Exit Function
End If
If SlabBeamConst 3 Or SlabBeamConst < 1 Then
CableExtension = "Please review Slab/Beam/Const again"
Exit Function
End If
'''''\\\\\\\\\\\\\\'''''
'''''Determine length to be used for calculation because of
different type
of end'''''
If (0 < WorksheetFunction.Sum(EndType1, EndType2)) And (2
WorksheetFunction.Sum(StressEnd1, StressEnd2)) Then 'single live
with dead
coupler/swaged end
sngLengthForLookUpTable = (Length - 0)
bytDoubleLive = 1
ElseIf PigsDick = True Or ((0 = WorksheetFunction.Sum(EndType1,
EndType2)) And (2 WorksheetFunction.Sum(StressEnd1, StressEnd2)))
Then
'single live without dead coupler/swaged end, and pig's dick
sngLengthForLookUpTable = (Length - 0.5) '-0.5 because of
dead-end, assumed that stressed couldn't be transfer at the end
bytDoubleLive = 1
ElseIf (0 = WorksheetFunction.Sum(EndType1, EndType2) And 2 <=
WorksheetFunction.Sum(StressEnd1, StressEnd2) And PigsDick = False)
Then
'double live
sngLengthForLookUpTable = Length / 2
bytDoubleLive = 2
Else
CableExtension = "Other combination of ending in existence.
Please
see IT personnel."
End If
bytLow =
CByte(WorksheetFunction.RoundDown(sngLengthForLook UpTable, 0))
bytHigh = bytLow + 1
sngLengthDifference = sngLengthForLookUpTable - bytLow
'''''\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\'''''
'''''Lookup table for extension'''''
If Span Mod 2 = 0 Then 'eg, 4,6,8,10,12
sngLow3 = LookUpExtensionTable(StrandSize, Span / 2,
SlabBeamConst,
bytLow)
sngHigh3 = LookUpExtensionTable(StrandSize, Span / 2,
SlabBeamConst,
bytHigh)
sngIntermediateLength = ((sngHigh3 - sngLow3) *
sngLengthDifference
+ sngLow3) * bytDoubleLive
ElseIf WorksheetFunction.RoundUp(Span, 0) Mod 2 = 0 Then
'eg,
5.1,6.7,
Span = WorksheetFunction.RoundUp(Span, 0)
'get extension for interpolation
sngLow1 = LookUpExtensionTable(StrandSize, Span / 2,
SlabBeamConst,
bytLow)
sngHigh1 = LookUpExtensionTable(StrandSize, Span / 2,
SlabBeamConst,
bytHigh)
sngLow2 = LookUpExtensionTable(StrandSize, (Span - 2) / 2,
SlabBeamConst, bytLow)
sngHigh2 = LookUpExtensionTable(StrandSize, (Span - 2) / 2,
SlabBeamConst, bytHigh)
'linear interpolation to get span in odd number, ie 5, 7, 9,
11
sngLow3 = (sngLow1 + sngLow2) / 2
sngHigh3 = (sngHigh1 + sngHigh2) / 2
sngIntermediateLength = ((sngHigh3 - sngLow3) *
sngLengthDifference
+ sngLow3) * bytDoubleLive
ElseIf WorksheetFunction.RoundUp(Span, 0) Mod 2 = 1 Then
'eg,
4.1,6.7,
Span = WorksheetFunction.RoundUp(Span, 0) - 1
sngLow3 = LookUpExtensionTable(StrandSize, Span / 2,
SlabBeamConst,
bytLow)
sngHigh3 = LookUpExtensionTable(StrandSize, Span / 2,
SlabBeamConst,
bytHigh)
sngIntermediateLength = ((sngHigh3 - sngLow3) *
sngLengthDifference
+ sngLow3) * bytDoubleLive
Else
CableExtension = "Error: Calculating extension."
End If
'''''\\\\\\\\\\\\\\\\\\\\\\\\\\'''''
'''''Checking for Pan and double live'''''
CableExtension = ExtensionPan(Pan1, StressEnd1, StressEnd2, Pan2,
sngIntermediateLength)
'''''\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\'''''
''''''''''\\\\\\\\\\\\\\\\\\\''''''''''
End Function