Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting up a Month Array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting up a Month Array
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting up a Month Array
Otto,
This does not address your array question, but another way to get a short month; Sub test2() Shortmonth = Application.GetCustomListContents(3) '3 = third list mymonth = Shortmonth(3) ' 3 = third month End Sub Uses a built in list in Excel Don Pistulka "Otto Moehrbach" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting up a Month Array
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting up a Month Array
Dave
You are a marvel. I'll get into what you gave me and see what I can learn. I have John's book but I don't know why I didn't look in it first. Thanks again. Otto "Dave Peterson" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting up a Month Array
Don
I appreciate your help. I was not aware of this list. Otto "Don P" wrote in message ... Otto, This does not address your array question, but another way to get a short month; Sub test2() Shortmonth = Application.GetCustomListContents(3) '3 = third list mymonth = Shortmonth(3) ' 3 = third month End Sub Uses a built in list in Excel Don Pistulka "Otto Moehrbach" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help setting up and array formula in Excel 2003/XP | Excel Worksheet Functions | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Setting default pivot table field setting to "sum" | Excel Discussion (Misc queries) | |||
setting ctl to array of checkboxes yields type mismatch error. | Excel Programming | |||
setting ctl to array of checkboxes yields type mismatch error. | Excel Programming |