How to get current month?

A really helpful and simple way is to combine the format function together with date.

Examples (assuming today is Oct 23, 2019):

To get current month as a number as in original question:

MsgBox Format(Date, "mm")

^ Will return: 10

To get current month as short text:

MsgBox Format(Date, "mmm")

^ Will return: Oct

To get current month with full text:

MsgBox Format(Date, "mmmm")

^ Will return: October

You can combine these with days and years as well.

Additional examples:

MsgBox Format(Date, "dd-mmm-yyyy")

^ Will return 23-Oct-2019

MsgBox Format(Date, "dddd-mmmm-dd-yyyy")

^ Will return: Wednesday-October-23-2019

This is creating a custom format, so you can rearrange the dd, mm, yyyy areas as you see fit, such as:

MsgBox Format(Date, "yyyy/mm/dd")

^ Will return: 2019/23/10

Try,

debug.print Format(Date, "mmm")       'Mar
debug.print Format(Date, "mmmm")      'March
debug.print Format(Date, "m")    '3
debug.print Format(Date, "mm")   '03

 Month(Now)

Returns the index number associated with the current month.

Jeeped's code below is the most compact, but to give you an idea of how indexes work, the following code will return the month name based on the index returned:

Dim months(11) As String
months(0) = "Jan"
months(1) = "Feb"
months(2) = "Mar"
months(3) = "Apr"
months(4) = "May"
months(5) = "Jun"
months(6) = "Jul"
months(7) = "Aug"
months(8) = "Sep"
months(9) = "Oct"
months(10) = "Nov"
months(11) = "Dec"

Dim nowMonth As Integer
nowMonth = Month(Now)

For i = 0 To 11
  If nowMonth = (i + 1) Then
     MsgBox (months(i))
  End If
Next

Found an easier solution to get the current Month Name

Just use MonthName(Month(Now)) and assign it to a string.

Month(Now) gives you the month number and MonthName() uses that number to display the current month