calc: Basic syntax error. Expected = again

[MikeMcClain46]

LibreOffice Version: 6.1.5.2

I know I’ve raised this question before but never got it resolved.
The following code in a new calc sheet gives no errors and runs fine with the first row filled
with 1 to 5 and 3 in cell 7 or G1. It also needs styles Red and Grn defined.
This identical code in my stock tracking spread sheet gives the error at the point marked
and stops everything else from working.
Any suggestions for tracking down this type of error in 3600+ lines of code is more
than welcome.
Thanks,
Mike

public const DowRowL = 0
public const LastColL =8
public const ColorBarOffsetL = 3
public const DOW = 3
Sub Main
  colorBarSetFmtCondLO
End Sub
Sub  colorBarSetFmtCondLO()
  Dim srcOffset As Integer, whichDay As Integer
  Dim cbCol as Long, oCell
  whichDay = DoW
  cbCol = LastColL - ColorBarOffsetL
  srcOffset = 5
  oCell = ThisComponent.CurrentController.ActiveSheet.GetCellbyPosition( _
						cbCol, DowRowL) 
  offsetTo20DayAvg = 1	'	offset from cbCell to 20DayAvg cell
  If whichDay = offsetTo20DayAvg Then srcOffset = srcOffset + 1
  colorBarSetCondFormsLO(oCell, srcOffset, offsetTo20DayAvg)'<-- Expected =
End Sub
Sub colorBarSetCondFormsLO(cbCell, srcOffset as Integer, offset20DayAvg as integer)
  Dim srcAddr As String, avg20dayAddr As String
  srcAddr = "RC[-" & srcOffset & "]"
  avg20dayAddr = "RC[" & offset20DayAvg & "]"
  form = "=" & srcAddr & "+STYLE(IF(CURRENT() <" & avg20dayAddr & _
  	"; ""Red""; ""Grn"" ))"
  cbCell.FormulaLocal = form
  ThisComponent.CurrentController.Select(cbCell)
  if msgbox(form, 1) = 2 then stop
End Sub

[Edit - Opaque] Removed “pre” tagging from non-code text

[Lupp]

@anon73440385: Sorry. Had the same idea at about the same time.

[anon73440385]

@Lupp: No problem …

[Lupp]

This all seems to be related to Excel usage.
It’s uncommon to use RC addressing in Calc, e.g.

The macro module you pasted your macro in for usage with your “stock trading sheet” seems to have the option VBAsupport 1 enabled. That’s what triggers the error at compile time. Either there is some syntactical thing I don’t know concerning Excel VBA, or the respective code trying to get compatibility in LibreOffice Basic has a bug.
Using a dummy variable, say q, and assigning q = colorBarSetCondFormsLO(oCell, srcOffset, offsetTo20DayAvg) makes the compile-time-error vanish also under VBAsupport 1.

I have no idea how the code actually should create a conditional format of the bar-toy type. Using the STYLE() function in the context looks strange to me.

However, I didn’t actually study the code thoroughly.

[MikeMcClain46]

Regarding RC addressing: I have several columns of calculations whose position moves daily as I add another column for today’s data. Having learned Cartesian coordinates early on it seems a natural fit to address those columns based on an offset from the last column of data whitch changes as another column id added.

How might I achieve the same in A1B2 addressing?

Regarding the Style usage: I found it here:
https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_STYLE_function
Due to the fact that LO/OO bundles all the text attributes into styles I know of no other way to accomplish the task.

If you do I’d be happy to hear it.

Thanks,
Mike

[Lupp]

I don’t deprecate RC addressing at all. In fact I would prefer it to be the default - or a permanent alternative. It’s just uncommon. To use it under VBAsupport via the property formulaR1C1 may give an advantage. I lack related experience.
Inserting formulas by user code should anyway be a rare exception. To do it with so-called relative addresses will be complicated. I never do.
Everything of the kind shouldn’t need macros. But I neither know the background of your specific design, nor do I want to.
Generally an inflationary usage of user code seems to be encouraged by the Excel “community”. I see it as one of the lock-in helpers of MS.
To use the STYLE() function is ok, of course. I simply wanted to state that youcannot get a CF functionality this way.
I don’t feel sure that you already undertstood the style concept basically and in its relation to CF.

[Lupp]

Concerning CF again there is a discrepancy due to feature requests and/or “compatibility” requirements.
By its origin it’s reasonably designed to overlay attributes taken from a named cell style to concerned cells for the view only.
The younger toys of the All Cells family (“Data bar”, “Color scale”…) don’t fit into this well considered scheme. In fact they aren’t real CF but, well, toys then, and come along a bit like “poor man’s charts”.
I never use them.
I don’t know a way to establish pseudo-CF of the kind by user code. I never tried, and am not interested.