Declaring Constant Arrays?

[HedgePig]

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

 

[patrickmuldoon99]

Another 20 cents;

Const sArr As String = "1,3,5,7" 

Sub a() 
Dim v 

For Each v In Split(sArr, ",") 
    MsgBox CInt(v) 
Next 
End Sub

If we're talking efficiency here, surely simply having an array in the first place is more efficient than this code? This way, the array needs to be split then accessed by a identifier. Having the array as an array in the first place means you can access based on whatever index you like.

Obviously, if you're going for 'robustness, flexibility and maintainability' then I guess I have these words...

Robustness: Provided it is coded correctly, having the array as a variable as opposed to a constant should make no difference. The robustness will only be compromised if the code itself interferes with the values in the array. If it is programmed correctly, it will not. If you want to include efficiency in here, then granted, the non-constant array will be evaluated every time it is used.

Flexibility: By definition, a constant is not flexible. You cannot change its value unless you go into the code and change it there. This is a similar effect to declaring a variable as something, and making sure no code interacts with it.

Maintainability: Well, bit of a laymans attitude, but just declare the array at the top of the code if you like. So long as it's in scope, it doesn't matter anyway. In fact, using a variable to store the array makes it in fact more maintainable, as you can set it up as public, and can then create methods that will adapt the values to suit given certain scenarios.

 

[pgc01]

Hi Glenn

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

Thanks for pointing it out. I have already edited the post.

Hedges, if you had already read it, i'm sorry, it was not meant for you.

Best regards
PGC

 

[bkelly]

While my post was not intended to be rude, I accept that it did appear to be that way. I do appologize.

I have a system containing dozens of computers (not figuratively, literally) generating gigabytes of data that must be checked for accuracy and must be checked very quickly under changing conditions. I recognize that this is no excuse for being rude.

When people spend a lot of time working with a product then tend to develope a feeling of ownership and pride, and with that, sometimes a defensive attitude. That is the way people are.

Excel does some wonderful things, but it is also riddled with inconsistancies and some down right idiot methods and concepts.

I am not high and mighty by any means, but when someone does not understand why an array of constants might be needed they do need a lecture on software engineering. This is not that place so a good book would be appropriate.

Please notice that in the past I have taken the time to profusely thank the people here. I do recognize that they are far above average in intelligence and ability. I do recognize that I could not have accomplished many of the things I have done with Excel without the help found here.

But there is a paradox here. A utility such as Excel that requires that much level of knowledge to accomplish some of the simple tasks has some serious problems.

To be a bit redundant, I accept my chastisment and will endeavor to be a more polite. I am now about four hours into another 12 to 14 hour day of a three week venture and must be on my way.

Thank you for accepting my foibles as a human and thanks for taking the time to answer my questions.

 

[patrickmuldoon99]

am not high and mighty by any means, but when someone does not understand why an array of constants might be needed they do need a lecture on software engineering. This is not that place so a good book would be appropriate.

You really are unbelieveable my friend. The idea of typecasting appears to be new to you from an earlier post. Who needs a lecture in software engineering?

I have a system containing dozens of computers (not figuratively, literally) generating gigabytes of data that must be checked for accuracy and must be checked very quickly under changing conditions. I recognize that this is no excuse for being rude.

And who in their right mind uses excel for a task of this magnitude??

I hate to keep on, but could you give me one piece of unsound reasoning in any of my comments regarding an array of constants in VBA?

 

[pgc01]

Hi Patrick

I agree with most of what you say, but I have to tell you that I the idea of having constants inside the code gives me the creeps.

If you have worked in big projects with big teams you get used to have rules like that.
If it's multilanguage projects or really big ones you are sometimes even forbidden to have any constant in any module with code. You have mudules just for the constants that are then inserted or linked at different stages to the code modules.
Imagine selling the product to another country and to look inside the code for where are the strings to translate and where are all the columns, lines, widths, etc, that you'll have to change. It would be a nightmare.
The same if you want to change layouts in a big project.

Over the years these things get inside your skin and maybe you end up overdoing it in simple cases where there is no real demand for such rigour.

Anyway I think is that there is no optimal solution in BASIC, and I don't find it a bad think. BASIC is, as the name says basic. It's a great language for simple projects. In some cases you know you will not have the perfect solution from the point of view of software engineering. You have to expect that and to make your own choices.

Best regards
PGC

 

[patrickmuldoon99]

PGC

I do see what you mean, my comments have been founded under the assumption that the project this gentleman is involved in is one that is suited to Excel. Obviously, methodologies and such like would require a certain amount of strictness, something with VBA doesn't really cater for. My logic and reasoning was based on the capabilities of Excel and VBA, and all of my comments are based on what one can do in this environment.

Thanks for the well put comments, in a perfect tone for this board.

Patrick


P.S, BKelly, I await with baited breath your forthcoming book - 'Using Excel for ridiculous tasks, brought to you by Mr Arrogance'

 

[GlennUK]

I see the rudeness has not stopped ...

when someone does not understand why an array of constants might be needed they do need a lecture on software engineering

... Bryan Kelly, it is your ignorance that is so frustrating, as you jump to the conclusion that when someone asks why you need an array:

Oaktree:
Then again, why do you need the array of days in a month at all?

... that it means that they cannot understand the concept of array constants. That's what I mean by ignorance.

 

[Richard Schollar]

I'd like to add that I thought Bryan made a quite magnanimous apology in his last post above - to me it's clear he did not set out to offend and has apologised for any offence he did inadvertantly cause. It's time to let the matter drop.

 

[jindon]

Hey guys,

Split function is not available on '97....

 

[patrickmuldoon99]

I'd like to add that I thought Bryan made a quite magnanimous apology in his last post above - to me it's clear he did not set out to offend and has apologised for any offence he did inadvertantly cause. It's time to let the matter drop.

Agreed, an apology was present, but he couldn't resist another dig in questioning my professional integrity as a software designer. That is all I take issue with after taking time out of my life to help the guy.

But you're correct, we should let the issue go, so this is my last comment.

PGC and Glenn, thankyou for your support.

Jindon, i'm sure you're very aware of this anyway, but in case anyone is viewing this post and wants to use split in XL 97, you can use this, courtesy of the Microsoft site:

      Public Function Split(ByVal sIn As String, Optional sDelim As _
            String, Optional nLimit As Long = -1, Optional bCompare As _
             VbCompareMethod = vbBinaryCompare) As Variant
          Dim sRead As String, sOut() As String, nC As Integer
          If sDelim = "" Then
              Split = sIn
          End If
          sRead = ReadUntil(sIn, sDelim, bCompare)
          Do
              ReDim Preserve sOut(nC)
              sOut(nC) = sRead
              nC = nC + 1
              If nLimit <> -1 And nC >= nLimit Then Exit Do
              sRead = ReadUntil(sIn, sDelim)
          Loop While sRead <> ""
          ReDim Preserve sOut(nC)
          sOut(nC) = sIn
          Split = sOut
      End Function

Patrick