Starfire Solar Spreadsheet Mod 8

Home of SOLAR STARFIRE, 6th edition, rules based on the upcoming history of the Terran Solar Union.

Moderators: SDS Members, SDS Owner

Forum rules
1. Nothing obscene.
2. No advertising or spamming.
3. No personal information. Mostly aimed at the posting of OTHER people's information.
4. No flame wars. We encourage debate, but it becomes a flame when insults fly and tempers flare.

Try to stick with the forum's topic. Threads that belong to another forum will be moved to that forum.

Starfire Solar Spreadsheet Mod 8

Postby Xveers on Fri 01 Jun 2012 23:26

Heya!

Well, I promised I'd be starting on this madness sooner or later... Well it's Later!

So, I don't quite know how to say this, but for this I'm afraid I just have to jump to doing some basic macros. No way around it anymore, as I've hit a wall with the kind of kludge jobs I can do in Excel. Far too much of the code is barely, and I do mean BARELY modifiable. It does its job, but it's such a spaghetti job that I look at it and go "Well, it works. But how?" For a lot of the enhanced functionality I want to implement is either impossible, or dammed near impossible without them (and would make the current spaghetti code look like as straight as a rail line across the plains of Australia).

So, here's what we're looking at for the initial release of Solar Starfire Mod 8:
Solar Compatible: Formulae & definitions set to Solar Standard
Custom Excel Functions: Reduced spreadsheet size and improved maintenance
GM's Tab: All the critical information a GM needs to process a turn
Cleaned Spreadsheets: No "intermediate" columns needed to calculate final results


What's going to come in the future?
Alternate Race Support: Be the gas(bag) race you always wanted to be
Enhanced Data Processing: See how fast your fleet move safely, and how many survey points they can produce per turn!
More cross-referencing: Feed in data once, and have it pull it from wherever

And, down the road a bit farther... Automated Turn Processing: Hit a button, and have your population increment, your CFN shipping moved, construction advanced, research incremented and surveying moved forward.
User avatar
Xveers
Vice Admiral
Vice Admiral
 
Posts: 901
Joined: Wed 15 Jul 2009 02:26
Location: New Westminster, BC, Canada

Re: Starfire Solar Spreadsheet Mod 8

Postby Vandervecken on Sat 02 Jun 2012 09:00

I'm not a fan of macros for most of the obvious reasons, but I completely understand why you need to go there. This is quite a system to get your hands to wrap around, and I hope it doesn't cause much 'wailing and gnashing of teeth' on your part to make it work. We all appreciate the hard work you have done, and the hard work you have graciously taken upon yourself with Solar and spreadsheet Mod 8 (Woot !!!). Good luck, "May the Force be with You', and all that jazz.

Thanks again,

I really look forward to using your Spreadsheet.
I weary of the chasssse. Wait for me. I will be mercccciful and quick.
User avatar
Vandervecken
Fleet Admiral
Fleet Admiral
 
Posts: 1247
Joined: Sun 29 Jan 2012 20:21
Location: Minnesnowta

Re: Starfire Solar Spreadsheet Mod 8

Postby Cralis on Sat 02 Jun 2012 09:03

WOW.

I wasn't expecting you to carry it that far! Awesome!
Image
User avatar
Cralis
SDS Member
SDS Member
 
Posts: 11754
Joined: Tue 30 Jun 2009 19:27
Location: Oregon, USA

Re: Starfire Solar Spreadsheet Mod 8

Postby Xveers on Sat 02 Jun 2012 18:29

As an example of the kind of code cleanup I'm talking about...

THIS is what works out your trade income in the current release of the spreadsheet:

=IF(C12="Restricted Trade",IF(D12*0.05>'Empire Overview'!$P$8*0.1,'Empire Overview'!$P$8*0.1,D12*0.05),IF(C12="Limited Trade",IF(D12*0.1>'Empire Overview'!$P$8*0.2,IF(D12>'Empire Overview'!$P$8,'Empire Overview'!$P$8*0.2*(('R&D'!$C$3*0.05)+1),'Empire Overview'!$P$8*0.2*((E12*0.05)+1)),IF(D12>'Empire Overview'!$P$8,D12*0.1*(('R&D'!$C$3*0.05)+1),D12*0.1*((E12*0.05)+1))),IF(C12="Free Trade",IF(D12*0.15>'Empire Overview'!$P$8*0.3,IF(D12>'Empire Overview'!$P$8,'Empire Overview'!$P$8*0.3*(('R&D'!$C$3*0.1)+1),'Empire Overview'!$P$8*0.3*((E12*0.1)+1)),IF(D12>'Empire Overview'!$P$8,D12*0.15*(('R&D'!$C$3*0.1)+1),D12*0.15*((E12*0.1)+1))))))*IF(F12="",1,IF(F12>0,F12+1,1+F12))

And that's how I have to edit it. No tabbing, or structure to speak of. Just one massive line of nested if loops.

Here's what I call now with a macro:

=TradeRevenue(C9,D9,E9,F9,'Empire Overview'!$P$8,'R&D'!$C$3)

True, the Macro Code is larger than the above non-macro code (it's about... two pages worth of code), but it's properly formatted and has TONS of white space. And it lets me do things far more accurately and efficiently. Nevermind that I only need to make changes in one place now to update it all :)
User avatar
Xveers
Vice Admiral
Vice Admiral
 
Posts: 901
Joined: Wed 15 Jul 2009 02:26
Location: New Westminster, BC, Canada

