03-08-2004 12:12									#1
	ACDG3- the rpg game Support Apolyton, buy GURPS/ Alpha Centauri

Hehehe, I've been fooling around with spreadsheets lately, and recently I thought about trying to make an RPG game out of SMAC on a spreadsheet. Well, 4 days and a couple thousand if/then equations later, the ACDG3 rpg game is finished.

Basically, it's like one of those really old rpg games from the 1980's. You move around on a map, fight enemies that randomly appear, gain experience and gold (in this case, energy credits) and you can buy better weapons and armor, potions to restore health (longevity vaccine and clinical immortality vials), etc. The object of this game is to make a dangerous journey to The Hive and defeat Chairman Yang and his entourage in rpg combat. Along the way you'll fight 10 different kinds of enemies. Here's a screenshot to show:

Attachment: acagame.jpg
This has been downloaded 88 time(s).

	03-08-2004 12:21									#2
	Put an end to popups!

Now, I must warn you all: this game will probably seem VERY boring, except for the slight novelty value. It requires a tiny bit of player input to make it work. But for the most part, it runs on its own, which I thought would never happen. The hardest thing was getting the combat engine to work right. And even now I'm not totally sure that its working 100% as I intended, but it seems to be working well enough from the little bit I've played on it.

Here's another screenshot, this one of part of the World Map:

Attachment: acagame2.jpg
This has been downloaded 86 time(s).

	03-08-2004 12:28									#3
	Support Apolyton, pre-order Civilization IV

So, here it is, both in xls format and wks format. I hope it works on other computers, but it might not, because my computer is sold old (a windows 98) that it might generate files that are incompatible with the newer computers.

There's a brief tutorial on the left-hand side of the spreadsheet, but if anyone has anyone questions, comments, or ideas of how I can improve it, please let me know.

Attachment: acdg3 rpg game.zip
This has been downloaded 5 time(s).

	03-08-2004 12:33									#4
	Avatar Enlargement: We've got the solution

You have way too much time to spare

I'll give it a try if I can get any of my programs to open up either file. Looks like fun though, RPG designing can be rather engrossing (and make you pull out hairs by the dozen...).

	03-08-2004 12:49									#5
	Remove this text

Yeah, I've got this whole summer off from school, and there's not much else to do but read, check the ACDG, and find something else to occupy my time with. This last week, it's been trying things out with spreadsheets.

