GTISW shipbuilder released

[TheDS]

I've decided to go ahead and post the current version of my ship-building spreadsheet, so that it could be used by those of you chomping at the bit to make your own ships. The file was made using Excel and a large quantity of gadgets so that typing things in has been reduced to a minimum; you can build your whole ship without touching the keyboard.

In this thread I have asked a few questions, most of which went unanswered. In addition to still needing these questions answered, I could also use a little help in regards to the "directions for use". To me, the sheet is fairly obvious in how it works, except for a couple things. I put in lots of notes that can be read by hovering over them, but in the intro page, I tried to go over a few of the more obscure things, and even I realize I did a lousy job with those instructions. So, if you want, please let me know how to better do this.

Naturally, if there are any errors or problems, I'd like to know so I can fix them. My email address is in the sheet for particularly long or mundane issues, or if you want to make sweeping changes to the sheet and send me the changed sheet and give me a better idea what you're talking about. (Turn on Track Changes if you do this.)

Here's the link
If the download doesn't start right after you click this link, please read post #3, below. The failures I'm seeing on the server can be cured with the very first tip.

UPDATE: Try this new link, which leads to a public MSN folder. You have the choice of downloading the file zipped or not zipped. I may put other items there later, once I get verification from some of you that this link works as expected. Hopefully no one will have to create an MSN account to access these files. What you should see upon clicking this link is a picture of a folder behind a globe. Click that, and you will be taken into all the public folders. Right now, there's only the two files, but later, I may have more files for you to play with, and they may be in further folders. Please let me know if there's any problems with this.

[Urban]

I am unable to get the file.

[TheDS]

This may be because your browser is set to use Passive FTP, which I believe is the default setting of IE. To change this, follow this quick procedure:

In the TOOLS menu of your browser, choose "Internet Options..."

A window pops up. Choose the "Advanced" tab.

Scroll down about 1 page until you see "Use Passive FTP (for firewall and DSL modem compatibility)". This should be just above the "HTTP 1.1 settings" section.

Clear the check mark beside "Use Passive FTP (for firewall and DSL modem compatibility)".

Click OK at the bottom of the window.

Now the file should come up when you click the link in the first post. It pops up a new window briefly. If you have a pop-up blocker, it may be closing this window before it can download anything. if this is what the fault is, you will need to create an exception in your pop-up blocking software. Since SJ doesn't spam, you can create an exception for all of the sjgames website.

If you are still having problems, you may have some security settings on your browser interfering. IE7 users may suddenly be unable to do a number of other things, like online banking; if this is the case, I would pester my local computer buddy to help me, or pester MS if someone isn't available.

You might also have firewall software that is interfering, and as in the case of the pop-up blocker, you will need to adjust those settings on your own.

[ADDED] Since the transfer is through FTP, which uses Port 21, if you're downloading from an office or an otherwise secure or limited network (some coffee shop networks), Port 21 may be being blocked by the internet provider or network administrator. Unless the block is in your personal router/firewall. there's little you can do about this except try from some other place. [/ADDED]

Unless you have some hacker-buddy putting weird stuff on your computer for you, you should be aware of whether or not a software firewall is installed on your computer, and if it's the problem. If you're not sure, then the answer is probably "no".

If none of the above helps, PM me with a more detailed description of your problem and I'll see what I can figure out. (This goes for anyone, of course.) I am not having any problem with the link myself.

[ADDED] If you're using Windows 95, 98, or ME, you might not be able to get this file to download at all. Please PM me and we'll figure out another way to get the file to you. [/ADDED]

[Urban]

Thanks! Works now.

Most impressive work on that spreadsheet! Been toying with my own for reducing the manual calculations but yours is lightyears ahead of mine :)

[TheDS]

Thank you for the comments, always a pleasure to give someone more than they expect! Once you've gotten the chance to fiddle around with it, lemme know what you think.

I was about to tack on a note (before I saw your post) that I checked my server logs, and found numerous attempts to log in using passive mode, and that they were all failing, and was going to reiterate to those trying to download that they needed to set their browsers off of Passive mode. From what I can tell, the current settings DO allow it, but for whatever reason (any number of possible interferences) it only works in active mode.

[ElHombre]

Thanks for the work you've done. This makes building ships far easier for me. Any plans for adding TL12 options to it?

[TheDS]

Only if they have it Travellerized :D

While the sheet is modular enough that I could make substantial changes, it still depends on what they make available. GTISW made a number of changes in the way Traveller is visualized and in the way the "gaming" portion of it is run, to say nothing of the ship-building procedure. While I've made sheets for a few other versions of Traveller in the past, none but the most simple were completed because it was really just too complex. (I had a good one for TNE as finished as this one, but it was made on the Amiga, and couldn't port over.) I was very impressed with the simplicity of this design system, and knew that I could do it.

If you're wondering about a possible system for use with GURPS Vehicles, I seriously doubt I am up to that challenge; the full FF&S proved beyond my ability to program a sheet for (and I REALLY wanted to), and GURPS Vehicles 3e proved almost beyond my ability to even understand(!), so I'm not holding out any hope for GV4. I'll TRY, I'm sure, but I'm not holding out hope. I believe there will be a professional effort made anyway.

I don't know when, but I will be updating this file eventually, to "finish" it, even if I have to invent all my own answers to the questions I asked earlier.

Your feedback is greatly appreciated and very encouraging; thank you.

[DaltonS]

After working with this spreadsheet for a while, here are a few suggestions.

1. Get rid of the spinner controls. They may seem cool, but they clutter up the design section and don't copy well. (That is, the copies of all controls seem to remain linked to the cells on the original sheet rather than to the copies on the new one. Not good if you want to tweak an old design.)
2. It's never a good thing when a basic numeric data value is the product and/or sum of two or more data entry cells (such as a percentage and a units add-on, or two multipliers, plus a half unit from a checkbox). Rounding the value up, down or to the nearest half unit is acceptable, as is adding extra units to a calculated minimum, but each basic value should have only one input cell dedicated exclusively to it. If a formula is needed to calculate the value of that cell, one should be entered by the user. If a half unit is needed, the user can add ".5" to the entered number.
3. List controls should be replaced by list validated input cells. As each row should have only one such field, reserve a column (which should be hidden in the final version) exclusively for the list index of that value determined by the MATCH function. This way you don't have to LOOKUP an item name in the original data table every time you need it. The index cell can be referenced by the INDEX function to get values from other columns of the same table.
4. The output section doesn't have the same format as the example ship data blocks on TIW p.201-218. It should match if just for consistency. I've written a few Basic macros to simplify this.
   1. This function rounds "a" to "n" significant digits. In GURPS, "n" almost always equals 2, making this function useful in any GURPS related spreadsheet.
      

      Function SIGDIGIT(a,n)
      If a<>0 Then
      SIG=Abs(n)-Int(Log(a)/Log(10))-1
      SIGDIGIT=Int(a*10^SIG+0.5)/10^SIG
      Else
      SIGDIGIT=0
      End If
      End Function

   2. This function will return iName in its plural form. The following rules are used:
      • Words ending in "ch", "o", "s", "sh", "x", or "z", are returned with "es" added to the end.
      • Words ending in "f" or "fe" are returned with "f" or "fe" replaced by "ves".
      • Words ending in a consonant and "y" are returned with "y" replaced by "ies".
      • All other words are returned with "s" added to the end.
      Note that "iName" should always be provided in the singular form to all the following functions.
      

      Function PLURAL(iName As String) As String
      Dim ItemStr$
      ItemStr$=iName
      Select Case LCase(Right(iName,2))
      Case "ch","sh"
      ItemStr$=ItemStr$&"es"
      Case "fe"
      ItemStr$=Left(ItemStr$,Len(ItemStr$)-2)&"ves"
      Case "ay","ey","iy","oy","uy"
      ItemStr$=ItemStr$&"s"
      Case Else
      Select Case LCase(Right(iName,1))
      Case "o","s","x","z"
      ItemStr$=ItemStr$&"es"
      Case "f"
      ItemStr$=Left(ItemStr$,Len(ItemStr$)-1)&"ves"
      Case "y"
      ItemStr$=Left(ItemStr$,Len(ItemStr$)-1)&"ies"
      Case Else
      ItemStr$=ItemStr$&"s"
      End Select
      End Select
      If UCase(iName)=iName Then
      ItemStr$=UCase(ItemStr$)
      End If
      PLURAL=ItemStr$	
      End Function

   3. The final three functions produce quantified plural or singular elements for CONCATENATEd item lists. The first outputs a quantified item name, the second outputs an empty string for zero quantity items and a comma prefixed quantified item for non-zero ones, and the third PLURALIZEs the element in GURPS Vehicle Builder format (no quantity for single items, word quanitities for two to ten whole items and digits for other non-zero quantities).

      Function PLURALIZE(iQty, iName As String) As String
      PLURALIZE=LTrim(Str(iQty))&" "&IIf(iQty=1,iName,PLURAL(iName))
      End Function
      
      Function LISTITEM(iQty, iName As String) As String
      LISTITEM=IIf(iQty=0,"",", "&PLURALIZE(iQty, iName))
      End Function
      
      Function GVBITEM(iQty, iName As String) As String
      If iQty=0 Then
      GVBITEM=""
      Else
      Select Case iQty
      Case 1
      GVBITEM=iName
      Case 2,3,4,5,6,7,8,9,10
      GVBITEM=Choose(iQty-1,"two","three","four","five","six","seven","eight","nine","ten")&" "&PLURAL(iName)
      Case Else
      GVBITEM=PLURALIZE(iQty, iName)
      End Select
      End If
      End Function

