Wrong function results

[madsteeve]

I am working on excel with multi sheets and I have created a test.bas file which contains a simple function such as

Please Login or Register  to view this content.

I go to VBA window and I import the file as module. When I import it at a new worksheet the results are correct. When I import the module at my current worksheet (multi sheets) the value which I get from Test is wrong

eg. New blank worksheet: A1=something, B1=11, C1=1.5, D1=51.5 then =Test(A1;B1;C1;D1) ----> correct value 1648.5
old worksheet (multi sheets):same as above -----> wrong value 580.5

Values A1...D1 at 2 excel files are the same!

What am I doing wrong?

[MrShorty]

68-43.3*11+712*1.5+19.2*51.5=1648.5

68-43.3*11+712*0+19.2*51.5=580.5

I would guess that something is happening to C1 so that it is being seen as 0 -- either a number stored as text or something like that. When you step through the function with a watch for Var3, what does it show as the value for Var3?

[madsteeve]

When I press fx button, it show Var3 : C1 =1.5
The data for A..D columns has been filled from a third person. C column has only numbers. I have deleted 1.5 and retyped it, but with no luck. You are right about C1*0, when I change 1.5 to eg. 1000 it keeps calculate 580.5 (when i change B or D the result changes) How can this be resolved?

P.S. Sheet2...sheet10 have other values (strings or numbers). I only want sheet1 A...D cell values.

[JosephP]

if you put a breakpoint in your imported code is that function actually called?

[madsteeve]

if you put a breakpoint in your imported code is that function actually called?

Function is working and is called from worksheet. I am getting a dynamic result, but is wrong. As it said above, C values are seen as zeros, although excel displays them correct at function arguments.

[JosephP]

are you sure it is the correct version of the function that is being called? if you put a breakpoint in the function you can step through to see what is happening

[madsteeve]

Does VBA editor auto save changes directly to function files? I have typed a characted by mistake at vba window which changed function arguments and auto saved to file. I am sorry for the inconvenience. Thanks a lot to all of you.
How can I mark this thread as solved or..... erase it ?

[JosephP]

click 'thread tools' at the top of the page then mark solved :-)

[MrShorty]

Just thought I would add, this kind of thing is why most people suggest using Option Explicit in your modules (http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx see using Option Explicit about 3/4 of the way down). With Option Explicit, when you accidentally change a variable name to something undeclared, the compiler will throw an error which should, hopefully, make it fairly easy to see that you have accidentally typed in an incorrect variable name.