[Solved] Basic function nested cell reference gives #VALUE!

[renbo]

I have a simple OpenOffice Basic based function that, given a start date, finds the next Bi Monthly pay date. The function works fine when passed a cell reference that contains a date value, but does not work when passed a cell that contains the result of this same function.

Example:

Cell B1 Contains: 09/30/2022
Cell C1 Contains: =NEXTBIMONTHLYPAYDATE(B1+10) (this correctly resolves to 10/14/2022 for the cell value)
Cell D1 Contains: =NEXTBIMONTHLYPAYDATE(C1+10) (this resolves to #VALUE!)

Am I missing something? Or is it not possible to have cell values with user defined functions reference in this way?

[FJCC]

Can you post a document that illustrates the problem? To upload a file, click Post Reply and look for the Attachments tab just below the box where you type a response.

For a quick test, what is the result of

Code: Select all

=C1 + 10

If that is #Value, then your function is not returning a number.

[renbo]

Ahhh, that got me closer to understanding. It is not a number, it's a date, but I was under the impression that dates can be acted upon like numbers. And that seems to be the behavior for the +10 in C1.

I have attached a sample document.

[Villeroy]

A date in a spreadsheet IS a number and any number in a spreadsheet can be interpreted as a date. Calc has 2 data types:
1. Text
2. Numbers
Formulas may return error values as a 3rd type.
In Excel Boolean is a separate data type. In Calc False is equivalent to 0 and True is equivalent to 1.
StarBasic is a completely different issue. [Tutorial] Date-Time Conversion in StarBasic
I would do that simply on sheet with formulas instead of macros.

[renbo]

The function I have written, although simple, is not easily represented in a formula:

Code: Select all

Function NextBiMonthlyPayDate(optional inDate as Date) as Date
	
	Dim outDate as Date
	if IsMissing(inDate) then
		outDate = Date()
	else
		outDate = inDate
	end if
	
	if Day(outDate)>=15 then
		' go for end of month
		if Month(outDate)=12 then
			outDate = DateSerial(Year(outDate)+1, 1, 1)-1
		else 
			outDate = DateSerial(Year(outDate), Month(outDate)+1, 1)-1
		end if
		while WEEKDAY(outDate)=1 or WEEKDAY(outDate)=7 
			outDate=outDate-1
		wend
	else
		' go for 15th
		outDate = DateSerial(Year(outDate), Month(outDate), 15)
		while WEEKDAY(outDate)=1 or WEEKDAY(outDate)=7 
			outDate=outDate-1
		wend
	end if
	
	NextBiMonthlyPayDate=outDate
	
End Function

I just want to make sure I am understanding your answer correctly. If you have a function that is used to calculate logic that is not easy to put into a typical formula, any cell whose value is the result of that function cannot be used as that function's output type?

[Villeroy]

This should do the job much better than a clumsy, slow and error probe Basic program:

Code: Select all

=DATE(YEAR(A1);MONTH(A1)+(DAY(A1)>14);IF(DAY(A1)<=15;15;1))+CHOOSE(WEEKDAY(CURRENT();2);0;0;0;0;0;2;1)[code]

[renbo]

This formula you provided:

Code: Select all

=DATE(YEAR(A1);MONTH(A1)+(DAY(A1)>14);IF(DAY(A1)<=15;15;1))+CHOOSE(WEEKDAY(CURRENT();2);0;0;0;0;0;2;1)

Does not return the same value as this formula using my function:

Code: Select all

=NEXTBIMONTHLYPAYDATE(A1)

IMHO, the reason customized functions exist is to make somewhat complicated logic more easily utilized in a spreadsheet.

Regardless, I am not trying to argue that point. Nor do I think statements like "clumsy, slow and error prone" are particularly helpful.

I simply wanted to know if Calc supported a specific capability, one that I have used elsewhere extensively. I'm really trying to find a way to use Calc instead of Excel, and get to using open source products.

[Villeroy]

YOU are the developer of your Basic program. So debug your code. You've got to check the types of the incoming parameter. It is always a Double unless it is String. A blank cell is not isMissing. A blank cell is 0.
I pointed you to a well document date/time converter in StarBasic.

[Villeroy]

With some fine tuning I get the same results. A named formula expression with a mixed reference to the date column simplifies the input.

[karolus]

The while…wend should occur exactly once and outside the if…end if

Code: Select all

Function Nex_bi(optional inDate as Date) as Date
	
	Dim outDate as Date
	if IsMissing(inDate) then
		outDate = Date()
	else
		outDate = inDate
	end if
	
	if Day(outDate)>=15 then
		' go for end of month
		if Month(outDate)=12 then
			outDate = DateSerial(Year(outDate)+1, 1, 1)-1
		else 
			outDate = DateSerial(Year(outDate), Month(outDate)+1, 1)-1
		end if
	
	else
		' go for 15th
		outDate = DateSerial(Year(outDate), Month(outDate), 15)
	end if
	
	while WEEKDAY(outDate, 2)>5  '# realize the 2nd Argument for Weekday
		outDate=outDate-1
	wend
	
	Nex_bi=outDate
	
End Function

[MrProgrammer]

Hi, and welcome to the forum.

The function works fine when passed a cell reference that contains a date value, but does not work when passed a cell that contains the result of this same function.

If A1 contains your =NEXTBIMONTHLYPAYDATE(…) formula, tests with =ISNUMBER(A1) and =ISTEXT(A1) show that your function does not return Calc dates (which are always numbers) but text. You should not expect to do arithmetic like A1+10 with a cell which contains text. If you want to do arithmetic with the result of your function, the result of the fuinction is wrong because it is the wrong type. I do not offer to fix your Basic function.
Read section 3. Dates in cells in Ten concepts that every Calc user should know.

The function I have written, although simple, is not easily represented in a formula:

Given a date in A2, formula =A2-DAY(A2)+IF(DAY(A2)>=15;DAYSINMONTH(A2);15)-CHOOSE(WEEKDAY(CURRENT());2;0;0;0;0;0;1) returns the same date as your 26-line Basic function and returns it as a date (a number) so you can do arithmetic with it. This attachment shows that my Calc formula returns the same date as your Basic function for every day in 2000.

202209191138.ods

(30.56 KiB) Downloaded 58 times

[Tutorial] Calc date formulas

Cell C1 Contains: =NEXTBIMONTHLYPAYDATE(B1+10) (this correctly resolves to 10/14/2022 for the cell value)

Using the English(USA) locale, the value in C1 is the 10-character text string 10/14/2022. You can't add 10 to that string. If your function would return a Calc date, the value would be the number 44848. You can add 10 to that and get 44858, which when formatted as a date would be the 24th of October in the year 2022.


If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[renbo]

I will mark this as [SOLVED], but discovered the following (for other's reference):

When I created a new document, created a new module, added the function to it exactly as it was written before, it started working. I cannot explain why, I can see no difference between the two, I can only surmise something got corrupted on the first document.

At any rate, no further investigation is needed. And thanks for those who took the time to look at the issue.

[pkan]

I am having a similar problem. I get #VALUE error in my sub-total and total in a particular column when I enter an alpha character in stead of a number. Only in this one column. Column is formatted for currency.

Edit: Locked to prevent answers to the last post which is a duplicate of a separate thread. FJCC