Function help, invalid outside procedure

[sastoka]

Hi all,

Excel 2010 / windows 7

I'm trying to assign a value to the current month via 2 function then return it to a destination. I'm all new to function, so I keep getting the error as Invalide outside procedure.


First Function

to assign a number to the current month

Funtion Nmonths()

Dim Nmonth As String


If month = "2014-07" Then
    Nmonth = "1"
    ElseIf month = "2014-08" Then
        Nmonth = "2"
        ElseIf Format(Date, "yyyy-mm") = "2014-09" Then
            Nmonth = "3"
            ElseIf Format(Date, "yyyy-mm") = "2014-10" Then
                Nmonth = "4"
                ElseIf Format(Date, "yyyy-mm") = "2014-11" Then
                    Nmonth = "5"
                    ElseIf Format(Date, "yyyy-mm") = "2014-12" Then
                        Nmonth = "6"
                        ElseIf Format(Date, "yyyy-mm") = "2015-01" Then
                            Nmonth = "7"
                            ElseIf Format(Date, "yyyy-mm") = "2015-02" Then
                                Nmonth = "8"
                                ElseIf Format(Date, "yyyy-mm") = "2015-03" Then
                                    Nmonth = "9"
                                    ElseIf Format(Date, "yyyy-mm") = "2015-04" Then
                                        Nmonth = "10"
                                        ElseIf Format(Date, "yyyy-mm") = "2015-05" Then
                                            Nmonth = "11"
                                            ElseIf Format(Date, "yyyy-mm") = "2015-06" Then
                                                Nmonth = "12"
                                                End If
                                                
      
End Function

Second function

To assign a month in letter to the current month

Function Lmonth()
      
Dim Lmonth As String
      
If Format(Date, "yyyy-mm") = "2014-07" Then
    Lmonth = "July"
    ElseIf Format(Date, "yyyy-mm") = "2014-08" Then
        Lmonth = "August"
        ElseIf Format(Date, "yyyy-mm") = "2014-09" Then
            Lmonth = "September"
            ElseIf Format(Date, "yyyy-mm") = "2014-10" Then
                Lmonth = "November"
                ElseIf Format(Date, "yyyy-mm") = "2014-11" Then
                    Lmonth = "October"
                    ElseIf Format(Date, "yyyy-mm") = "2014-12" Then
                        Lmonth = "December"
                        ElseIf Format(Date, "yyyy-mm") = "2015-01" Then
                            Lmonth = "January"
                            ElseIf Format(Date, "yyyy-mm") = "2015-02" Then
                                Lmonth = "February"
                                ElseIf Format(Date, "yyyy-mm") = "2015-03" Then
                                    Lmonth = "March"
                                    ElseIf Format(Date, "yyyy-mm") = "2015-04" Then
                                        Lmonth = "April"
                                        ElseIf Format(Date, "yyyy-mm") = "2015-05" Then
                                            Lmonth = "May"
                                            ElseIf Format(Date, "yyyy-mm") = "2015-06" Then
                                                Lmonth = "June"
                                                End If



End Function

Then come the calling macro

Sub Definemonth()


Range("B3").FormulaR1C1 = Nmonth & ". " & Lmonth

End Sub

I'm sure it's a easy thing to fix but I don't know.

If you've got a minute I would greatly appreciate.

Many thanks

 

[skywriter]

Try putting the word public in front of each function if the sub and functions are in different modules.

You spelled function as funtion in the Nmonths function.


Public Function Lmonth()
Public Function Nmonths()

 

[StephenCrump]

Or just replace all this with

Range("B3").Formula = "=MOD(MONTH(TODAY())+5,12)+1 & ""."" & TEXT(TODAY(),""mmmm"")"

 

[Rick Rothstein]

If I understand your two functions correctly, these shorter functions should work the same with the added benefit that you won't have to update a hard-coded year in the future...

Public Function Nmonth()
  Nmonth = Month(Date) - 6
End Function

Public Function Lmonth()
  Lmonth = Format(Date, "mmmm")
End Function

As for your macro, it looks like you can just do this...

Sub Definemonth()
  Range("B3").Value = Month(Date) - 6 & ". " & Format(Date, "mmmm")
End Sub

Note I assign the value directly to the Value property instead of the FormulaR1C1 as you appear to only be assigning a constant to the cell

 

[sastoka]

Many thanks, Rick,

worked perfectly,

thanks all.