Page 1 of 4 123 ... LastLast
Results 1 to 10 of 31

Declaring Constant Arrays?

This is a discussion on Declaring Constant Arrays? within the Excel Questions forums, part of the Question Forums category; Hello Can I declare a "constant array" in VBA ? What I would like to do is set up a ...

  1. #1
    Board Regular
    Join Date
    Jun 2002
    Location
    Shanghai, China
    Posts
    144

    Default

    Hello

    Can I declare a "constant array" in VBA?
    What I would like to do is set up a constant which contains the number of days in each month (ignoring leap years), e.g.

    Const DaysInMonth(12) as Integer = Array(0,31,28,31,30,31,30,31,31,30,31,30,31)

    However, I'm unable to find the correct syntax. Is this possible in VBA? (I'm using Excel 97)

    Regards
    Hedges


  2. #2
    dk
    dk is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,934

    Default

    I'd be very suprised if you could do this. You'd be better off using a variable array, or a custom function which returns the number of days in a given month.

  3. #3
    Board Regular
    Join Date
    Jan 2005
    Posts
    418

    Default

    I need to revisit this question. There is a need for arrays of constants. I need to add some cells to a worksheet to show average, min, and max. The worksheet is quite asymetrical and the columns to be evaluated are not definable. Here is what I have been able to do as a workaround.

    Code:
    Dim Evaluation_Array As Variant
    Evaluation_Array = Array(3, 4, 5, 6, 8, 11, 15)
    
    For Each Sum_Column_Number In Evaluation_Array
    
       Call Build_Evaluation_Cell(My_Worksheet, _
                                  Sum_Column_Number, _
                                  Sumation_Row, _
                                  Top_Sumation, _
                                  Bottom_Sumation)
    Next
    The subroutine (in part) adds a summing cell in a row for each of the columns specified. There is more to this, but I hope the code extract above will convey the general concept.

    Evaluation_Array really should be an integer, not a variant, as should be Sum_Column_Number. The use of variant requires that the subroutine be declared with a variant rather than an integer.

    The original post is approaching five years old now. Can an array of constants be declared?
    Thank you,
    Bryan Kelly

    We need to know what a dragon is
    before we study its anatomy.
    (Bryan Kelly, 2010)

  4. #4
    Board Regular
    Join Date
    Jun 2006
    Location
    West London
    Posts
    345

    Default

    I don't understand why you would need to do this.

    What is the difference between defining this array as a constant, and just defining it by hardcoding the values in to be compiled at run time?

    It makes no difference as far as I can see...

    You can have the array as an integer, there is no problem there either.... so long as you know the array will be a predetermined length.

    e.g.

    Code:
    option base 1
    dim myarray(3) as integer
    myarray(1) = 3 : myarray(2) = 4: myarray(3) = 5
    Even if you don't know it would be a pre-determined length, you can use the

    Code:
    redim preserve
    keyword to extend the array as and when required.



    Am I missing something here?
    If at first you don't succeed destroy all evidence that you ever tried.

  5. #5
    MrExcel MVP Oaktree's Avatar
    Join Date
    Jun 2002
    Location
    Chicago, IL
    Posts
    7,746

    Default

    Then again, why do you need the array of days in a month at all? Couldn't you just use:

    Code:
    Dim monthnum As Integer
    monthnum = 2
    MsgBox Day(DateSerial(2006, monthnum + 1, 0))
    or similar?
    "The greatest challenge to any thinker is stating the problem in a way that will allow a solution." Bertrand Russell

  6. #6
    Board Regular
    Join Date
    Jan 2005
    Posts
    418

    Default

    Question: Why do we need to be able to declare an array of constants?

    First, for the same reason that we need constants, we need an array of constants. And for all of those reasons. I don't want to build it a run time, I want it predefined at compile time. I have some deadlines and don't have time to entertain that discussion right now. Get a few books on software engineering and read about software design and constants. Read about writing software for flexibility, robustness and maintainability.

    Please bear in mind that example code posted here is often a vehicle used to convey a complex idea. The simplest possible code segment that can convey the question is posed, the often huge amount of complexity behind it is deliberatly omitted. Because the quesion is simple does not mean that the needs are simple.

    For now I take it that there is no declaration of constant arrays and will move on. I appreciate the time you took to respond and will leave this thread monitored.
    Thank you,
    Bryan Kelly

    We need to know what a dragon is
    before we study its anatomy.
    (Bryan Kelly, 2010)

  7. #7
    Board Regular
    Join Date
    Jul 2004
    Location
    Flordia
    Posts
    911

    Default

    It's not exactly the an array when it's set as a constant, but the below has similar effects to what you ask:
    Code:
    Const z As String = "1, 2, 3, 4, 5, 6, 7"
    
    Sub Test()
        x = Split(z, ",")
        For y = LBound(x) To UBound(x)
            MsgBox x(y)
        Next
    End Sub

  8. #8
    Board Regular
    Join Date
    Jun 2006
    Location
    West London
    Posts
    345

    Default


    First, for the same reason that we need constants, we need an array of constants. And for all of those reasons. I don't want to build it a run time, I want it predefined at compile time. I have some deadlines and don't have time to entertain that discussion right now. Get a few books on software engineering and read about software design and constants. Read about writing software for flexibility, robustness and maintainability.
    I read your response with interest.

    First and foremost, this is a discussion forum where people offer help to others for free. I am not being paid to sit here and read your posts, I do it for the love of helping people out.

    If you read many of the posts on this forum, as I have, you will notice a trend with posts. That trend is that people often ask questions, under the presumption that the question they pose will help them fix their solution. All to often, however, it emerges that if more detail is given on the actual problem (as opposed to the solution they believe will fix it), other caveats to the problem can be unearthed that lead to the conclusion that the OP's question can actually be reposed in another form, leading to an answer that satisfies what is the orginal issue.

    I stated I did not understand why you would want an array of constants. To this point, I still don't understand your intentions. If your motivation was made clearer, then as you say, we could cut through this whole portion of analysis and simply offer you solutions that achieve what you want.

    I have to take significant issue with your tone. You know nothing about anyones software development history on this board. As it happens I am a graduate in Computer Science from a top UK university. I do not need your patronising advice on getting some 'books on software design'. I am very aware of software design principles. I work as a software designer here in London, with Java and C++, as well as a little dabbling in VBA. I have answered numerous posts on this forum with no complaints from the OP's who have got things working as a result. You cannot declare a constant array in VBA. I could have left it there, but decided to try and explore your problem and offer alternative solutions that could act as a workaround for you. Sure constants are only evaluated once as opposed to everytime a variable is used. Yes, there is an overhead to that, but if its important as you say it is, you can use a variable to hold the array, and then use some (now its my time to get patronising) common sense to leave the array alone at run time, and therefore protect its values.

    Furthermore, with this high and mighty attitude you seem to have, I would expect you would get little help from the people on this board. This is a community that involves mutual respect, and a culture of helpfulness and learning. Both myself, and Oaktree (a Microsoft most valued professional) have taken the time out of our lives to attempt to address your problem, to try and talk you through your thought process so as to ascertain not only what you want, but why you want it as well. Frankly I find your consequent posts abhorrently patronising and full of negativity.


    My advice - change your attitude, and fast, else you'll find yourself going down in a very negative fashion not only on this medium, but in life in general.

    Good day to you.
    If at first you don't succeed destroy all evidence that you ever tried.

  9. #9
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,777

    Default

    Hi bkelly

    1. I agree with you. We should be able to define constant arrays. Whenever the language allows it I define most of the constants at the beginning of the modules, outside the routines.

    2 . It is not true what you say: "The use of variant requires that the subroutine be declared with a variant rather than an integer."

    You can (should) use a type conversion function and define the subroutine properly.

    Try this:

    Code:
    Sub ConvType()
    Dim v As Variant
    
        v = 2
        Call paramtype(CInt(v))
    End Sub
    
    Sub paramtype(i As Integer)
    
        MsgBox i & ", " & TypeName(i)
    End Sub
    3. You can use a workaround like BJungheim's. This will let you define the constant array at the beginning of the module

    Code:
    Const sArr As String = "1,3,5,7"
    
    Sub a()
    Dim v
    
    For Each v In Split(sArr, ",")
        MsgBox CInt(v)
    Next
    End Sub
    4 - It might be better if along with a syntax question you would explain your problem.
    There may be other ways of doing what you want that may be more efficient and easier to implement.

    In this case, although I don't have enough information about your problem, I got the feeling that maybe you could use a multiarea range.
    If you had explained your problem better, I might be able suggest it or understand that it makes no sense

    5. Last but not least:

    [quote]I have some deadlines and don't have time to entertain that discussion right now. Get a few books on software engineering and read about software design and constants. Read about writing software for flexibility, robustness and maintainability.[quote]

    Let me tell you that what you wrote is very unpolite and even a bit aggressive.
    As you know we all try to help each other in this board. The attitude from one member towards the others is expected to be polite, even friendly.
    An attitude like the one you displayed, if meant, is not welcome.

    Best regards
    PGC

  10. #10
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    10,945

    Default

    Hi pcg,

    I just want to point out that it's Bryan Kelly who is being so rude, and not Hedges.
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

Page 1 of 4 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com