VBA function issue with Excel

[Palmbak]

I'm using Excel 2000, in the background I'm using VBA to populate data/functions into my spreadsheets.Every function works and shows the right result however one user defined
function show only "#NAME?" instead of expected result. When I click on the cell the function "=+calculateNasdRegChargePercent(Z4)" appears and once I enter through it, it then calculates the right formula, in excel you have Calculation features that allow you to either manually calculate or automatically, but since I'm using VBA, I'm replicating the same process on the back-end. According to everything I know and have read, it should work, perhaps I'm missing something, can someone help.

Can anybody help me to force the function to evaluate itself without manual interaction using VBA. I already used Calculate function with partial success.

Thanks!!


Jon Palmbak
Senior Business Consultant

http://www.metlife.com

 

[PHV]

Why not post the VBA code populating the cell with the formula ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Jon,

How do you expect a cogent answer, if you have not provided the VBA code to evaluate?

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[Palmbak]

the code goes from upper row (rCrpFlsHmCellFT(iFT).Row + 1)
to the lower bound row (rCrpFlsEndCellFT(iFT).Row - 1)
and inserts the function calculateNasdRegChargePercent with the reference to the other cell

>>Code

For iCnt = rCrpFlsHmCellFT(iFT).Row + 1 To rCrpFlsEndCellFT(iFT).Row - 1
rCrpFlsHmCellFT(iFT).Offset(iCrpFlsTrgtRwOffsetFT(iFT), CORP_FAILS_TAB_COL_HEADERS.NasdRegCharge).Value = _
calculateNasdRegChargePercent(rCrpFlsHmCellFT(iFT).Offset(iCrpFlsTrgtRwOffsetFT(iFT), _
CORP_FAILS_TAB_COL_HEADERS.DaysToMaturity).Value)
iCrpFlsTrgtRwOffsetFT(iFT) = iCrpFlsTrgtRwOffsetFT(iFT) + 1
Next

>>Function

Public Function calculateNasdRegChargePercent(iMaturityDays As Single) As Single
Dim snMaturityYears As Single
Dim snPercent As Single
snMaturityYears = iMaturityDays / 360
Select Case snMaturityYears
Case Is < 0.25
snPercent = 0
Case Is < 0.5
snPercent = 0.005
Case Is < 0.75
snPercent = 0.0075
Case Is < 1
snPercent = 0.01
Case Is < 2
snPercent = 0.015
Case Is < 3
snPercent = 0.02
Case Is < 5
snPercent = 0.03
Case Is < 10
snPercent = 0.04
Case Is < 15
snPercent = 0.045
Case Is < 20
snPercent = 0.05
Case Is < 25
snPercent = 0.055
Case Is >= 25
snPercent = 0.06
End Select
calculateNasdRegChargePercent = snPercent
End Function


Jon Palmbak
Assistant Controller

http://www.tullettprebon.com

 

[SkipVought]

Your logic is off. Step thru and see what's happening.

You'd be better off with a lookup table, using a less than match...
[tt]
=match(lookupval,lookuprange,1)
[/tt]
1 or -1 match tyep, depending on the sort of the table


Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[firefytr]

Use a LOOKUP() function. If need be, setup a table manually.

Any function will show the #NAME error when it cannot be found. If you are using it from within a workbook (i.e. worksheet formula) the code must either be located in that workbook or you must reference where it is (i.e. "=Personal.xls!FunctionName()").

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci