GURPS Spaceships Design Spreadsheet

[ericbsmith]

Originally Posted by loofou

About the , and . issue: My windows is set up to UK settings, but my keyboard uses the German layout. I had problems in the past with the decimal points being commas, so I set my region to UK with decimal dots. It might still be related, though, I'll investigate further on another machine with complete native UK settings for everything and see if this changes anything.

In the sheet you uploaded I see multiple places where a text string conversion of a number which should be outputting a "0" is outputting a ",0" which makes me seriously suspect regional setting issues. Most of those text strings are set up to output numbers as text in the "#,##0" format, so with a regional separator of a comma it's going to interpret that comma as the decimal separator and output ",0"

[loofou]

Originally Posted by ericbsmith

In the sheet you uploaded I see multiple places where a text string conversion of a number which should be outputting a "0" is outputting a ",0" which makes me seriously suspect regional setting issues. Most of those text strings are set up to output numbers as text in the "#,##0" format, so with a regional separator of a comma it's going to interpret that comma as the decimal separator and output ",0"

Interesting, because I double checked my regional settings and everything is indeed set up as UK and the decimal dot is specifically set. If I want to write a correct decimal number in any program, I need to type the dot and can't use my german keyboard layout number pad comma.

But funny enough my second PC has the exact same problem with the sheet and that does not even have a german kb layout. Everything was set to UK from the beginning.

Maybe Excel has it's own regional settings somewhere, I need to check this.

[EDIT] Indeed Excel has it's own settings and for some reason those were set to really weird values. I don't know how I never noticed that! I set it to the correct values and most of the problems with the sheet are gone. I tested the RC10 and could use it quite nicely. Now we still need to figure out why the macros throw errors after RC 10 (need to confirm later which update it was exactly that broke the macros)

[ericbsmith]

Originally Posted by loofou

Maybe Excel has it's own regional settings somewhere, I need to check this.

[EDIT] Indeed Excel has it's own settings and for some reason those were set to really weird values. I don't know how I never noticed that! I set it to the correct values and most of the problems with the sheet are gone.

Yup. For anyone else that's looking to go Excel Options -> Advanced -> under Editing Options there's a checkbox for Use System Separators. You'd need to either check the box if it's using something odd or uncheck the box and set the two separators to . for decimal and , for thousand.

Originally Posted by loofou

I tested the RC10 and could use it quite nicely. Now we still need to figure out why the macros throw errors after RC 10 (need to confirm later which update it was exactly that broke the macros)

I've done some significant updates in the macros since RC10, almost completely rewriting them. If you let me know what the specific error is and when it's coming up I may be able to figure something out.

[ericbsmith]

Version 2.0 Release Candidate 17
-A couple minor bug fixes
-Added a rudimentary Regional Setting detection to the Office 2007 version.
--This checks the Regional Settings for Excel itself the first time the file is opened and if you are not using custom settings which set a . as the decimal separator and , as the thousand separator it offers a dialog to set these for you (this means even if you are using Windows Default Settings, and Windows Default Settings use these, you'll still get the dialog; AFAIK there is no way for me to check the Windows Default Settings to suppress the dialog).
--This dialog only shows up the first time you open the file and only if it detects you are not using the compatible settings. "First time" resets if you use Save As to save the file; the next time you open it after Saving As will be treated as the first time.

You can download the spreadsheet here:

Excel 2000 (RC17)
http://gurpsland.no-ip.org/zip/SpaceshipsV2.zip (5.1 MB)
http://gurpsland.no-ip.org/zip/SpaceshipsV2.7z (~2.5 MB)

Excel 2007 (RC16)
http://gurpsland.no-ip.org/zip/SpaceshipsV2-2007.zip (~6.3 MB)
http://gurpsland.no-ip.org/zip/SpaceshipsV2-2007.7z (~6.2 MB)

OpenOffice 3.3.0 (RC9)
http://gurpsland.no-ip.org/zip/SpaceshipsV2-Ooo.zip (~2.1 MB)
http://gurpsland.no-ip.org/zip/SpaceshipsV2-Ooo.7z (~1.9 MB)

[loofou]

Originally Posted by ericbsmith

If you let me know what the specific error is and when it's coming up I may be able to figure something out.

I recorded a small video going through all the options where I remembered some errors appeared. I think I found two or three different ones and some other minor bugs. I used the second-to-latest version (the one before you uploaded the new update just now).

I hope it helps in finding the errors: https://www.dropbox.com/s/c85b673hs5...video.mp4?dl=0

[ericbsmith]

I just uploaded another version of v2.0RC17. This one, I hope, will fix the issue where the Save Sheets aren't showing any ships in them. This should also fix some of the issues you had with the Load Next Ship button, since that relies on loading the next visible ship in line from the target Save Sheet as well as the issue with the Save Ships portion of the Output sheet.

If not can I get you to Save the sheet and upload it again so I can suss out more errors?

[loofou]

Yes, the ship lists seem to work now (but the filters are not, or I am using them wrong, I don't know). Systems popup is still empty and some of the error messages still appear. The HTML output column is also still broken and clicking the Design Table macro button brings me to the Unofficial Ships list.

I also resaved the file: https://www.dropbox.com/s/hloc490hw3...Copy.xlsm?dl=0

We are getting there :)

[ericbsmith]

Originally Posted by loofou

Systems popup is still empty and some of the error messages still appear.

What do you mean "systems popup" - do you mean the the drop-down menus on the design table for selecting Ship Systems?

Originally Posted by loofou

We are getting there :)

Yes we are. The most frustrating part of this is that these aren't generating issues in Office 2000 or 2007, but I can see the errors frozen in the version of the sheet you save so long as I don't recalculate the sheet. As soon as I let it recalculate the errors clear, which makes it difficult to do more than a fix or two at a time because I need to get past one error to see if there's another.

So I just uploaded another fix for the Save Sheets which will hopefully get those and the output sheet working correctly now.

[Krasudreal]

Originally Posted by ericbsmith

With 7Zip you just need to download both files into the same directory and open 001, it will automatically extract any files that are in archive 002.

The only thing I can think of is windows regional settings; do you have your region set up where windows uses a comma as the decimal symbol? That can cause all sorts of problems.

You might very well be right on the money. I am from Brazil and we indeed use... Y'know, commas. I'll try to fix this... Somehow...

[loofou]

Originally Posted by ericbsmith

What do you mean "systems popup" - do you mean the the drop-down menus on the design table for selecting Ship Systems?

Yes that's what I mean. As you see in the video, it's simply empty.

Originally Posted by ericbsmith

As soon as I let it recalculate the errors clear, which makes it difficult to do more than a fix or two at a time because I need to get past one error to see if there's another.

I can make more tests and resaves this evening after work (which would be early in the morning for you I guess? Well, around same time as yesterday).

Btw: Thank you for your hard work on this :)

[EDIT]
There is still the Sheet Size Error 1004 (when using the Zoom or Resolution buttons in the Design Table). Popups for the Ship Systems are empty on start, but as soon as you load a ship and reset, they work fine. Output seems also to be fixed. Only minor stuff remains :)

Latest resave: https://www.dropbox.com/s/i7vu6sm6cl...0220.xlsm?dl=0