Return string element from array given index

Question

I have the following code:

     Dim CurrMonth As Integer
     Dim MonthPos As Variant
     Dim CurrPos As Integer
     
     
     CurrMonth = Month(Date) - 1
     MonthPos = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")

Could anyone provide guidance on how I would be able to get (in this case) the string element from the Array MonthPos given the index CurrMonth.

FaneDuru · Accepted Answer · 2022-05-31 09:46:35Z

2

Please, use the next adapted way:

Sub extractMonth()
  Dim CurrMonth As Long, MonthPos As Variant, CurrPos As Integer, prevMonth
 
     CurrMonth = Month(Date) - 1
     MonthPos = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
     prevMonth = MonthPos(CurrMonth - 1) '- 1 because it is a zero based 1D array...
     Debug.Print prevMonth 'the previous month (I could see Month(Date) - 1) and I supposed that this is needed.
                           'otherwise, you should simple use MonthPos(CurrMonth), for the current month
End Sub

edited May 31, 2022 at 9:46

answered May 31, 2022 at 9:40

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pᴇʜ · Accepted Answer · 2022-05-31 09:47:34Z

1

https://www.softwaretestinghelp.com/vba-array-tutorial/#One_Dimensional_Array offers following example:

Private Sub arrayExample3()
Dim thirdQuarter(13 To 15) As String 'creates array with index 13,14,15
thirdQuarter(13) = "July"
thirdQuarter(14) = "Aug"
thirdQuarter(15) = "Sep"
MsgBox "Third Quarter in calendar " & " " & thirdQuarter(13) & " " &
        thirdQuarter(14) & " " & thirdQuarter(15)
End Sub

So for your case should be:

MonthPos(CurrMonth)

edited May 31, 2022 at 9:47

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

answered May 31, 2022 at 9:36

Reporter

3,9345 gold badges36 silver badges50 bronze badges

Comments

Pᴇʜ · Accepted Answer · 2022-05-31 09:54:18Z

1

Instead of the array you can also use

Format$(date,"MMMM")

or if you need the previos month then

Format$(DateSerial(Year(Date),Month(Date)-1,1),"MMMM")

to get the name of the month in the language of the operating system.

answered May 31, 2022 at 9:54

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

1 Comment

T.M. Over a year ago

... or Application.Text to get the month name in VBA's EN-US standard +:) - Fyi Added approach via evaluation allowing any months increments|decrements.

T.M. · Accepted Answer · 2022-05-31 19:00:51Z

Assuming Excel (to evaluate the months name array in one go) the following approach allows to input any increments or decrements as optional second argument:

Function getMonth(currMonth As Long, Optional MonthOffset As Long = 0)
'a) build array of month names via evaluation (Excel)
    Dim months(): months = [Text(Date(0,Column(A:L),1),"mmmm")]
'b) calculate wanted month
    currMonth = (currMonth + MonthOffset + 12) Mod 12
    If currMonth = 0 Then currMonth = 12
'c) return month name
    getMonth = months(currMonth)
End Function

Example call

Simply pass a possible decrement as 2nd argument:

   Debug.Print getMonth(1)              ' ~~> returns January
   Debug.Print getMonth(1,-1)           ' ~~> returns December
   'assuming call as of 5/31 2022, i.e. equalling getMonth(5) 
   Debug.Print getMonth(Month(Date))    ' ~~> returns May
   Debug.Print getMonth(Month(Date),-1) ' ~~> returns April

Collectives™ on Stack Overflow

Return string element from array given index

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related