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?

Bob,
You have been an amazing help to everyone on this forum. I would be honored to try and help since I have a little bit of an idea of what it might be. could you post a sample worksheet so we can see the name values and hopefully be able to get you an answer.

Wow, excelled, what a kind thing to say! Ok, here's the workbook.

I've just started playing a Facebook game called "Airline Manager". Some of the calculations were a little complex, and I decided that rather than looking up some of the more stable values in a separate worksheet I'd try using the Name manager this time. Here's a map to one example of the problem:

1) In the Market worksheet I collect raw data on each type of aircraft. Out at the right, in columns 11 through 16, are the number of workers I'd have to hire to serve that airplane. The Airbus A300-600, for example (on line 2 of the Market worksheet) is a big passenger plane that requires 2 pilots, 9 on-board attendants, 5 mechanics, 5 clean-up crew, 5 more doing check-in, and 2 supervisors.

2) In the name manager are some values showing what I pay each position. SalChk, for example, shows that I pay check-in crew members $400/day. SalPilot is currently $525/day, and so on.

3) The Market worksheet, as I said, is where I collected the raw data on each airplane. But I decide which ones I want to buy for my hypothetical airline ("TeacherSwift Air") using the analyses on the Aircraft tab. Take a look at column 15, headed "Staff"; each cell in that column passes the model name from col 1 to a worksheet function named "Staff", which calculates the total salary paid daily to maintain one aircraft of that model. The Aerospatiale ATR 42-500, for example, on line 3 of the Aircraft worksheet, costs $3500/day in crew salaries.

4) The worksheet functions are in a code module named Functions. You'll find the Staff function there.

5) The Staff function starts by getting the argument, the aircraft model, and finding what row it's on in the Market worksheet.(You can find WFMatch in the mCommon code module, if you want to be sure it works right. But it does.)

6) Then the Staff function calculates the total salary by multiplying {the count for each type of crew member} by {the salary for that crew type}:7) NN is a function that gets the value from the name manager for the given label. You'll find it in the mCommon code module, and now we've come to the problem. What I expected to be able to do for each Name is something like this:But what I always get when I do that is not the numeric value 525, but the string value "=525".

So if you look at NN, what it actually does is get that string value, then do a Mid(string, 2) on it to cut out the '=', then add 0 to it to convert it to a numeric value, and finally return it to the calling routine.

I gotta believe that isn't necessary; there must be a way to get the numeric value directly. But if so, I haven't figured it out yet.

Attached Files

Airline Manager.xlsm (201.7 KB, 10 views)

Wow this was certainly informational. I am sure you thought of this solution but I will post what I would do so others reading this forum can see.

I don't think there is a way to get just the integer value from a name since I believe a name is always a string. This can be verified by trying a Shape Name. I tried entering a shape with the name 6 but it still goes to "6". I think that names have to be strings so they can be referred to in any MS program. Similar to if you use a mid formula on group of numbers it now becomes a string and you have to add 0 to it to bring it back to a number. Good ol' data types.

Here is the solution I would suggest that you could put at the beginning of your Sub and then you can use the same variable name throughout your code.

By the way this same thing can be achieved using mid and Cstr, or replace and + 0 it looks like there are lots of ways but it does not look like a name can have just a number value since it will always add on that equals sign to make it a string.

If anybody else finds out different please let us all know.

Thanks

Hi Bob,
Have you tried this:
Or:

LOL. I remember a comic translation of womamspeak into manspeak and vice versa. One of the entries was a woman saying "You certainly are...manly." The translation is "you're sweating and you smell a lot". I get the feeling that "informational" may mean "boy, you sure churned out a lot of words, there".

Yeah, of course the way you suggested is the obvious way to do it. But I have a sort of mania about putting frequently-used routines inside smaller routines, to make them easier to use; and I not only call NN five times in this function, but I also call in from the function immediately above. As soon as I saw I needed to do it more than once, that was it; I assumed I'd want it again in yet other routines, sooner or later, so I put it in a separate function.

I'd never even heard of it. And now that I look it up, it seems to me I've been ignoring the methods of the Application object far too long. I think I have some reading and experimenting to do. Thanks, Catalin; I don't know that I'll end up using it—what I have now seems to work—but even if I don't, I'll have learned about Evaluate and those other useful-looking methods and will probably use some of them on another occasion.

Hey Bob,

I didn't intend for "informational" to sound the way it came it out. I was just surprised how the naming conventions worked and how they are rendered in VBA so I thought it was "informational".

@ Catilin the evaluate function looks like it can do quite a bit. Thank you for bringing it up.

Excelled, don't take me too seriously; I didn't assume that's the way you meant it, I just thought it sounded funny. It was self-mockery more than anything else.