User defined function returning #VALUE!

This is in excel 2002

I created my own function that looks up values from other closed workbooks and sums them. The part that looks up the values from the other workbooks, gets the correct value... but in my active worksheet the cell value displays as #VALUE!

not sure what the problem is. any help is much appreciated!

this is a simplified version of the user defined function:

Please Login or Register  to view this content.

This is the function that actually looks up the value... got this online:

Please Login or Register  to view this content.

If the function is in another workbook then you will have to ref that workbook

e.g if in book1 it would look like

=Book1!GetCellValue(

VBA Noob

A couple of thoughts/questions:
SumOverWorkbooks()

• You have dim'd lTotal as Integer but the "L" indicates long, how big are the numbers you are expecting?
• You say that lTotal is in a loop; but, as written, the current value will be replaced by the new value. Should you have:
  lTotal =lTotal + GetCellValue...
• You have defined lTotal as Integer but, you are using CInt to convert the value to Integer(?)

Function GetCellValue(...

• If your file does not exist, the Fn returns a "...not found" string; but, SumOverWorkbooks() will try to assign the string to the integer variable iTotal

Finally, you are using CInt in several places. Do you know of a certainty that the values will ALWAYS be numeric? CInt will return #Value if not. Can you add code to test for numeric/non-numeric?

Thanks for your reply. Sorry about the looks of the code... i keep changing it around trying to get it to work. the "l" was for "local"... but i'm a java developer and that's my companies normal standards

for now i'm sure the values are always numbers. in the simple case i'm trying to get working let's assume that we're dealing with 1 file, 1 cell, and you know there's a number there. I thought it had to do with using the ExecuteExcel4Macro command so i tried a different method below but still no go! i had a msg point right before the end of the function and i KNOW that it has no problem getting the value from the other workbook... it just doesn't get displayed properly in the cell. "#VALUE!"

I also thought it might have had something to do with the datatype of the return value so that's when i started converting the value to an integer.

any help would be much much appreciated! maybe you can try creating 2 workbooks and pasting this code into a function. call the file 1.xls and see if it works.

thanks

Please Login or Register  to view this content.