Oops. I missed that part of the question.
If you know how big the array will be, you can declare it with the right
dimensions when you start:
Dim myMonths(1 To 12)
Then when you populate the each of the elements of that array, you can refer to
them using an index.
dim myNum as long
mynum = 3
msgbox mymonths(mynum)
One way to populate that array is like this:
myMonths(1) = "jan"
myMonths(2) = "feb"
myMonths(3) = "mar"
myMonths(4) = "apr"
myMonths(5) = "may"
myMonths(6) = "jun"
'etc
myMonths(12) = "dec"
If you don't know the dimensions (or you want to add things to the array), you
can declare it like:
dim myMonths() as string
After you find out how many you need, you can do:
redim mymonths(1 to 12)
Say you want some months, but not all: If you've already popululated some of
the elements, you can preserve what you did by:
dim myMonths() as string
redim myMonths(1 to 2)
mymonths(1) = "Jan"
mymonths(2) = "feb"
'something changes, so you have to add one more element
redim preserve mymonths(1 to 3)
mymonths(3) = "Dec"
You can also create an array by declaring the variable as a variant and making
it an array later:
dim mymonths as Variant
...
mymonths = array("jan","feb","mar","apr")
this creates a 4 element array. But the indexing depends on how an option is
set in the module. If you have "option base 0" (or no "option base" statement
at all, the first element is element 0.)
If you have "option base 1", then the first element is element 1.
If I were dealing with months, I'd want to be careful about the base number. I
think I'd put explicitly put "option Base #" in my module. Then whatever I
chose, I'd adjust the code that way.
option base 1
''all the junk with mymonths: mymonths = array("jan","feb","mar","apr")
msgbox mymonths(3) 'would return Mar.
Option base 0
''all the junk with mymonths: mymonths = array("jan","feb","mar","apr")
msgbox mymonths(3) 'would return Apr.
=====
And if you really don't care about the base, you can cycle through the array by
just using the lbound() and ubound() functions.
dim ictr as long
for ictr = lbound(mymonths) to ubound(mymonts)
msgbox mymonths(ictr) & "--" ictr
next ictr
===
And you can even declare weirder looking arrays:
dim myArray(-5 to 5)
and you have 11 elements. Might be useful someday.
And you can have multidimensions in your array:
dim myArray2(1 to 5, 3 to 9, -3 to -2, 1 to 1)
It would have 5*7*2*1 = 70 elements. (I can't think of any reason for doing
this, but sometimes it makes sense to make the indexing match the data.)
And if you have a multidimensioned array, you can do that redim stuff, but you
can only change that last dimension if you want to preserve the existing
elements.
====
I looked John Walkenbach's Power programming with VBA (for xl2000) and John
Green's (et al) Excel 2002 VBA and they both cover arrays.
I'd visit your local bookstore and decide which is a better fit for you (and
compare similar versions, too!) Each of those books is so thick, I'd look for
the best overall fit. I wouldn't base it on just this one subject.
======
And one other neat thing to do with arrays is to pick up a range from a
worksheet and populate an array:
dim myRng as range
dim myArr as variant
set myrng = worksheets("sheet1").range("a1:a10")
myarr = myrng.value
This creates a two dimensional array: 10 x 1.
If you were declaring that array, it would look like:
dim myArr(1 to 10, 1 to 1) as variant
If you pick up multiple columns, you could do this:
dim myRng as range
dim myArr as variant
set myrng = worksheets("sheet1").range("a1:e10")
myarr = myrng.value
This would look like:
dim myArr(1 to 10, 1 to 5) as variant
Then you could loop through the dimensions:
for iRow = lbound(myarr,1) to ubound(myarr,1)
for iCol = lbound(myarr,2) to ubound(myarr,2)
msgbox myarr(irow,icol)
next icol
next irow
And don't forget the online help, too.
Otto Moehrbach wrote:
Dave
You have solved my problem again. Thanks.
Do you know of a source for learning how to work with arrays in VBA? I am
sorely lacking in knowledge of how to setup an array, declare an array,
access an array. I thought I would learn from your response to my question
in this thread but you didn't respond to that part of my question. What you
gave me is far better than using an array and I thank you for that, but I am
always trying to learn more. Thanks again. Otto
"Dave Peterson" wrote in message
...
how about (for xl2002):
MsgBox MonthName(myNum, True)
or befo
Option Explicit
Sub testme01()
Dim myMonth As String
Dim myNum As Long
myNum = 3
myMonth = Format(DateSerial(2003, myNum, 1), "mmm")
MsgBox myMonth
End Sub
Otto Moehrbach wrote:
Excel 2002, Win XP
I have a need to get a Text month from a number month. IOW, if I have a
3,
I want to get "Mar" without the quotes.
I think I need to set up an array something like:
Months(Array("Jan","Feb","Mar" etc))
Then Months(3) would get me "Mar".
Obviously I don't know the code to do this. If someone could help me
with
this, it would be much appreciated.
If there is an easier way to get "Mar" from 3 that would help too.
However,
I would still like to have help with setting up an array like the above
because I need the education. Arrays are new to me as you can tell.
Thanks for the help. Otto
--
Dave Peterson
--
Dave Peterson