VBA Compile Error in Excel

[mscheuvront]

Hello everyone,

I'm *extremely* new to VBA programming (just started about an hour ago), though i'm well versed in other languages.

I play a game called Dungeons and Dragons with some friends, and I was looking into an integrated solution for rolling a bunch of dice (-- i.e. 36d6 (36 - 6 sided dice)) onto our character sheets, which are in excel.

I figured that the most elegant solution would be to have a macro that would read a string, and then take from it what it needs to make the appropriate calculation.

WHAT I WOULD LIKE THE PROGRAM TO DO:

read a string from a cell, and parse the necessary information, being stored into the appropriate variables (num - loop control, size - max of Randbetween)
use a FOR loop to generate a number between 1 and max
add generated number to 'answer' variable
iterate until completion
return answer to given cell

'=======EXAMPLE======='

string to read: 2d6
num = 2
max = 6
answer = 0
tmp = 0

'loop num times to generate numbers between 1 and max'

first iteration: tmp = 3
answer = answer + tmp

second iteration: tmp = 5
answer = answer + tmp

'loop exits'

return answer 'to given cell'

'=======END EXAMPLE======='

I apologize up front if I overcomplicated either my attempt at a solution, or the phrasing of my inquiry.

My code:

Please Login or Register  to view this content.

The error I receive when attempting to run my code:
VBA error.png

[walruseggman]

1. I'm assuming you're wanting to do this on a range of cells instead of just S14, but i didn't see the range specified
2. You mention twice you want the answer outputted to a cell, but don't specify which cell, and your code shows a message box

So This code does what you tried to do in your example. If you can't figure out how to modify to suit your needs, then please post an example workbook that shows an example data set.

Please Login or Register  to view this content.

[InvisibleMan]

Please Login or Register  to view this content.

[mscheuvront]

1. I'm assuming you're wanting to do this on a range of cells instead of just S14, but i didn't see the range specified

I'm not trying to use a range of cells. The goal is to have a single cell, S14, containing a string of text (4d6 for example) and then using the macro to output to S15

You mention twice you want the answer outputted to a cell, but don't specify which cell, and your code shows a message box

I mention it, because that would be something that's nice to have, though the message box was my temporary solution, due to not knowing what exactly I needed to do to accomplish what I was after.

...If you can't figure out how to modify to suit your needs, then please post an example workbook that shows an example data set.

I apologize. I'll do so in the future.

[mscheuvront]

Please Login or Register  to view this content.

Would you mind explaining to me why this works? I'm a little confused with just tying to eyeball it.

[walruseggman]

Sorry if you interpreted my tone as negative. Was just trying to be clear what my code would/would not do.

Anyway, try this code. Should do what you want.

Please Login or Register  to view this content.

[mscheuvront]

Thank you very much! I'll go ahead and mark my thread as solved!

[InvisibleMan]

You have to refer to a range as, for example, Range("S14"), not just S14. You can't use Find, that's an Excel worksheet function ... so use Instr instead. Again, RandBetween is a worksheet function, but you can use it if you say: Application.Worksheetfunction.RandBetween. Left and Right are text functions so I've used CInt to ensure the result is converted to Integer. And the Right formula wasn't quite right so that was adjusted.

But using Split is a much neater way of separating the values.

Note that [S14] is a shorthand way of referring to Range("S14"). Got to admit, it's not a shortcut I choose to use.