Dear Tushar

[patrickmuldoon99]

Thread at :http://www.mrexcel.com/board2/viewtopic.php?t=27893&start=30

You know what is absolutely amazing? No one -- not one of you -- provided Kelly with a solution. Maybe, you meant to, but you didn't. I was the first to point out that a function can simulate a constant array.

In addition to using a function, one can also implement a constant array with a read-only property or a write-once property in a class module.

Yet, none of you provided him with any of those possible solutions. Nor did anyone research the subject and come up with a way to implement a constant array.

So, don't get all high-and-mighty about wanting to discuss the topic of interest. None of you has done that! And, the topic of interest is *constant arrays* not arrays in general.
Quote:
Von Pookie, it had been laid to rest until Tushar resurrected it. He did not just discuss the subject at hand: arrays, but couldn't understand peoples reactions to OP2, and said so quite strongly.

With reference to the post that Tushar left - after which point the thread was locked; I have this for you my friend.


Tushar,

Kelly didn't want a solution. Kelly wanted an answer, an answer that has been a profound no.

Not all of us here are as blessed as yourself with such a wealth of knowledge and experience. I berate myself daily for this (as i'm sure others do), just waiting for that opportunity when one day I can even approach your brilliance.

As a beacon in the excel community, you have established that a class module can be used to implement the constant array. That is fantastic. Brilliant.

Unfortunately, you seem to disregard the following quotes that I have collected from this thread (which i'm really really really starting to suspect you haven't read) just to cast some illumination on the thought processes of us lesser mortals.

But whats the point, you probably only read the first line of this post and jumped onto your keyboard with some smart reply. But thats what you do isn't it.

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

Sub a()
Dim v

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

PGC answering the question by suggesting a workaround, following up on BJungheim's comments.It's not quite a function, but it's almost there.

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 above is Oaktree attempting to address the motivation for the post with some nice code.

It was pointed out that the split function was not available in XL 97, rendering the initial solution ineffective to someone on that platform, at which point the following code was posted:

      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

Tushar, have you actually read these posts?

 

[Asala42]

This is probably the kind of thing you take to PMs instead of the lounge. I read the original thread and there was some heavy angst there - no need to call him out here and relive it.

[edit: as an aside - I can definately see your side of it, btw. Still....[fill in the blank about bansticks and threadlocks] ]

 

[Oorang]

Let it go. The whole thing is much ado about nothing. This is a technical forum. If you have an issue with someone just PM them nicely and discuss it. No point in posting it all over the board.

 

[Von Pookie]

It was let go--no one had posted to this (or about it) since last week.

I was hoping to just let this particular thread slide off of the first page of the forum and into oblivion, but apparently not. Locked it just to cap the thing off.