ByRef - source address of argument

  • Hi everyone,

    In the formula of a cell, I call a user-defined function in which the arguments it receives are passed as ByRef.

    The function is defined thus:

    Function Next_Due_Date(ByRef start_date As Date, ByRef frequency_label As String)


    End Function

    Let's say cell C1 contains:

    =Next_Due_Date(A1, B1)

    in which A1 contains a date and B1 a text-string.

    Inside the function Next_Due_Date, I want to find out what the cell addresses were (A1 and B1) from which start_date and frequency_label came, in addition to the values they hold.

    Can this be done, please? I tried:

    a = start_date.Address

    but that produced an "invalid qualifier" compiler error.

    Thanks in anticipation.

  • It is supplied with a date (the first date on which a new repeat-delivery arrangement was set up), and a "repeat every..." text string like 1 month, 3 weeks, 60 days...

    It then looks at today's date and calculates when the next delivery is due, according to the original set-up date and the requisite interval between successive repeat deliveries.

    The set-up date, and the text string, should come from cells on the same row as the cell from which the function is called.

    Unfortunately there have been input errors in which this is not the case.

    So, as part of the function's process, I want to identify the row numbers from which the input arguments came, and make sure they are on the appropriate row.

  • Why do you need a UDF for that? You can use inbuilt formulas, which would be most efficient.

    Post your function, use Code Tags. If you don't know what Code tags are read the Forum Rules.

  • Roy,

    Thanks for responding.

    I'm not a "professional" programmer, but self-taught, and I occasionally write macros to help friends, charities, etc to automate tasks in Excel. So I'm afraid efficiency of programming is not my strong point, I just write code that I can read and understand if and when I need to go back to it in future.

    I'm attaching a sample workbook.

    Thanks again for seeking to help.

  • I see what you are doing now, but I don't think you can check the cells used within the function.

    If the user just drags the formula down to the next row it will work. Better still would be to convert the data to a Table then as ows are added all formats and formulas will be inserted automatically.

    Overview of Excel tables

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!