dateadd puzzle

[daybase]

An old and until now reliable function has suddenly started to return an error "invalid procedure name or argument"

Public Function nextdemand(per, dat) As Date
Dim FirstDate As Date ' Declare variables.
Dim IntervalType As String
Dim Numbr As Integer
Dim msg As String
Numbr = 1
If per = "2" Then
Numbr = 2
End If
If per = "4" Then
Number = 4
End If
If per = "M" Then
IntervalType = "m"
End If
If per = "W" Or per = "2" Or per = "4" Then
IntervalType = "ww"
End If
If per = "Q" Then
IntervalType = "q"
End If
'MsgBox (IntervalType)
nextdemand = DateAdd(IntervalType, Numbr, dat)
End Function

BUT if the msgbox command (which went in in debugging) is activated all works perfectly - but there is the irritation of a pop up box which I dont want or need - what am I doing wrong? All help greatly appreciated.

 

[xlbo]

Works fine for me - how are you calling the function?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Andrzejek]

Works for me as well, after fixing some missspelled Numbr:
Messed with your code a little:

Option Explicit

Private Sub Command1_Click()
    MsgBox nextdemand("2", [blue]CDate[/blue]("09-18-2008"))
End Sub

Public Function nextdemand(per [blue]As String[/blue], dat [blue]As Date[/blue]) As Date
Dim FirstDate As Date    [green]' Declare variables.[/green]
Dim IntervalType As String
Dim Number As Integer
Dim msg As String

Number = 1

[blue]Select Case[/blue] per
    Case "2"
        Number = 2
    Case "4"
        Number = 4
[blue]End Select[/blue]

[blue]Select Case[/blue] per
    Case "M"
        IntervalType = "m"
    Case "W", "2", "4"
        IntervalType = "ww"
    Case "Q"
        IntervalType = "q"
[blue]End Select[/blue]

[green]'MsgBox (IntervalType)[/green]
nextdemand = DateAdd(IntervalType, Number, dat)

End Function

Have fun.

---- Andy

 

[combo]

There could be too:
- no default IntervalType,
- probably shouldn't be 'e' in Numb[!]e[/!]r = 4


combo

 

[daybase]

well puzzled now - i have cut and pasted nextdemand = DateAdd(IntervalType, Numbr, dat) into each of the if/endif's and all works beautifully.

I am at a loss to explain either why it suddenly stopped working for me after months and months or why this solution works but... problem solved apparently so thank you all for taking the time to look.


If per = "W" Or per = "2" Or per = "4" Then
IntervalType = "ww"
nextdemand = DateAdd(IntervalType, Numbr, dat)
End If

If per = "Q" Then
IntervalType = "q"
nextdemand = DateAdd(IntervalType, Numbr, dat)
End If

 

[daybase]

Had completely overlooked CASE option - thanks for the reminder.

 

[daybase]

Altered code to case statments and unless the nextdemand = DateAdd(IntervalType, Numbr, dat) is inserted in each case statement i still get the error. Strange but true..

 

[DaveInIowa]

Combo's observation about IntervalType not being defaulted probably identified the problem. If parameter per's value is not 2, 4, M, Q or W; the DateAdd function will fail with the error message you're seeing.

Another suggestion would be to always use Option Explicit at the top of your module.

Building on Andy's code and removing unused variables and defaulting values:

Option Explicit

Public Function nextdemand(per As String, dat As Date) As Date
    Dim Interval As String
    Dim Number As Double
    
    Interval = "ww"
    Number = 1
    
    Select Case per
        Case "2", "4"
            Number = CDbl(per)
            Interval = "ww" '<- In case "ww" wasn't the default used above
            
        Case "m", "M", "q", "Q"
            Interval = LCase(per)
            
        Case "w", "W"
            Interval = "ww"
    End Select
    
    nextdemand = DateAdd(Interval, Number, dat)
End Function