Declaring Constant Arrays?

Status
Not open for further replies.

HedgePig

Board Regular
Joined
Jun 27, 2002
Messages
146
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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.
 

bkelly

Active Member
Joined
Jan 28, 2005
Messages
465
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?
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
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?
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,016
Office Version
  1. 365

ADVERTISEMENT

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?
 

bkelly

Active Member
Joined
Jan 28, 2005
Messages
465
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.
 

BJungheim

Well-known Member
Joined
Jul 24, 2004
Messages
914

ADVERTISEMENT

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
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
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.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,885
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:

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

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Hi pcg,

I just want to point out that it's Bryan Kelly who is being so rude, and not Hedges.
 
Status
Not open for further replies.

Forum statistics

Threads
1,143,742
Messages
5,720,596
Members
422,292
Latest member
Bernd0501

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top