Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public function returns error intermitently
Hi, I have the following Public Function in a non-object module, and for the
most part it works perfectly. However, when I run a macro (or something else happens that I can't define just yet) the cells using the function return #VALUE (when they previously had the correct values in them - and nothing else to do with them has changed. Simply copy & paste the cel with the function in it does nothing. I have to actually copy & paste the formula for the cell in the formula bar at the top and then the value returns to normal. The code is: Public Function FX_EXISTING(num) Dim cnt As Integer, cntfx As Integer, fx_pos(10) As Integer cnt = 1: cntfx = 0 Do Until cntfx = num If Range("FX.REFI").Offset(cnt, 0) 0 Then cntfx = cntfx + 1 fx_pos(cntfx) = cnt End If cnt = cnt + 1 If cnt 10 Or cntfx = num Then: Exit Do Loop If cntfx = num Then: FX_EXISTING = Range("FX.REFI").Offset(fx_pos(num), -23).Value 'Sheets("LOANS").Calculate End Function I had a suspicion that the Calculate at the end may have mad it play up so I commented that out, but it didn't help (it's probably not necessary anyway). Regards, Brett |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public function returns error intermitently
Because your function refers to cells that are not in the argument list
excel does not know when to recalculate the function. Try adding Application.Volatile to your function so that it will recalculate at each Excel recalculation. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Brettjg" wrote in message ... Hi, I have the following Public Function in a non-object module, and for the most part it works perfectly. However, when I run a macro (or something else happens that I can't define just yet) the cells using the function return #VALUE (when they previously had the correct values in them - and nothing else to do with them has changed. Simply copy & paste the cel with the function in it does nothing. I have to actually copy & paste the formula for the cell in the formula bar at the top and then the value returns to normal. The code is: Public Function FX_EXISTING(num) Dim cnt As Integer, cntfx As Integer, fx_pos(10) As Integer cnt = 1: cntfx = 0 Do Until cntfx = num If Range("FX.REFI").Offset(cnt, 0) 0 Then cntfx = cntfx + 1 fx_pos(cntfx) = cnt End If cnt = cnt + 1 If cnt 10 Or cntfx = num Then: Exit Do Loop If cntfx = num Then: FX_EXISTING = Range("FX.REFI").Offset(fx_pos(num), -23).Value 'Sheets("LOANS").Calculate End Function I had a suspicion that the Calculate at the end may have mad it play up so I commented that out, but it didn't help (it's probably not necessary anyway). Regards, Brett |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public function returns error intermitently
Thank you Charles, in the interim I stumbled across that in a reply to
another post in 2005 and it seems to work. It may even solve that non-triggering of a worksheet change event that I've been griping about - I'll be testing that shortly. Regards, Brett "Charles Williams" wrote: Because your function refers to cells that are not in the argument list excel does not know when to recalculate the function. Try adding Application.Volatile to your function so that it will recalculate at each Excel recalculation. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Brettjg" wrote in message ... Hi, I have the following Public Function in a non-object module, and for the most part it works perfectly. However, when I run a macro (or something else happens that I can't define just yet) the cells using the function return #VALUE (when they previously had the correct values in them - and nothing else to do with them has changed. Simply copy & paste the cel with the function in it does nothing. I have to actually copy & paste the formula for the cell in the formula bar at the top and then the value returns to normal. The code is: Public Function FX_EXISTING(num) Dim cnt As Integer, cntfx As Integer, fx_pos(10) As Integer cnt = 1: cntfx = 0 Do Until cntfx = num If Range("FX.REFI").Offset(cnt, 0) 0 Then cntfx = cntfx + 1 fx_pos(cntfx) = cnt End If cnt = cnt + 1 If cnt 10 Or cntfx = num Then: Exit Do Loop If cntfx = num Then: FX_EXISTING = Range("FX.REFI").Offset(fx_pos(num), -23).Value 'Sheets("LOANS").Calculate End Function I had a suspicion that the Calculate at the end may have mad it play up so I commented that out, but it didn't help (it's probably not necessary anyway). Regards, Brett |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public function returns error intermitently
Check your other post.
When you're going to multipost similar messages or just want to supersede your previous message with a new message, you should either post in the same thread--or at least post a reply to the initial post telling people not to waste time responding to that message. Brettjg wrote: Hi, I have the following Public Function in a non-object module, and for the most part it works perfectly. However, when I run a macro (or something else happens that I can't define just yet) the cells using the function return #VALUE (when they previously had the correct values in them - and nothing else to do with them has changed. Simply copy & paste the cel with the function in it does nothing. I have to actually copy & paste the formula for the cell in the formula bar at the top and then the value returns to normal. The code is: Public Function FX_EXISTING(num) Dim cnt As Integer, cntfx As Integer, fx_pos(10) As Integer cnt = 1: cntfx = 0 Do Until cntfx = num If Range("FX.REFI").Offset(cnt, 0) 0 Then cntfx = cntfx + 1 fx_pos(cntfx) = cnt End If cnt = cnt + 1 If cnt 10 Or cntfx = num Then: Exit Do Loop If cntfx = num Then: FX_EXISTING = Range("FX.REFI").Offset(fx_pos(num), -23).Value 'Sheets("LOANS").Calculate End Function I had a suspicion that the Calculate at the end may have mad it play up so I commented that out, but it didn't help (it's probably not necessary anyway). Regards, Brett -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public function returns error intermitently
Hi Dave, yes sorry about that. I couldn't remember what precise question name
was (and I forgot to tick notify replies), and this site is so pedantic about that (even to the point of caps v lower case). Even looking for recent posts by myself is pretty useles - it misses heaps of recent stuff and gives me stuff I asked about 3 years ago. I'll check your reply after some badly needed fuel. Regrds, Brett "Dave Peterson" wrote: Check your other post. When you're going to multipost similar messages or just want to supersede your previous message with a new message, you should either post in the same thread--or at least post a reply to the initial post telling people not to waste time responding to that message. Brettjg wrote: Hi, I have the following Public Function in a non-object module, and for the most part it works perfectly. However, when I run a macro (or something else happens that I can't define just yet) the cells using the function return #VALUE (when they previously had the correct values in them - and nothing else to do with them has changed. Simply copy & paste the cel with the function in it does nothing. I have to actually copy & paste the formula for the cell in the formula bar at the top and then the value returns to normal. The code is: Public Function FX_EXISTING(num) Dim cnt As Integer, cntfx As Integer, fx_pos(10) As Integer cnt = 1: cntfx = 0 Do Until cntfx = num If Range("FX.REFI").Offset(cnt, 0) 0 Then cntfx = cntfx + 1 fx_pos(cntfx) = cnt End If cnt = cnt + 1 If cnt 10 Or cntfx = num Then: Exit Do Loop If cntfx = num Then: FX_EXISTING = Range("FX.REFI").Offset(fx_pos(num), -23).Value 'Sheets("LOANS").Calculate End Function I had a suspicion that the Calculate at the end may have mad it play up so I commented that out, but it didn't help (it's probably not necessary anyway). Regards, Brett -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public function returns error intermitently
As a general design guideline, you should always pass as parameters
all of the cell that are used within a user defined function. You should never refer to cells that are not passed in as parameters. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 8 Apr 2009 04:00:01 -0700, Brettjg wrote: Thank you Charles, in the interim I stumbled across that in a reply to another post in 2005 and it seems to work. It may even solve that non-triggering of a worksheet change event that I've been griping about - I'll be testing that shortly. Regards, Brett "Charles Williams" wrote: Because your function refers to cells that are not in the argument list excel does not know when to recalculate the function. Try adding Application.Volatile to your function so that it will recalculate at each Excel recalculation. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Brettjg" wrote in message ... Hi, I have the following Public Function in a non-object module, and for the most part it works perfectly. However, when I run a macro (or something else happens that I can't define just yet) the cells using the function return #VALUE (when they previously had the correct values in them - and nothing else to do with them has changed. Simply copy & paste the cel with the function in it does nothing. I have to actually copy & paste the formula for the cell in the formula bar at the top and then the value returns to normal. The code is: Public Function FX_EXISTING(num) Dim cnt As Integer, cntfx As Integer, fx_pos(10) As Integer cnt = 1: cntfx = 0 Do Until cntfx = num If Range("FX.REFI").Offset(cnt, 0) 0 Then cntfx = cntfx + 1 fx_pos(cntfx) = cnt End If cnt = cnt + 1 If cnt 10 Or cntfx = num Then: Exit Do Loop If cntfx = num Then: FX_EXISTING = Range("FX.REFI").Offset(fx_pos(num), -23).Value 'Sheets("LOANS").Calculate End Function I had a suspicion that the Calculate at the end may have mad it play up so I commented that out, but it didn't help (it's probably not necessary anyway). Regards, Brett |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public function returns error intermitently
THIS POST IS NO LONGER REQUIRED
"Brettjg" wrote: Hi Dave, yes sorry about that. I couldn't remember what precise question name was (and I forgot to tick notify replies), and this site is so pedantic about that (even to the point of caps v lower case). Even looking for recent posts by myself is pretty useles - it misses heaps of recent stuff and gives me stuff I asked about 3 years ago. I'll check your reply after some badly needed fuel. Regrds, Brett "Dave Peterson" wrote: Check your other post. When you're going to multipost similar messages or just want to supersede your previous message with a new message, you should either post in the same thread--or at least post a reply to the initial post telling people not to waste time responding to that message. Brettjg wrote: Hi, I have the following Public Function in a non-object module, and for the most part it works perfectly. However, when I run a macro (or something else happens that I can't define just yet) the cells using the function return #VALUE (when they previously had the correct values in them - and nothing else to do with them has changed. Simply copy & paste the cel with the function in it does nothing. I have to actually copy & paste the formula for the cell in the formula bar at the top and then the value returns to normal. The code is: Public Function FX_EXISTING(num) Dim cnt As Integer, cntfx As Integer, fx_pos(10) As Integer cnt = 1: cntfx = 0 Do Until cntfx = num If Range("FX.REFI").Offset(cnt, 0) 0 Then cntfx = cntfx + 1 fx_pos(cntfx) = cnt End If cnt = cnt + 1 If cnt 10 Or cntfx = num Then: Exit Do Loop If cntfx = num Then: FX_EXISTING = Range("FX.REFI").Offset(fx_pos(num), -23).Value 'Sheets("LOANS").Calculate End Function I had a suspicion that the Calculate at the end may have mad it play up so I commented that out, but it didn't help (it's probably not necessary anyway). Regards, Brett -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF(AND(OR function returns #VALUE! error | Excel Worksheet Functions | |||
Function Error, Returns 0 | Excel Programming | |||
Find function returns the #VALUE! error value | Excel Discussion (Misc queries) | |||
public Function Error Excel 2003 | Excel Programming | |||
sumproduct function returns #value or #ref error | Excel Worksheet Functions |