Simple polynomial produces error ONLY for a certain input, otherwise works

**anna86** · 04-14-2014, 10:04 AM

Dear All,

I'm new here, and new to Visual Basic, (but having a background in C and C++).
Sorry if my question is foolish, but I'm really puzzled by the problem I'm facing now.

I'm trying to write a simple user defined function, "calctemp(sensor, resistance As Double) As Double" below, which calculates a polynomial to the 6th degree
It should take the 7 coefficients of this polynomial (K_1 ... K_7) from cells in the same row as input cell "sensor" (with offset +1, +2... +7 to the right from that input cell).
And "resistance" is the variable taken with double precision, as the second input.

I wrote the code below. And it worked nicely, with the desired precision.
But, if I input resistance = 2774.0
it gives #VALUE! error. Regardless from the polynomial coefficients! Regardless from anything else, seemingly, except for the power of the polynomial.
This error occurs at power 5, and present above.
I don't know what's so special about this number 2774.0, already 2774.000001 gives a numerically correct result. Other round numbers (3300.0, whatever) work too, and I did not find other input values failing so far.

Am I doing something incorrect in the code? I'm not sure my usage of "sensor.Offset(0, 1).Value" is the recommended way to select cell values next to the input cell (in principle, I would think the input "sensor" would need a type, like "As Address", to be sure we are referring to the cell address, and shifting that with the .Offset(,) method) But again, this part seems to work...
I would be glad for any debugging ideas!
I'm using Excel for Mac 2011.

PHP Code:




Function calctemp(sensor, resistance As Double) As Double



    Dim res As Double

    

    Dim K_1 As Double, K_2 As Double, K_3 As Double, K_4 As Double

    Dim K_5 As Double, K_6 As Double, K_7 As Double

    

    res = 1000 / resistance

    K_1 = sensor.Offset(0, 1).Value

    K_2 = sensor.Offset(0, 2).Value

    K_3 = sensor.Offset(0, 3).Value

    K_4 = sensor.Offset(0, 4).Value

    K_5 = sensor.Offset(0, 5).Value

    K_6 = sensor.Offset(0, 6).Value

    K_7 = sensor.Offset(0, 7).Value

    

    calctemp = K_1 + K_2 * res + K_3 * res ^ 2 + K_4 * res ^ 3 + K_5 * res ^ 4 + K_6 * res ^ 5 + K_7 * res ^ 6

        

End Function

**MrShorty** · 04-14-2014, 10:35 AM

I don't see anything syntactically wrong with the code. Have you tried stepping through the function one step at a time? http://office.microsoft.com/en-us/su...819.aspx?CTT=1 By stepping through, hopefully you can identify the statement that is causing the error and note the values of the different variables and identify the source of the error.

Will it calculate correctly in the spreadsheet? The final function is relatively simple (a single =SERIESSUM() function should do it). I don't know how diagnostic it will be, but it might be interesting to know if Excel's native function has the same problem with the same input or not. (It also might suggest an alternative to using the VBA UDF, and we can sidestep the problem completely).

**anna86** · 04-14-2014, 01:55 PM

Thank you very much for your reply! As for the debugging, I can insert breakpoints, but Debug -> Step Into just simply has no effect at all. Could not figure out why. (Excel for Mac 2011 14.0.0) I start to think something is broken (with me, or with my excel

)
I send an example spreadsheet in attachment, if you have some time & interest to look at the problem.

There is nothing wrong with mathematical formula, the same expression calculates the numerical values nicely. I plot this in row J in the example,where I don't use predefined functions, just basic expressions directly in the spreadsheet and usual references to the cells.

Next to it in row K you can see the output of the function calctemp(sensor,resistance) (code in Module1), which is supposed to operate with the same input values.
Indeed, for most of the "resistance" inputs (row I in example) it gives the very same result as the direct expression. But the function fails if resistance = 2774.0 (regardless from the "sensor" input).

I'm quite confused...

**MrShorty** · 04-14-2014, 02:29 PM

I cannot seem to replicate the problem. The value errors are present when I open your file, but I enter the error cell, press F2 and enter (to recalculate that cell, or do something else to force it to calculate the function in the error cell), it displays the correct answer, and I cannot recreate the error value. It would appear to me that the error is something more sinister in your spreadsheet/installation and not a problem with the code.

**Solus Rankin** · 04-14-2014, 02:38 PM

in principle, I would think the input "sensor" would need a type, like "As Address", to be sure we are referring to the cell address, and shifting that with the .Offset(,) method

To this point, I think you're looking for Range, if you really want to give a datatype to the variable

Please Login or Register  to view this content.

**anna86** · 04-14-2014, 02:46 PM

Thanks a lot for trying it, MrShorty! Yes, I got this feeling that something is badly broken when the debugging tools did not work -- I just did not have another machine to try it.
Uh-huh. Well, I guess I check whether my institute has some upgrades, or I try to reinstall this one.
Interesting, I've never seen such a glitch before. Very Scary.

And thanks for the tip, Solus Rankin!

**MrShorty** · 04-14-2014, 04:04 PM

I find that "debug->step into" never works right for UDF's like this. This command just does not have any way of specifying the arguments (sensor and resistance in your case) for the function call when called from "debug -> step into". With UDF's like this, I find that the best way to get into "debug" mode is to set a break point somewhere in the function (often I will set the breakpoint on the function statement itself), then call the function from a spreadsheet cell.

**anna86** · 04-15-2014, 05:21 AM

Thanks for the tips! I upgraded: Excel for Mac, 14.3.0, and the glitch disappeared. However it makes me a bit nervous, I hope there aren't other (silent) numerical errors from broken VBA libraries or whatever.

This is scary stuff. Is Mac normally an OK-platform for programming Excel? ...

I got so excited, I was about transfer part of my data analysis to Excel when I realized yesterday you can program it 'easily', but this discouraged me a little.

**MrShorty** · 04-15-2014, 01:57 PM

I am not at all familiar with Excel for Mac -- I work exclusively with Windows machines. I have not had any similar problems with my UDF's. In general, I would say that Excel/VBA has been a fairly stable, reliable platform for programming many different types of data analysis. I can't explain this little hiccup you've experienced, but I would guess that it is a rather rare occurrence.

Simple polynomial produces error ONLY for a certain input, otherwise works

LinkBack

Thread Tools

Rate This Thread

Display

Simple polynomial produces error ONLY for a certain input, otherwise works

Re: Simple polynomial produces error ONLY for a certain input, otherwise works

Re: Simple polynomial produces error ONLY for a certain input, otherwise works

Re: Simple polynomial produces error ONLY for a certain input, otherwise works

Re: Simple polynomial produces error ONLY for a certain input, otherwise works

Re: Simple polynomial produces error ONLY for a certain input, otherwise works

Re: Simple polynomial produces error ONLY for a certain input, otherwise works

Re: Simple polynomial produces error ONLY for a certain input, otherwise works

Re: Simple polynomial produces error ONLY for a certain input, otherwise works

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Simple Macro Works for Me but 1004 Error for User

[SOLVED] Excel produces Error when the code works

C# code works fine with Excel2007 but produces exception while running with Excel2003

[SOLVED] Input box cancel produces error

simple copy column works but ERROR!

Bookmarks

Bookmarks

Posting Permissions