Re: Starfire Solar Spreadsheet Mod 8

Postby Cralis on Sat 02 Jun 2012 19:12

One of my work responsibilities is VBA code. I'm very well aware of the mess that Excel/Access automation is... and the VBA macro/application part isn't much better.

...but at least you don't have hundreds of millions of dollars riding on not making a mistake in those nested loops. Thank God, that particular excel spreadsheet isn't mine either.

Excel automation/applications is pretty much the only thing I hate more than Access. Microsoft has (unfortunately) left both in the computer-relative stone age of development.
Image
User avatar
Cralis
SDS Member
SDS Member
 
Posts: 11754
Joined: Tue 30 Jun 2009 19:27
Location: Oregon, USA

Re: Starfire Solar Spreadsheet Mod 8

Postby Vandervecken on Sun 03 Jun 2012 07:01

The little excel stuff I created for my non-starfire game's player 'Worlds' sheet, stuff like - =IF(G8="Neu",AI8*(VLOOKUP(VLOOKUP(U8,$CA$16:$CB$24,2),$BP$39:$BQ$41,2)),IF(G8="Occ",AI8*(VLOOKUP(VLOOKUP(U8,$CA$16:$CB$24,2),$BP$45:$BQ$48,2)),IF(G8="Pri",AI8*(VLOOKUP(VLOOKUP(U8,$CA$16:$CB$24,2),$BP$52:$BQ$55,2)),IF(G8="Sec",AI8*(VLOOKUP(VLOOKUP(U8,$CA$16:$CB$24,2),$BP$59:$BQ$64,2)),IF(G8="Ter",AI8*(VLOOKUP(VLOOKUP(U8,$CA$16:$CB$24,2),$BP$68:$BQ$72,2)),AI8*1))))), is so much more easy and basic than than what you have (and what you have to deal with). So I don't quite need to go the Macro route any time soon. I have learned enough so that I can mostly understand what you are doing with that piece of cell instruction you pasted, but am very glad I didn't have to work it out. It is always a tiny rush when I type instruction into an excel sheet and it does what it is supposed to do, I imagine that you probably get the same rush (and a lot of relief) when you are doing this on Starfire Spreadheets.

My thanks goes out to all you guys for getting stuff out that makes the rest of us play this game a bit easier: Spreadsheets, Training Scenarios, Fleet lists, Fiction, and hopefully very soon the "Quick Start rules".
I weary of the chasssse. Wait for me. I will be mercccciful and quick.
User avatar
Vandervecken
Fleet Admiral
Fleet Admiral
 
Posts: 1247
Joined: Sun 29 Jan 2012 20:21
Location: Minnesnowta

Re: Starfire Solar Spreadsheet Mod 8

Postby Glaive7 on Tue 10 Jul 2012 08:08

As a newbie and opening with SOLAR Starfire, I am very much looking forward to Mod 8. I tried using earlier mods, but with my lack of experience, the adjustments just confused me.
Glaive7

I'm going in!!! (I just don't know if I'm coming back out....)
Glaive7
Lieutenant Commander
Lieutenant Commander
 
Posts: 75
Joined: Tue 10 Jul 2012 02:07
Location: Eaton Rapids, Michigan

Re: Starfire Solar Spreadsheet Mod 8

Postby Xveers on Mon 06 Aug 2012 22:09

Status update Nao!

Well, here's the deal. I've done a pair of passes over the spreadsheet and I -think- I've gotten most of the bugs and got it set up to be on the solar standard. There are currently two things that are currently not, however.

The first is the tech tab. I need to go through this and make sure that each tech, knot, and branch is correct. This is... tedious, but reasonably doable.

The second problem is the rebuilding of the basic income calculation. I'm working to rebuild this in a manner so that we can support truly alternative life forms (Gas giant/vacuum/etc). It's proving to be a mite tricky to build.... The basic logic flow is that you compare home habitability to your planet's habitability, which lets you do a vlookup across that with the planet's REI that gets you the REI percentage that tells you how much a planet actually gives you in income. That's a bit of a challenge.

Still, once that's done I'll be releasing the Mk1 Solar spreadsheet for varied testing and kicking!
User avatar
Xveers
Vice Admiral
Vice Admiral
 
Posts: 901
Joined: Wed 15 Jul 2009 02:26
Location: New Westminster, BC, Canada

Re: Starfire Solar Spreadsheet Mod 8

Postby Glaive7 on Tue 07 Aug 2012 15:33

Glad to hear of the progress! :)
Glaive7

I'm going in!!! (I just don't know if I'm coming back out....)
Glaive7
Lieutenant Commander
Lieutenant Commander
 
Posts: 75
Joined: Tue 10 Jul 2012 02:07
Location: Eaton Rapids, Michigan

Re: Starfire Solar Spreadsheet Mod 8

Postby Vandervecken on Tue 07 Aug 2012 21:07

Very glad to hear about your progress on Mod 8 !

I should try to do it pencil and Paper first on my own. It's the best way to really learn the system (and how to exploit it). But, finding the time this Summer has been harder than I thought to do that. Not even the best of plans will usually survive "first fire' though.

So three cheers for the work of Xveers.
I weary of the chasssse. Wait for me. I will be mercccciful and quick.
User avatar
Vandervecken
Fleet Admiral
Fleet Admiral
 
Posts: 1247
Joined: Sun 29 Jan 2012 20:21
Location: Minnesnowta

Next

Return to Solar Starfire

Who is online

Users browsing this forum: No registered users and 6 guests