+ Reply to Thread
Results 1 to 12 of 12

user defined constants?

  1. #1
    Registered User
    Join Date
    05-25-2011
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    52

    user defined constants?

    I have a macro that I use to run simulations. At the top I have a constant defined for the number of simulations and then I have multiple variable dimmed off of that number. So for example:
    Const Simulations = 100
    dim myvariable (1 to Simulations) as double

    Ideally what I want to be able to do is define what that number of simulations is at the very beginning and use that throughout. But since it's a constant, I can't read it in as a variable, even if it would never change after that. Is there any way that I don't know of to allow the user to enter how many simulations and then use that number as a constant?

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: user defined constants?

    Try

    Public Const Simulations As Double = 100
    Cheers!
    Deep Dave

  3. #3
    Registered User
    Join Date
    11-13-2006
    Posts
    87

    Re: user defined constants?

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    08-04-2009
    Location
    Golborne England
    MS-Off Ver
    2013
    Posts
    85

    Re: user defined constants?

    Try this it works for me.

    Please Login or Register  to view this content.

  5. #5
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: user defined constants?

    Instead of a constant, you could case Simulations as a UDF, that remembers its value.


    Please Login or Register  to view this content.

    Code like
    Please Login or Register  to view this content.
    will ask the first time, but will remember that entry thereafter.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: user defined constants?

    No, because then it wouldn't be a constant. There are some good workarounds above though

  7. #7
    Registered User
    Join Date
    05-25-2011
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    52
    Quote Originally Posted by NeedForExcel View Post
    Try

    Public Const Simulations As Double = 100
    I'm not quite sure how that works for me. Would that allow me then to change it since it's a public constant? I tried adding that and I got an error: "invalid attribute in sub or function"

  8. #8
    Registered User
    Join Date
    05-25-2011
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    52
    Quote Originally Posted by Kyle123 View Post
    No, because then it wouldn't be a constant. There are some good workarounds above though
    Haven't tried them all yet, but i don't think any address my issue which is the size of my variable array can only be based off a constant. It won't let you size your variable based on a variable. But if i have to use a constant, then that means to change the number of simulations, i have to edit code, which is OK for me but not so much if i give this to other people to use...

  9. #9
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: user defined constants?

    Quote Originally Posted by gryffin13 View Post
    Haven't tried them all yet, but i don't think any address my issue which is the size of my variable array can only be based off a constant. It won't let you size your variable based on a variable. But if i have to use a constant, then that means to change the number of simulations, i have to edit code, which is OK for me but not so much if i give this to other people to use...
    Yes it will let you dimension an array based on a variable. THis has no constants and works as expected.


    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    11-13-2006
    Posts
    87

    Re: user defined constants?

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    05-25-2011
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    52
    Quote Originally Posted by mikerickson View Post
    Yes it will let you dimension an array based on a variable. THis has no constants and works as expected.


    Please Login or Register  to view this content.
    Oh alright. I don't think i fully understood the first time. This looks good. I'll report back once i try it. Thanks!

  12. #12
    Registered User
    Join Date
    05-25-2011
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    52
    Quote Originally Posted by mikerickson View Post
    Yes it will let you dimension an array based on a variable. THis has no constants and works as expected.


    Please Login or Register  to view this content.
    I got this to work. Thanks so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy, paste, replace- user defined range in all the defined sheets
    By aganesan99 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-13-2014, 12:28 PM
  2. Replies: 0
    Last Post: 11-19-2012, 10:41 AM
  3. Using array constants in user-defined excel vba function
    By MiloMan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-01-2009, 08:45 PM
  4. System defined constants in foreign languages
    By Nick O in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-27-2006, 07:25 AM
  5. using user defined constants
    By ravi in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-26-2005, 12:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1