How to access cell values in VBA Function using Range parameter

**HooGolfer** · 07-04-2012, 08:37 AM

Newbie question:
I am writing a VBA function to work on a range of cells (the first time I'm passing a parameter of type Range). I can't figure out the proper syntax to get the values of the cells in that range. The code looks like:

Please Login or Register  to view this content.

...

I have used the Watch window to show the contents of c and c.Value2. Under c, I see Value2 = 5.02e-04 (the value I want to retrieve), but c.Value2 is shown as Empty and SwimTime = 0. How can I retrieve the values of the cells in the Times range within the function?
Thanks,
Steve

**Steffen Thomsen** · 07-04-2012, 09:33 AM

May i ask what the goal of the function is?

Do you need to get back an array of values or a single value?
Is the range input a single column range or a multi column range?

**HooGolfer** · 07-04-2012, 03:32 PM

The goal of the function is to reduce a set of swimmer's times (1 to 7 values) to a single value. The other argument to the function is the algorithm to use (pick latest, pick the fastest, average or weighted average).

**HooGolfer** · 07-04-2012, 04:14 PM

Apparently c.Value2 was correct. I had put a breakpoint in the macro and when tracing it I got the "Empty" and 0 results in the original note. When I removed the breakpoint, it ran correctly. I'm guessing that Excel called the function multiple times. The first call, the inputs were not all complete (they come from other worksheet functions). I'm guessing that eventually all the inputs get calculated and the function is executed again ... at which time it returns the data I expected. I'm not sure why the c Watch showed the proper value and the c.Value2 Watch showed a different value though.