How to test for null value in a cell with a macro

LewG2 · July 7, 2025, 6:47pm

Four formulas below. I think the issue is testing for “” in formula, but not sure.

(A) If I manually type this into k3, or paste it into any cell, it works.
=IF(G3="",-J3*D3,-100*J3*D3)

If I use these in a macro, it compiles but produces “FALSE” or errors:

(B) CF_formula = “=if(G3=”",=D3*J3,=-100*D3*J3)"
suggests change to =if(G3=",=D3*J3,=-100*D3*J3"),
note locations of dbl quotes
– if I accept the change
it results in FALSE
– if I reject the change it enters =IF(G3=",=d3*j3,=-100*d3*j3))
note that it replaces second quote above with extraneous “)”
and results in #NAME?

If I then manually correct the bolded formula immediately above to read as in (A), which works, it displays “Err:510”

(C) CF_formula = “=if(G3=0,=D3*J3,=-100*D3*J3)”
It doesn’t suggest a change and
displays Err:510. Same result if I manually correct it to (A)

(D) CF_formula = “=if(G3=Chr$(34)+Chr$(34),=D3*J3,=-100*D3*J3)”
Same behavior as (C): it doesn’t suggest a change and
displays Err:510. Same result if I manually correct it to (A)

bantoniof · July 7, 2025, 8:06pm

I think your problem are the typographics quotes.
In basic, If you want to write double quotes as text you must duplicate them but never use typographics, only plain quotes.

you can test how to write your formula whit the simple print instruction

Sub test
print  "=IF(G3="""",-J3D3,-100J3*D3)"
end sub

LewG2 · July 7, 2025, 10:22pm

Thanks. It enters the correct syntax
=IF(G3="",=-D3J3,=-100D3*J3)

but same error Err:510 as other tests.

Note also the OP, manually typing the correct formula into the destination cell after the macro does it and fails – still no change. Strange

Villeroy · July 8, 2025, 2:26pm

If you really feel the need to put a formula into a cell by means of macro code (I don’t understand why), you should manually enter the formula into a cell and then inspect that cell in order to see the working formula from the API’s view point.

koyotak · July 7, 2025, 8:09pm

that is not a formula cause operators are missing, write =IF(G3="",-J3*D3,-100*J3*D3).

I cannot see any error in this formula.
Better using (ISBLANK(G3)).

LewG2 · July 7, 2025, 9:56pm

Fixed the operator problem.

Same problem. Err:510

I’m on a macbook, macos 15.5. It shouldn’t make a difference, but can it?

CRDF · July 7, 2025, 10:59pm

Here Ubuntu package version: 4:24.2.7-0ubuntu0.24.04.4
code

Sub Formula
	Dim oSheet As Object
	Dim aux As String
	aux = "=IF(G3="""";-J3*D3;-100*J3*D3)"
	print(aux)
	oSheet = ThisComponent.getCurrentController().getActiveSheet()
	' set formula at A1:
    oSheet.getCellByPosition(0, 0).setFormula(aux)
End Sub

works.
(?)

LewG2 · July 8, 2025, 1:36pm

Thanks for taking time to check. I’m on macos 15.5

CRDF · July 8, 2025, 2:04pm

Did you try the semicolons?
“=IF(G3=”""";-J3D3;-100J3*D3)"

Wanderer · July 8, 2025, 2:16pm

The typpgraphic quotes and the missing operators are a result of this discourse-site. @CRDF actually wrote

"=if(G3="",=D3*J3,=-100*D3*J3)"

Hint: always use backticks to enclose quotes.
The asterix is used for formatting.

LewG2 · July 8, 2025, 2:31pm

@CRDF – yes tried ; – no difference (and no compile errors)

@Wanderer – didn’t catch the misrepresentation of * as formatting code in OP. Changing now.

CRDF · July 8, 2025, 2:37pm

Yes, understood.
To me here if I put colons in the string it gives me Err:508
FormulaVirgulas

Villeroy · July 8, 2025, 2:49pm

Do it the other way round. Enter the formula manually and then inspect the cell programmatically.

LewG2 · July 8, 2025, 2:57pm

Sorry don’t understand. How to inspect programmatically?

Villeroy · July 8, 2025, 3:08pm

Select the cell with the correct formula and run
print ThisComponent.CurrentSelection.getFormula()

Use the object inspector in the Basic IDE.

Use an improved object inspector such as XRay or MRI.

Do you think, we know all that shit by heart? We simply look it up! You can ask the program anything, and it will give a correct answer in most cases.

bantoniof · July 8, 2025, 4:22pm

In my Windows 10 (Semicolon as formula separator)

The insertion of the formula works as expected and
there is no error 508; the formula works as espected:

if there is something in G3 (number or text), the result in A1 is -2000
if G3 is empty, the result in A1 is -20

The formula separator may vary in function of the locales or configuration of the formulas

It can be colon ( , )
or semicolon ( ; )

L.O. v 25.2.4.3

Villeroy · July 8, 2025, 4:39pm

No. It is always a semicolon, regardless of what you see in the UI. get/setFormula uses semicolon with English function names and references in Sheet.A1:B9 syntax.

bantoniof · July 8, 2025, 4:46pm

You can modify this. I do not remember but I’ve seen the colon as separator in some Calc Documents.

mikekaganski · July 8, 2025, 5:06pm

You can modify what? yes you can modify what is shown / used for manual input. But here we discuss what to use in a programmatic call to setFormula. And there, you always use a special syntax, that does not depend on your locale, program settings and OS. If it was different, than your macro that you wrote today would break for you tomorrow, when you changed your settings / OS / sent it to your friend.

And for cases when you need to pass a localized formula string in the programmatic way (say, you show an input box to your user, and ask them “enter your formula here” - indeed, not expecting your user to know such details as “special stable syntax”, take that input and pass to the cell), then you use FormulaLocal property.

LewG2 · July 8, 2025, 6:49pm

Not a programmer. How do you run that command? Select as in put the cursor there, or double click to “select” the formula it contains? Then what?