Page 1 of 3 123 LastLast
Results 1 to 15 of 40
  1. #1
    New Lounger
    Join Date
    Dec 2014
    Location
    Vancouver, BC
    Posts
    19
    Thanks
    18
    Thanked 0 Times in 0 Posts

    Making Monopoly game on Excel spreadsheet

    Hi

    I have been messing around in my leisure time designing a Monopoly game to be played on an excel spread sheet. It is certainly not Pulitzer prize material but is a challenge to myself if I can do it. It will also be entertaining when completed. Aligning columns and rows while using the color scheme, amazingly it resembles the game board fairly well. I have a data sheet that has all the data for the streets and an Info sheet the has the data for the Chance and Community Chest cards.

    I have been learning VBA slowly by scouring the Internet and picking up tid bits in this forum. Using Randomize and the Rnd function that I picked off from the help menu, I can randomly generate numbers that will pick a card (by the row number) and roll the dice. But that is as far as I got. The board logic seems that it will be mostly be "IF" statements and message boxes for the cards and statistics. I don't think I will have a problem with that but that will be my next hurdle.

    I want to show an animation of a dice rolling across the board when the user clicks a "ROLL DICE" button. I prefer to do this directly on the sheet as opposed to a user form which was the only solution I found. Back in the day this was easy to do on my Texas Instrument TIAA "something or other" using extended basic and sprites. Is there an equivalent in VBA? Most of what I am reading refers to Visual Basic and gives me error messages when I attempt to run it from Excel.

    If anyone is up for the challenge, you will have at least one captive audience and a salute when I drink down my next beer.


    Thanks,
    Brian

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 988 Times in 918 Posts
    Can you post the code you are using and the error it produces?

    cheers, Paul

  3. #3
    New Lounger
    Join Date
    Dec 2014
    Location
    Vancouver, BC
    Posts
    19
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Good Morning Paul,

    This is one of many I pasted into the module window and tried to run.
    Code:
    Public Function RollDice(value As String) As Integer
            ' setup working variables to make things easier
            Dim tmp As String = ""
            Dim modType As String = ""
            Dim numberOfDice As Integer = 0
            Dim numberOfSidesPerDice As Integer = 0
            Dim modifier As Integer = 0
            ' First we check to see if there is a "d" in the string
            If value.Contains("d") = True Then
                ' There is so first we need to get the value infront of the d
                tmp = Field(value, "d"c, 1).Trim
                ' and convert it to an integer if it's a number, errors are silently
                ' ignored for now. 
                If Integer.TryParse(tmp, numberOfDice) = False Then
                    numberOfDice = 0
                End If
                ' Now look at the value after the d
                tmp = Field(value, "d"c, 2).Trim
                ' does it contain a + or a -?
                If tmp.Contains("+") = True Then
                    modType = "+"
                End If
                If tmp.Contains("-") = True Then
                    modType = "-"
                End If
                ' if does not contain a + or a -, then there is no modifer
                If modType = "" Then
                    ' and we take the right side of the d as the number of sides
                    ' of the dice
                    If Integer.TryParse(tmp, numberOfSidesPerDice) = False Then
                        numberOfSidesPerDice = 0
                    End If
                Else
                    ' there is a + or a - so we need to extract the number on the left
                    ' side of the +/-
                    Dim bit As String = Field(tmp, CChar(modType), 1).Trim
                    If Integer.TryParse(bit, numberOfSidesPerDice) = False Then
                        numberOfSidesPerDice = 0
                    End If
                    ' now we take the right side of the +/- and set that to the modifier
                    bit = Field(tmp, CChar(modType), 2).Trim
                    If Integer.TryParse(bit, modifier) = False Then
                        modifier = 0
                    End If
                End If
            Else
                ' Ah so there is no d so we assume it's not a forumlar, just a number
                numberOfDice = 0
                numberOfSidesPerDice = 0
                If Integer.TryParse(value, 0) = True Then
                    modifier = 0
                Else
                    modifier = 0
                End If
            End If
     
     
            ' Now comes time to roll the dice
            Dim lp As Integer
            Dim total As Integer = 0
            ' Set up a random object randomised by the syystem date and time
            Dim objRandom As New System.Random(CType(System.DateTime.Now.Ticks Mod System.Int32.MaxValue, Integer))
            ' loop through the number of dice
            For lp = 1 To numberOfDice
                ' add each roll to the total
                total = total + CInt(objRandom.Next(numberOfSidesPerDice) + 1)
            Next
            ' now modify the total if needed
            If modType = "+" Then
                total += modifier
            ElseIf modType = "-" Then
                total -= modifier
            End If
            ' we have the results of the dice roll
            Return total
        End Function
     
        ' Using the delimiter to split the string into chunks, return the pos chunk
        ' e.g. Field("1d6+1","d",2) would return "6+1"
        Public Function Field(ByVal sourceString As String, ByVal delimiter As Char, ByVal pos As Integer) As String
            Dim parts() As String = sourceString.Split(delimiter)
            If pos > parts.Length Then
                Return ""
            Else
                Return parts(pos - 1)
            End If
        End Function
    It seems that most descriptions of the graphics is stationary dice with flipping faces that eventually stop at some combination of numbers

    Thanks Brian
    Last edited by RetiredGeek; 2015-06-23 at 07:46. Reason: Added Code Tags

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 988 Times in 918 Posts
    I don't have Excel so you'll have to wait for someone else to give it a whirl.

    cheers, Paul

  5. #5
    Silver Lounger
    Join Date
    Mar 2014
    Location
    Forever West
    Posts
    2,078
    Thanks
    0
    Thanked 259 Times in 248 Posts
    Quote Originally Posted by Paul T View Post
    I don't have Excel so you'll have to wait for someone else to give it a whirl.
    cheers, Paul
    Do you have the free LibreOffice? It has worked with a number of .xls files I've opened in it, latest versions even work with files from Office 2007 and later with the x added to the extension.
    www.openoffice.org

  6. #6
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by X_LD View Post
    Good Morning Paul,

    This is one of many I pasted into the module window and tried to run.
    Code:
    Public Function RollDice(value As String) As Integer
            ' setup working variables to make things easier
            Dim tmp As String = ""
            Dim modType As String = ""
            Dim numberOfDice As Integer = 0
            Dim numberOfSidesPerDice As Integer = 0
            Dim modifier As Integer = 0
            ' First we check to see if there is a "d" in the string
            If value.Contains("d") = True Then
                ' There is so first we need to get the value infront of the d
                tmp = Field(value, "d"c, 1).Trim
                ' and convert it to an integer if it's a number, errors are silently
                ' ignored for now. 
                If Integer.TryParse(tmp, numberOfDice) = False Then
                    numberOfDice = 0
                End If
                ' Now look at the value after the d
                tmp = Field(value, "d"c, 2).Trim
                ' does it contain a + or a -?
                If tmp.Contains("+") = True Then
                    modType = "+"
                End If
                If tmp.Contains("-") = True Then
                    modType = "-"
                End If
                ' if does not contain a + or a -, then there is no modifer
                If modType = "" Then
                    ' and we take the right side of the d as the number of sides
                    ' of the dice
                    If Integer.TryParse(tmp, numberOfSidesPerDice) = False Then
                        numberOfSidesPerDice = 0
                    End If
                Else
                    ' there is a + or a - so we need to extract the number on the left
                    ' side of the +/-
                    Dim bit As String = Field(tmp, CChar(modType), 1).Trim
                    If Integer.TryParse(bit, numberOfSidesPerDice) = False Then
                        numberOfSidesPerDice = 0
                    End If
                    ' now we take the right side of the +/- and set that to the modifier
                    bit = Field(tmp, CChar(modType), 2).Trim
                    If Integer.TryParse(bit, modifier) = False Then
                        modifier = 0
                    End If
                End If
            Else
                ' Ah so there is no d so we assume it's not a forumlar, just a number
                numberOfDice = 0
                numberOfSidesPerDice = 0
                If Integer.TryParse(value, 0) = True Then
                    modifier = 0
                Else
                    modifier = 0
                End If
            End If
     
     
            ' Now comes time to roll the dice
            Dim lp As Integer
            Dim total As Integer = 0
            ' Set up a random object randomised by the syystem date and time
            Dim objRandom As New System.Random(CType(System.DateTime.Now.Ticks Mod System.Int32.MaxValue, Integer))
            ' loop through the number of dice
            For lp = 1 To numberOfDice
                ' add each roll to the total
                total = total + CInt(objRandom.Next(numberOfSidesPerDice) + 1)
            Next
            ' now modify the total if needed
            If modType = "+" Then
                total += modifier
            ElseIf modType = "-" Then
                total -= modifier
            End If
            ' we have the results of the dice roll
            Return total
        End Function
     
        ' Using the delimiter to split the string into chunks, return the pos chunk
        ' e.g. Field("1d6+1","d",2) would return "6+1"
        Public Function Field(ByVal sourceString As String, ByVal delimiter As Char, ByVal pos As Integer) As String
            Dim parts() As String = sourceString.Split(delimiter)
            If pos > parts.Length Then
                Return ""
            Else
                Return parts(pos - 1)
            End If
        End Function
    It seems that most descriptions of the graphics is stationary dice with flipping faces that eventually stop at some combination of numbers

    Thanks Brian
    Does Excel actually run that code or does it fail when you try to compile it?

    That doesn't look like VBA to me - it just looks like a chunk of VB code that's been pasted into Excel and therefore won't ever work!

    It might actually be easier if you wrote a short bit of pseudo-code describing what it is you're trying to do

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Brian

    ..I knocked up this dice roller for Excel2007, 2010, 2013, 2016.
    It's a first version.

    You could adapt it for your game.

    (I tested it also works for Excel2003 - just save it in .xls format)

    zeddy
    Attached Files Attached Files

  8. The Following 2 Users Say Thank You to zeddy For This Useful Post:

    Anklebuster (2015-06-25),X_LD (2015-06-23)

  9. #8
    New Lounger
    Join Date
    Dec 2014
    Location
    Vancouver, BC
    Posts
    19
    Thanks
    18
    Thanked 0 Times in 0 Posts
    To answer your question Jeremy, I couldn't get that specific code to run. It was stated to be VB but one of the few that was described as "Rolling dice with movement".

    Thank you zeddy for your sample. It is the nicest one that I found that uses VBA. I would like the dice to move across the board if possible as if someone was throwing them. Could yours be adapted?

  10. #9
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 988 Times in 918 Posts
    Quote Originally Posted by Berton View Post
    Do you have the free LibreOffice?
    Yep, but it doesn't run VBA (it does sometimes).

    cheers, Paul

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Brian

    ..it can be done. What does your board look like?????

    Note:
    In my example file, you may notice that occasionally only one of the two dice will show.
    There is a mathematical reason for that.
    ..but I count it as 'throwing the dice and one falls off the Board'.

    zeddy

  12. #11
    New Lounger
    Join Date
    Dec 2014
    Location
    Vancouver, BC
    Posts
    19
    Thanks
    18
    Thanked 0 Times in 0 Posts
    You are a comedian Zeddy

    The screen looks just like a Monopoly Board. I have a 4:3 aspect ration screen and it fits perfectly in that view.

    Thanks,
    Brian

  13. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Ok Brian

    ..you found me out!

    But, seriously, I wanted to know between what columns, and what rows, you wanted to have the dice 'roll'.
    And is your zoom level set to 100%???
    And whether you see the 'whole' board on the screen, or if you have to 'scroll'??

    I'll work on the basis that everything fits into the display window then.

    zeddy

  14. #13
    New Lounger
    Join Date
    Dec 2014
    Location
    Vancouver, BC
    Posts
    19
    Thanks
    18
    Thanked 0 Times in 0 Posts
    zeddy

    Here is my game board. I see the entire board at 90% and just the lower edge is missing at 100%. If I had a preference, the dice would come in from the lower left and rest somewhere in the center of the board.

    Now if a dice happens to accidently go off the table, we'll just have to roll again.

    B.

    Untitled.png

  15. #14
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,729
    Thanks
    148
    Thanked 156 Times in 149 Posts
    Doesn't this break copyright laws? Just wondering.....

  16. #15
    New Lounger
    Join Date
    Dec 2014
    Location
    Vancouver, BC
    Posts
    19
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Hi a-mdb,

    I do not plan on making any profits or distribution of my project. The board was generated not from a picture but from my design and work of an Excel spreadsheet which was not copied from another's efforts. The code behind it that will give it functionality will be the result of my authoring and the help I get from this forum.

    IMHO, this would not be a copyright infringement but I do not claim to be a patent lawyer.

    Brian

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •