Workbook data Names in VBA are string only?

BobBridges · #3 01-27-2014, 09:59 PM

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.

Code:

rm = WFMatch(Mdl.Value, so.Columns(1))

(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}:

Code:

  Staff = so.Cells(rm, cnP).Value * NN("SalPilot") _
        + so.Cells(rm, cnCr).Value * NN("SalCrew") _
        + so.Cells(rm, cnM).Value * NN("SalMech") _
        + so.Cells(rm, cnCl).Value * NN("SalCln") _
        + so.Cells(rm, cnCh).Value * NN("SalChk") _
        + so.Cells(rm, cnS).Value * NN("SalSpv")

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:

Code:

value = ThisWorkbook.Names("SalPilot").Value

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Way to search for a string in text file, pull out everything until another string?	omahadivision	Excel Programming	12	11-23-2013 12:10 PM
Replace contents form 1 workbook with another based on simular data	shabbaranks	Excel	1	12-05-2012 11:11 PM
macro to transfer data from one workbook to another workbook	virsojour	Excel Programming	5	02-01-2011 08:58 PM
inserting a string of data into an MS Word table???	matto	Word VBA	0	07-16-2010 09:35 AM
How do I merge data from one sheet in a workbook out into multiple sheets	nolesca	Excel	4	06-07-2010 08:13 AM