How do you program interactive character sheets?

[garrowolf]

I want to create an excel type sheet for creating characters quickly in my system but I don't know where to start. Does anyone know how to do this kind of thing? or a tutorial? I haven't been able to find one.

 

[Dannyalcatraz]

How do you mean interactive? Do you mean so that it auto-calculates certain things, like when you change you Con, your HP change?

If that's what you mean, Excel has a help menu that should include a list of the various formulas the program uses.

If your version of Excel doesn't, I bet a quick search for "Excel formulas" will get you what you need.

 

[Nytmare]

I make all of my character sheets in excel and used to use straight formula formatting for everything. The more automation I ended up adding/wanting to add however, the more fragile and bulky the character sheets would end up becoming.

What I ended up eventually discovering was that there's a pretty rugged and easy to learn Basic programming language built into Excel called VBA. You end up saving so much time and frustration and the character sheets are far less likely to break because you accidentally erased some random bit of hidden of formula.

Here's the VBA tutorial from the top of a google search.

http://www.excel-vba-easy.com/

 

[garrowolf]

So I just need to put in formulas to do most of it?
Is there a version of VBA for Open office/libre office?

 

[garrowolf]

OKay I found Open Office Basic
Now I will read and figure it out!

 

[Dannyalcatraz]

The more automation I ended up adding/wanting to add however, the more fragile and bulky the character sheets would end up becoming.

How do you mean "fragile"?

I had, at one point, a nifty PC spreadsheet for HERO in Excel- much like PC generation in the game itself, it took me a little time to get the thing together initially, but once I had the proper formulas in place...

 

[Nytmare]

How do you mean "fragile"?

For me, I would usually end up with a sheet PACKED with giant, unwieldy formulas tucked to the sides and corners, as far out of the way as I could. There would be formulas, or integers saved in margins, and made white on white so that you couldn't see them.

I'd frequently discover that I'd somehow either accidentally deleted, by way of erasing or moving a full column, or by repasting things for different formatting, or accidentally replaced a hidden formula with an errant keystroke cause I didn't realize that what appeared to be an empty cell actually had some mind bogglingly complex bit of if/then logic in it.

Granted the problem might lie in the way that I program things, I tend to do things really organically, constantly changing and morphing things the longer I use the program. Being able to keep the programming separate, where it's easy to clean and recompose things with a broader picture of what's going on, as opposed to operating with general grasp of the overall pattern while I'm editing a huge formula, 20 bits of information at a time, cause it's 15 lines of code packed into a single cell of a spreadsheet.

 

[Dannyalcatraz]

Hmm...I just used to put the formulas in the cells, invisibly. It seemed to be the default, as I recall.

 

[Nytmare]

Hmm...I just used to put the formulas in the cells, invisibly. It seemed to be the default, as I recall.

I'll give you an example from one of my older 3.5 character sheets.

Hit points are just a number. I could just have a cell where I type in the number, but I want to be fancy cause I'm a glutton for punishment.

So first off, the sheet itself isn't where I type numbers in at all. I never know when I'm going to screw up and accidentally type over something important, so I make a separate form on a separate sheet in the workbook, where the only thing I'm doing is typing stuff in. I'll even go so far as to make all the cells where I want to type stuff in light green in case I get extra stupid.

Now, so that I can have a record of what choices I'm making when (and to make sure that I don't do something like gain hitpoints more than once by mistake) I'll have the form be set up so that I can input what choices and changes are made each level (like how many hitpoints I rolled) and have the sheet add those as well as auto-calculate how many hit points the character will gain from their con bonus.

Buuuuut, I still want to be a pain and add more. We use a Toughness house rule that gives you 1 hitpoint each level from the point where you take the feat on. So now I want to add something in that looks for the word "toughness" in the "Feat" section, and then adds 1 hitpoint every level after that. Now it's getting more complicated, so I add a new column next to the part of the form and have it check two things. #1 it checks to see if the cell next to it has the word "toughness" in it. If it does, it makes it's value 1. Also it checks to see if the cell directly above it is 1. If it is, that means that something spotted the word "toughness" somewhere above, so again, it makes its value 1. To do this as a single formula inside a cell, it means that I have to have an IF statement with a 2 query OR, a single FIND, and an ISERROR of the possible failed FIND statement in it.

Now, to be extra silly, I like to have checkmark bubbles for things like hitpoints and ammo. Granted I could do this by hand, but I'm enjoying the challenge, so I'm going to have a formula look at the number of hitpoints and use the REPT function to make 1 "O" for every hitpoint.

I like it, but I'd like it even more if I could split them up into groups of 5, and the formatting is weird with line breaks, especially once hitpoints reach the 30 or 40 mark, so I'll split it across two different cells, and it would be nice if I could figure out a way to...

In the end the formula for the two cells where hitpoint check bubbles are displayed is:

=IF(ROUNDDOWN(DO14/2/5,0),REPT("OOOOO ",ROUNDDOWN(DO14/2/5,0)),"")&IF(ROUNDUP(MOD(DO14/2,5),0),REPT("o ",ROUNDUP(MOD(DO14/2,5),0)),"")

and

=IF(ROUNDDOWN(DO14/2/5,0),REPT("OOOOO ",ROUNDDOWN(DO14/2/5,0)),"")&IF(ROUNDDOWN(MOD(DO14/2,5),0),REPT("o ",ROUNDDOWN(MOD(DO14/2,5),0)),"")

So yeah, totally my fault, but my character sheets when done straight with formulas meant that they were incredibly delicate, and they had a crazy learning curve that stopped me from being able to let my players fill stuff in for themselves. VBA let me pack stuff out of the way, let me program in an editor where I could use wordprocessor-esque commands, and gave me access to waaaaay more options and toys to pack into my sheets.

 

[Dannyalcatraz]

Ok, that is a bit more than I did- I wouldn't add the bubbles, for instance- but I rarely had problems getting the cells to check other cells for additive, subtractive, etc. factors, once I got it down.