I've done some tweaking along these lines myself, but I haven't tested it extensively yet. I hope this helps.

--

Dalton "the fustrated programmer (is there any other kind?)" Spence

[TheDS]

Thanks for taking a look at my work, Dalton. As you've noticed, I'm not exactly an expert. Your suggestions will be very useful once I get around to continuing development on this sheet (whenever that may be). Thank you for taking the time to help out. Let me get to commenting.

Your Design Suggestions

1. I used the spinners because when I use the sheet, I like to be lazy and not have to use the keyboard. This sheet requires almost NO touching the keyboard, until you want to enter text. Keying in values is still an option, though; sometimes we want big values. But as to your problem, I've never experienced this particular problem. Are you copying the whole sheet by copying the tab, or are you selecting parts of the sheet? The latter may be what's causing your problem, in which case, if copying the whole tab isn't going to work (like you need to copy just a portion of the ship), copy the cells with values. This might not work for every instance, but it should work for most.
2. Yeah, I agree; I should've done this better. It's on my list. However, I don't think what I have in mind will placate you. I needed a quick way to enter large or small values, and still have all digits be significant so people can have 10,234.5 units of something if they want. (And that's why I made it possible to simply type in values most of the time.) My original thinking was to have each digit have its own spinner (please don't pull your hair out), but that looked like too much work for all concerned.
3. This requires me to be smarter than I currently am. This is the part where I beg you to provide a working example so I can become that smart. (That is, I don't really understand how MATCH and INDEX work, especially in this example.)
4. This is actually not as easy as I thought it should be. First, I didn't like the organization in the book. It also looked too cluttered. (Don't say it.) What I really want is to put things into a tabular-type format, with nice neat columns for information, but I can't do that without creating an insane amount of empty space, and I don't know how to do a macro to figure out how to save space. So you get what you see.

Your Macros

1. I think I used Excel's built-in functions to do this. Your way would be less cluttered, and probably save file space, so I think I'll use it.
2. Cool!
3. If I'm understanding this right, this would work well with what I did for the lists of installed gear. I'll need to study it more to be sure of what it's doing.

All in all, your code examples have taught me a little bit today. Thanks! My Macro-fu isn't especially strong, but I did manage to write one some time ago to convert numbers to and from decibels. Conditionals are something I've been trying to figure out, and your examples have those, so you have definitely helped me.

[DaltonS]

Originally Posted by TheDS

1. I used the spinners because when I use the sheet, I like to be lazy and not have to use the keyboard. This sheet requires almost NO touching the keyboard, until you want to enter text. Keying in values is still an option, though; sometimes we want big values. But as to your problem, I've never experienced this particular problem. Are you copying the whole sheet by copying the tab, or are you selecting parts of the sheet? The latter may be what's causing your problem, in which case, if copying the whole tab isn't going to work (like you need to copy just a portion of the ship), copy the cells with values. This might not work for every instance, but it should work for most.

I did it both ways and got the same results. However, I use Open Office for my spreadsheets and not Excel, so that might be the problem. As to why you used spinners, I'm just the opposite; I started using computers in the early 70's, and somehow managed to stick with DOS until the late 90's, so I tend to prefer the keyboard to the mouse. ;)

Originally Posted by TheDS

2. Yeah, I agree; I should've done this better. It's on my list. However, I don't think what I have in mind will placate you. I needed a quick way to enter large or small values, and still have all digits be significant so people can have 10,234.5 units of something if they want. (And that's why I made it possible to simply type in values most of the time.) My original thinking was to have each digit have its own spinner (please don't pull your hair out), but that looked like too much work for all concerned.

The main thing I had against spinners was that they took up at least a quarter of the area of the input cell, so if you type a large number in, the first couple of digits end up under the spinner. Also, the attached notes tended to pop up anywhere, from beyond the edge of the window to right on top of the cell being used.

Originally Posted by TheDS

3. This requires me to be smarter than I currently am. This is the part where I beg you to provide a working example so I can become that smart. (That is, I don't really understand how MATCH and INDEX work, especially in this example.)

From my Open Office Calc function help file.

MATCH(search criterion;lookup_array;type)
Search Criterion is the value which is to be searched for in the single-row or single-column array.
lookup_array is the reference searched. A lookup array can be a single row or column, or part of a single row or column.
Type may take the values 1, 0, or -1. If Type = 1 or if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order. If Type = -1 it is assumed that the column in sorted in descending order. This corresponds to the same function in Microsoft Excel.
If Type = 0, only exact matches are found. If the search criterion is found more than once, the function returns the first one found. Only if Type = 0 can you search for regular expressions.
If Type = 1 or the third parameter is missing, the last value that is smaller or equal to the search criterion is returned. This applies even when the search array is not sorted. For Type = -1, the first value that is larger or equal is returned.

The value output by this function would be the row value in the one below.

INDEX(reference;row;column;range)
reference is a cell reference, entered either directly or by specifying a range name. If the reference consists of multiple ranges, you must enclose the reference or range name in parentheses.
row (optional) represents the row number of the reference range, for which to return a value.
column (optional) represents the column number of the reference range, for which to return a value.
range (optional) represents the index of the subrange if referring to a multiple range.

Originally Posted by TheDS

4. This is actually not as easy as I thought it should be. First, I didn't like the organization in the book. It also looked too cluttered. (Don't say it.) What I really want is to put things into a tabular-type format, with nice neat columns for information, but I can't do that without creating an insane amount of empty space, and I don't know how to do a macro to figure out how to save space. So you get what you see.

The trick is to put the sections 1 and 2 (Ship Design Creation and Tracking) at the top of the sheet with section 3 (the Completed Ship Sheet, with its own attached header) below them. And don't sweat the empty space.

Originally Posted by TheDS

Your Macros

1. I think I used Excel's built-in functions to do this. Your way would be less cluttered, and probably save file space, so I think I'll use it.
2. Cool!
3. If I'm understanding this right, this would work well with what I did for the lists of installed gear. I'll need to study it more to be sure of what it's doing.

All in all, your code examples have taught me a little bit today. Thanks! My Macro-fu isn't especially strong, but I did manage to write one some time ago to convert numbers to and from decibels. Conditionals are something I've been trying to figure out, and your examples have those, so you have definitely helped me.

I'm mainly a C++ programmer these days, so going back to BASICs was a bit awkward for me as well. ;)

--

Dalton "who is too tired to be witty ... again!" Spence