0

I'm creating a function that has as a parameter a day of the week (Monday, Wednesday, Thursday ...) that captures the day of the respective month of the week and returns its day in the cell, on Sundays the day value is reset

Function dayReference(week as Integer)

    If Weekday(Date, 1) = week Then
        ActiveCell.FormulaR1C1 = Day(Date)
    ElseIf Weekday(Date, 1) = 1 Then
        ActiveCell.FormulaR1C1 = ""
    End If        

End Function

I'm having 2 problems:

  1. With a parameter I cannot call the function either manually or through the spreadsheet, without the parameter I can call it manually.

Example: in the cell of my spreadsheet if I type dayReference (7) excel asks to create a macro or select an existing one, as my model is a function and not a macro I can't call it

  1. My goal is not to have to execute this script manually by the editor, but to have the behavior of native functions like Day and Date, which are dynamic, and I can make the call in any cell

What is the most efficient way to create it using ?

Improve this question
4
  • Can you give an example, the first sentence of the question is not clear. Commented Aug 22, 2020 at 19:59
  • The is callable from the any worksheet, if you put it in a module or if you put it in a worksheet and put Public in front of the word Function. Commented Aug 22, 2020 at 20:01
  • @Vityata Adding the public function to excel requires a module to run it, and when placing the function inside the module this error is returned: i.imgur.com/smSFeDQ.png, Commented Aug 22, 2020 at 20:35
  • 1
    Ctrl+G to bring up the immediate pane (your new debugging best friend; that's where Debug.Print statements send their output), and then type ?dayReference(3) and you'll invoke the function with parameter value 3 when you hit ENTER, and the ? is shorthand for Print and so after the function returns it should print its return value - but your function here doesn't return anything. Functions shouldn't have side-effects like this. Commented Aug 22, 2020 at 21:03

2 Answers 2

Reset to default
6

Functions should take an input, compute a result, and then return that result; the return type of your function is implicitly Variant, and its return value is never assigned, so callers can only ever receive a Variant/Empty value out of it.

[...] and returns its day in the cell

Ok, I see the idea now - simple misunderstanding. If what you want is ultimately a UDF (user-defined function) that is invoked from a formula in a cell:

=DayReference(D3)

There are a number of rules for this to work.

  • First rule is that functions cannot have side effects: running it should not affect any cells, sheets, Application state, or anything in global scope.

  • Second rule is that functions should normally take all their dependencies as parameters, such that invoking it with the same parameters reliably yields the same output. For example if a function needs a Date value, it should take that date as a parameter instead of just working with whatever the current date is. That way you can test and prove that the function returns the expected output given a Sunday.

    Public Function DayReference(ByVal wkDay as Long, ByVal refDate As Date) As Variant

    If Weekday(refDate, vbSunday) = wkDay Then
        DayReference = Day(refDate)
    ElseIf Weekday(Date, 1) = vbSunday Then
        DayReference = ""
    End If        

End Function

The function returns a Variant/Long (the day of the refDate) when the WeekDay of the specified refDate is the specified wkDay value, and a Variant/String (and empty string) when that weekday is vbSunday.

You could invoke it like this in a cell:

 =DayReference(A1,TODAY())

Or like this in VBA code:

Dim d As Variant
d = DayReference(Sheet1.Range("A1").Value, Date)
Debug.Print d

Or from the immediate pane (Ctrl+G):

?DayReference(vbSaturday, DateSerial(2020, 8, 22))
 22
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

The content was very enlightening, I didn't understand the typing very well but now it's clear to me, thank you very much
0

Try it in this way, please:

Function dayReference(week As Integer)
    If Weekday(Date, 1) = week Then
        dayReference = Day(Date)
    ElseIf Weekday(Date, 1) = 1 Then
        dayReference = ""
    End If
End Function

The function must be copied in a standard module.

It can be called from a cell, in this way:

=dayReference(7)

It will return 0 if none of the two conditions is True.

Edited:

If you want calling it, not from a cell, you can do it in this way:

Sub testdayReference()
  Debug.Print dayReference(7)
  'or
  ActiveCell.Value = dayReference(1)
End Sub
Improve this answer

7 Comments

When I make the call, a module is required -> i.imgur.com/aHGhPhy.png
@luis matheus: What do you mean? Don't you need a UDF function able to return, in the cell where from it is called, the current day according to the two conditions? Do you need something to return a formula?
Did you try running it directly from IDE?
No, what you did is exactly what I want, typing the formula in the cell will return the value. Now that I tested it, it returned "#VALUE!" in the cell
@luismatheus any function that has side-effects is going to do that when invoked from a cell. You need the function to RETURN its result, not have it written into ActiveCell - the cell invoking the function is not necessarily the active cell!
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.