ByRef - source address of argument

PatrickOfLondon · Apr 17th 2020

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.

**royUK** · Apr 17th 2020

What's the Function for?

PatrickOfLondon · Apr 17th 2020

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.

**royUK** · Apr 17th 2020

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.

PatrickOfLondon · Apr 17th 2020

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.

Code

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

sdate = start_date
 
If sdate >= Date Then
    Next_Due_Date = sdate
    Exit Function
End If

flen = Len(frequency_label)
If flen = 0 Then
    Next_Due_Date = " what repeat frequency?": Exit Function
End If
    

frequency_label_first_space_position = InStr(frequency_label, " ")
fqy = 1 * Left(frequency_label, frequency_label_first_space_position)
' frequency_label comes from a data-validated cell where users pick from a list of prescribed strings like:
' 30 days (use for calendar-monthly repeats)
' 0 days (use for one-off requests)

interval_initial_setting = "unknown"
interval_code_for_DateAdd = interval_initial_setting
' if it's still this after passing through the stages below,
' we don't know what to do, so return an indication of that
' each stage below is listed in order of likelihood of occurrence:
' no point in going through unnecessary stages each time around
    If fqy = 0 Then Next_Due_Date = Space(20) & "--- one-off ---": Exit Function
    If fqy = 30 Then interval_code_for_DateAdd = "m": date_add_qty = 1: GoTo calculate_next_due_date
    If fqy = 7 Then interval_code_for_DateAdd = "d": date_add_qty = 7: GoTo calculate_next_due_date
    If fqy = 60 Then interval_code_for_DateAdd = "m": date_add_qty = 2: GoTo calculate_next_due_date
    If fqy = 90 Then interval_code_for_DateAdd = "m": date_add_qty = 3: GoTo calculate_next_due_date
    If fqy = 14 Then interval_code_for_DateAdd = "d": date_add_qty = fqy: GoTo calculate_next_due_date
    If fqy = 21 Then interval_code_for_DateAdd = "d": date_add_qty = fqy: GoTo calculate_next_due_date
    If fqy = 28 Then interval_code_for_DateAdd = "d": date_add_qty = fqy: GoTo calculate_next_due_date
unknown:
    If interval_code_for_DateAdd = interval_initial_setting Then Next_Due_Date = "???": Exit Function

calculate_next_due_date:
    initdate = sdate
    nextdate = initdate
looper:
    nextdate = DateAdd(interval_code_for_DateAdd, date_add_qty, nextdate)
    
    If nextdate < Date Then GoTo looper


Next_Due_Date = nextdate
End Function

Display More

I'm attaching a sample workbook.

Thanks again for seeking to help.

**royUK** · Apr 17th 2020

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

PatrickOfLondon · Apr 17th 2020

Roy,

Many thanks. I'll explore the Tables concept.

**royUK** · Apr 18th 2020

Post back if you need help.

ByRef - source address of argument

Participate now!

Share

Users Viewing This Thread