The biggest hair-pulling-out inducer for me was getting the combat engine to work properly. First I kept getting "ERR" in the cells, until I realized that sometimes the equations were having to divide by 0. So I got that fixed, but then I would get wierd combat results, like my level 1 character doing 20,000 damage. So I got that fixed (I think :lol but then I had to figure out a way to tally the experience and ECs after each battle. Then I had to incorporate factional bonuses, the two healing items, and some other stuff into it all. Designing the combat engine alone took 2 days of over 10 hours a day. The other stuff wasn't quite so bad. Building the random enemy generator on the map was tedious but straightforward. But overall, the whole project took about 4 days of work. It started out as a simple "Gee, I wonder if I could do this..." and gradually snowballed in complexity. Anyway, I hope you are able to open it.

	03-08-2004 20:49									#6
	Suffering from ads?

Actually it might be fun if we run it right here as a group game. Zeiter run it and tells us the event. Anybody who wants to participate post here. We may actually have some rules like at least three people must agree when we move to a direction, etc.

Not sure how much stuff you've got in your game. It might be interesting if you got lots things that can be manipulated. Also people have to find where Yang is, and then enter his building where more traps and such is around. The person who takes up some action may face some risks like being injured or die but could also be rewarded individually. Etc.

	03-08-2004 20:56									#7
	Support Apolyton buy from Amazon

When I open the file, it says L93 has a circular reference. Is this normal?

	03-08-2004 21:10									#8
	Support Apolyton, buy Civilization 2

If you go to Tools/Options/Calculation and check the iteration box this should solve the circular reference problem, if the circular reference is indeed intensional.

	03-08-2004 21:27									#9
	Full PM-box? Change here!

The spreadsheet could improve somewhat. For example, in cell T13 you have entered a long mulitple if function. You could easily use a Vlookup funtion to look up the armor value. Check the help for details about how to use the function. If you need further help just ask.

Also direction is not very clear. You could insert some names so that people can jump to wherever you want them to go, instead of asking people to find "start here" or something like that. You could even insert a form upfront for people to fill in name and faction, then use a macro to take them directly to where you want them to start.

	03-08-2004 21:36									#10
	Full PM-box? Change here!

Formula P13 is in error. Should be "=(M13*5+11)-T13", instead of T6.

	03-08-2004 21:47									#11
	Got spare money?

Formula S39 is wrong.
"=IF(J50=0,0,IF(P50=0,S39+1,0))"
It references to itself. This is perhaps the origin of the circular reference.

	03-08-2004 21:50									#12
	Suffering from ads?

Also P50=0 is wrong, should be P50=false.

	03-08-2004 22:01									#13
	Help yourself to an AD-FREE life

Snowflake is working in her off-duty time!!!
You sure seem to know how handling spreadsheets.

	03-08-2004 22:10									#14
	Support Apolyton, pre-order Civilization IV

I noticed that you have many self reference cells, like when you calculated accumulated experiences and ecs. Excel doesn't work this way. You need to write VB codes for this.

Well I suppose the feedback is enough for you to work for a while now.

	03-08-2004 22:11									#15
	Increase the size of your Attachments

quote: Originally posted by GeoModder Snowflake is working in her off-duty time!!! You sure seem to know how handling spreadsheets.

Yeah I simply can't resist the attraction of spreadsheets. That's why I like my work. I got to do lots of those kind of stuff.

	03-08-2004 23:18									#16
	Support Apolyton, buy Alpha Centauri

Ah, thanks for the tips!

Yeah, the only way I could figure out how to do an iteration was to have the cell reference itself. But I guess that's not how it should be done, so I'll try as you said to use VB codes. Although from how much I had tested the game, the self-referencing iterations seemed to work fine.

Wait, so there's an easier way to look up things than to use a repeated IF function? D'oh! I bet if I had known that, it would have saved me a lot of time and effort.

Thanks alot for everything, Snowflake! I'm really learning a lot. I'll try implementing your suggested revisions and post them when I finish.

	03-08-2004 23:45									#17
	Support Apolyton, buy Call to Power 2

For the self reference part, what you can do is this.

Make a button say "fight". Then you attach a vb script to it. When people click on it, the script calculates the values for different cells, and write the result back to the spreadsheet.

Something like this:

OldExperience = Range("S39").value
NewExperience = Range("S50").value
OldExperience + NewExperience

Or simply

TotalExperience = Range("S39").value + Range("S50").value

Then you write the value back

Range("S39").value = TotalExperience

After you make the script, attach it to the button as a "onclick" event.

Remember you have to define all of the variables you are using in the script.

	03-08-2004 23:52									#18
	Inflate your Upload Space

quote: Originally posted by Zeiter Although from how much I had tested the game, the self-referencing iterations seemed to work fine.

For it to work, the excel option for maximum iterations needs to be changed to 1. I would say you can go with this right now in the testing period since vb coding is kind of in an entirely different terrain. The thing with your spreadsheet right now is that too many things are dependent on the end users, like they have to change options settings and such. In other words you need to write very detailed instructions.

	04-08-2004 00:38									#19
	Lose 30 kilos (of popups)

Yeah, it does require a fair amount of input from the user right now. Of course since I'm the one who designed it, it isn't hard for me to keep track of what I must do, but to some random person trying to play this for the first time it would be mighty confusing. Ideally, I'd like to have:
*The enemy code pop up automatically when an enemy is encountered on the random map, instead of the user having to go up and enter in the code his/herself
*I'd like to modify the battle engine so that there's no need for the "READY?" button. Right now, if leave that button as showing a "1", when you start a new battle, combat damage will be exchanged on the first round of combat, which will result in the enemy's defense being calculated as being "1", which will cause the player to deal over 20,000 damage. I wish I could figure out an automatic way to delay the start of the battle until after the enemies' information has been loaded into the battle arena. Did that make any sense?

Well, as it is, until I perform the modifications as you suggested, we could still do that group rpg game that you mentioned, and I could be the one who carries out all of the orders. Again, thanks for all of the help!

	04-08-2004 01:17									#20
	Increase Your PM Length

Okay, I've got the spreadsheet open, and I'm checking the things you said one by one.

quote: When I open the file, it says L93 has a circular reference. Is this normal?

Hmmm, whenever I open it up, it always says there's a circular reference, but it doesn't tell me where it is. I checked L93, and there shouldn't be any circular references there.

quote: If you go to Tools/Options/Calculation and check the iteration box this should solve the circular reference problem, if the circular reference is indeed intensional.

I've seen this option on the newer windows spreadsheet programs, but sadly, my windows 98 spreadsheet doesn't have a calculation tab or box under tools/options.

quote: Formula P13 is in error. Should be "=(M13*5+11)-T13", instead of T6.

Hehehe, actually it should be "=(M13*5+11)". The -T6 part is left over from when I was trying to incorporate some special abilities into the game. I had a nerve gas special ability where it gave +50% attack, but it decreased your strength permanently each time you used it by 1. I originally had the "nerve gas used this # of times" value in cell T6. But then I decided to drop the special abilities stuff for the time being, as it was making things drastically more complex. I may put them back in at some point, once I get the game generally running well. But anyway, correction made.

Now I'm gonna try using that Vlookup function. I'll get back to you with my success/failure with that in, hopefully, an hour or two.

	04-08-2004 01:26									#21
	Enter the AD-FREE zone

Well it's easy to reset the fight if you use vb coding. In fact I would almost recommend you to do it right now. I can see a series of actions that you can make. Say you have a little menu in the right corner of your screen where your character's position is in the middle. Then the player choose from the menu which direction he will go. A onupdate event macro/script will do the actual move and copy/paste for him, and to test if there is a monster around him. If so then it brings up the battle screen. In the battle screen there is a fight botton. When the player looks over everything (his stats etc) and chosen the enemy he click on fight. Then the random result generates and if he wins the script added the ecs and experiences for him, and then delete the enemy from the battle screen and reset everything else. Basically this would be in a entirely graphical interface instead of the actual spreadsheet, you have different buttons for different things.

Even if you are not ready for that yet, you still should make the ready cell into a button. It'll be pretty easy to make if you only let it perform one thing, that is to fill the cell with "1". Then you can make another "Done" button, and ask the user to click that when the fight is over. What it does is to change the cell back to "0" or empty and bring the player back to where he was in the map. Using buttons is much more intuitive than asking the user to change the value of a cell back and force.

	04-08-2004 01:33									#22
	Lose 30 kilos (of popups)

Hmmm I'm almost sure you could do iterations with office98. I don't have it now so I can't check but I've used it before I think...

For vlookup basically you have a range where you store different values. The first column is your key. When you use it, you look up the key value, and specify which column you want, then you can find the corresponding level. For example, say you have a range named Student like this:

1001 James
1002 Codar
1003 Kandy
1004 Drugs

Then in a cell your function =vlookup(C1, Student, 1, false) will return Codar if the value in C1 is 1002.

	04-08-2004 03:38									#23
	Tired of ads?

Well, I've come up with something that removes the need for the user to type in "1" in the ready box. I typed in "=IF(J38>3,1,0)" in I38. Then, in J38, I typed in "=IF(J50=0,0,(IF(J50>0,(J38+1),J38)))". (Yes, another one of those iterations. But hey, it seems to work.) So, when the player loads the enemies into the battle arena, the battle automatically waits 3 turns to begin. This gives the battle arena time to load in the enemy information before the battle starts. And now this works by the user just hitting the F9 key instead of having to type in "1" into the ready box. To help the user out, I also added a text box that says "Battle will begin in..." in cel I39. Then in cell J39, I put,

" =IF(J38=0,4,IF(J38=1,3,IF(J38=2,2,IF(J38=3,1,IF(J3
8>3,"Battle Has Begun!",0)))))"

