I'm not sure how to entitle this thread, nor how to google for the answer. I don't use names much in Excel, but I have a situation where it seems useful and I want my program to use it too.

Now, a name can refer either to a range (eg Market!C8) or to a value (2100). I see in VBA that I can get my program to use the range just fine, for exampleGreat, I can use that in a program. But when I want to use one that's just a scalar value, something curious happens. Maybe you already know what I'm going to say.

In my worksheet, for example, one formula refers to "RC5*RpPK". It works fine, multiplying the value in col 5 by 0.45 and displaying the numeric I want.

But in my VBA program, when I refer to Names("RpPK").Value, I get back a string "=.045". When I try adding 1 to it, I don't get 1.45, I get an error. I've tried various combinations, but all I get back is the string, not the value.

Now, this doesn't have to be a deal-breaker; I can always strip off the leading "=" and use a conversion function. But it seems silly; surely Excel has some method or property that just issues the numeric value rather than a string value. The worksheet has access to the numeric value; why wouldn't VBA?



Anyone know how to do this?