ACDG3- the rpg game

[Zeiter]

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.

[Snowflake]

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.

[Snowflake]

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.

[Zeiter]

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!

[Zeiter]

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

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.

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.

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.

[Snowflake]

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.

[Snowflake]

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.

[Zeiter]

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.

[Zeiter]

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.

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

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

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:

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.

[smacksim]

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!

[Snowflake]

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".

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

Test it by pressing "ctrl m". You'll notice I only wrote movement for moving South. So you can only fill the input box with "S" when you are testing. If it works fine, then you can go ahead and do the other three directions. Let me know if you don't understand the script. The offset(x,y) means the cursor goes down x row and y columns to the right.

When everything works you can then fine tune it, for example you can decide if you want to use capitalized letters for directions and such.

[Zeiter]

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.

[Snowflake]

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.

[Zeiter]

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.

[Snowflake]

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