This gives the user a little countdown for when the battle will start. They just hit F9 to progress the countdown, and then when the battle begins, "Battle Has Begun!" pops up in cell J39. Then, when the user deletes the enemy codes from the battle arena, everything resets. I think I may move cells I38 and J38 now, as they are unnecessary for the user to see. I'll probably put them over with the other blackened out information blocks. So, what do you think? Is this a good, user-friendly way to handle the start of the battle? It seems to me that the countdown method is self-explanitory enough.

	04-08-2004 05:57									#24
	Support Apolyton or Terrorists Win

Alright! I've simplified and automated the battle process so that all the user has to do is punch in the enemy code(s) and choose who they will attack. Everything else runs itself. I've also made the batte arena easier to follow so that the user can keep their screen centered on one spot for the entire battle and still see everything they need to see.

quote: Also P50=0 is wrong, should be P50=false.

I double checked it, and it running fine the way it is.

quote: Excel doesn't work this way. You need to write VB codes for this.

It seems to be working well enough at the moment, so I'll leave it as it is.

Right now, if I could just make the map movement less combersome, I'd be thrilled. I read your suggestion, but I'm really confused as to how to do this:

quote: Say you have a little menu in the right corner of your screen where your character's position is in the middle. Then the player choose from the menu which direction he will go. A onupdate event macro/script will do the actual move and copy/paste for him, and to test if there is a monster around him. If so then it brings up the battle screen. In the battle screen there is a fight botton. When the player looks over everything (his stats etc) and chosen the enemy he click on fight. Then the random result generates and if he wins the script added the ecs and experiences for him, and then delete the enemy from the battle screen and reset everything else. Basically this would be in a entirely graphical interface instead of the actual spreadsheet, you have different buttons for different things.

I'm sorry, but I have no idea what an onupdate event macro/script is. What are vb codes and macro/scripts? Where do you type them in (in the cell's formula bar?)?

I'm going to post a revised version of the game soon, once I update the tutorial and doublecheck everything one last time. And I'd also like to figure out a better way for moving around on the map. If you could explain that top paragraph a little more, perhaps I could incorporate that into my spreadsheet too. That would be freakin' awesome, to just move around without having to copy/paste every move. Anyways, thanks a ton for all of the suggestions.

	04-08-2004 08:06									#25
	Support Apolyton, buy Galactic Civilizations: Deluxe Edition

quote: Originally posted by Snowflake Yeah I simply can't resist the attraction of spreadsheets. That's why I like my work. I got to do lots of those kind of stuff.

Hee Hee Snowflake, that's a quote to keep!

	05-08-2004 21:03									#26
	Inflate your Upload Space

Ok. Open your spreadsheet, Tools/Macro/Record New macro.

Fill the popup window with macro name (suggest "movement")and shortcut key (I suggest "m").

Click on ok. Then click on the stop button.

Tools/Macro/Macros..., select the macro you just entered (movement), Edit. Paste the following before "End Sub".

code:

Dim PlayerName As String
Dim MyDirection As String
    
PlayerName = Range("K13").Value
Range("R77").Activate

Cells.Find(What:=PlayerName, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        ).Activate
MyDirection = Application.InputBox("Enter a direction (E, S, W, N)")
If MyDirection = "S" Then
    ActiveCell.Value = ""
    ActiveCell.Offset(1, 0).Value = PlayerName
    If ActiveCell.Offset(0, 1).Value <> "" Then
        ActiveCell.Formula = ActiveCell.Offset(0, 1).Formula
    Else: If ActiveCell.Offset(0, -1).Value <> "" Then ActiveCell.Formula = ActiveCell.Offset(0, 1).Formula
    End If
    ActiveCell.Offset(1, 0).Select
End If

	06-08-2004 00:12									#27
	Avatar Enlargement: We've got the solution

I'm sorry, but there exist nothing under tools, or anywhere, that says macro on the version I'm using. All I see under tools is:

*Spelling
*Sort
*Easy Calc
*Create new chart
*Rename chart
*Delete chart
*Duplicate chart
*Recalculate now
*Customize toolbar
*Options

And then I checked under options, and there still wasn't anything that said "macro". I guess it's because I'm using windows 98. Thanks for the help anyway.

Overall, I'm pretty satisfied with its workings now, although I need to tweak things in the gameplay like the amount of experience for each monster. I played through it once, and it was waaaay too easy to gain levels, so I'm going to go back and tweak that. And then I'll probably post a final version sometime soon. But thanks for pointing out all the little errors.

And if you really want to, you can try inserting the vbcode and macro stuff yourself. I hold no copyright on it. Once again, thanks for everything.

	06-08-2004 00:57									#28
	Got spare money?

Hmmm yes I guess it has been too long that I haven't used office 98. Are you sure you were using office 98 btw? It may not be the same version with your windows system. Check Help/About microsoft excel see what version you are using.

The bad thing about your using a low version is that people who use your game may have much higher levels and would perhaps encounter the problems I saw. Like for the iteration, looks like yours is automatically set to maximum iteration =1. But if other people have it set for 100 then your experience and ec accumulation is going to be badly fouled up.

If you want a test you can try type in =A1+B1 in cell A1 and 1 in cell B1. When you press F9, if you have iteration set to 100, you'll see increase of 100 each time you press F9. And if you turn iteration off, then the number will not change when you press F9 and you'll only get an error message saying circular reference.

	06-08-2004 01:18									#29
	Support Apolyton, buy Alpha Centauri

Okay, I tried that, and each time I hit F9, the value in cell A1 increased by 1.

And I checked what version I am using. It is Microsoft Works 2000 version 5.0

I've noticed that this version lacks a lot of things that I've used with the newer Excel spreadsheet. I just took a computer applications class this summer, and we were using XP in there, and I noticed a lot of awesome features that my home computer doesn't have. Oh well, I might get XP soon.

	06-08-2004 01:24									#30
	Suffering from ads?

Oh you are using Works. I thought you were using excel. They are rather different I think.