Calc, Single quote(s) in basic

I am trying to write a macro (in basic). I am having problem with single quote(s) in a string.
This creates the string that looks good to me.

MsgBox "'" & SrcSht &"'.$G$" & FirstROW.value & ":'" & SrcSht & "':$G$" & NextROW.value

When I use this to create a formula it fails because each single quote (looks like) is treated as a remark statement. Causing an incorrect formula.

WkAveFstr = ' & SrcSht &'.$G$ & FirstROW.value & :' & SrcSht & ':$G$ & NextROW.value|

WkAve.Formula = =Average(WkNumAve)|

How do I deal with this?
Thank You

Please upload a small sample file here together with the embedded macro code.

  • Is the object FirstROW a specific cell really? Because only a Cell object has .value property, but a row has not.

A reoccurring problem with unpaired single quotes in Basic code you can avoid by exclusively using the REM prefix for comments.
Another cause for apostrophes causing problems is the insane idea (not yours) to use a pair of them as escape helpers when sheet names without a clearly and resonably restricted syntax occur. Don’t join the caravan in this point, an use exclusively sheet names not needing escaping.

Just for knowledge…
The use of the Chr() Function could solve the problem?

WkAveFstr = Chr$(39) & SrcSht & Chr$(39) & ".$G$" & FirstROW.value & ":" & Chr$(39) & SrcSht & Chr$(39) & ":$G$" & NextROW.value

Seems you want to assign something to

  1. a Basic variable of (implicit or explicit) type String (WkAveFstr)
  2. a cell property of type String (WkAve.Formula)

In both cases the expression right of the = used as assignment operator must actually be a String or evaluate to a String result.

(Donno what you expect WkNumAve to stand for.)

In Basic as well as in Calc formulas literals must be quoted (with doublequotes).
If you need a doublequote to occur inside a literal it must be doubled. ("""" returns a single doublequote.)

I am posting weekly average data from one sheet to another to create a graph over time, by week number. To sue the values from another sheet the reference needs to contain the sheet name (SrcSht). Msgbox shows (what appears) to be a properly constructed range. Using this in the
WkAve.Formula shows the string greyed after the first single quote(’). I have tried using “’” but still get the string greyed out like the remaining is a remark

REM  *****  BASIC  *****

Sub Main
Dim Doc, Asheet, AllSheets, Dest, DestSheet, SourceSht as Object
Dim FirstDOW, LastDOW, FirstROW, LastROW as Object	'a range for First and last rows scanned
Dim DateCel, CalCel as Object
Dim oFA as Object
Dim SrcSht, DestSht, Mstr, WkAveFstr as String
Dim i, Srow, WNum, ThisWk as Integer

SrcSht = "Data"
DestSht = "Average Values"

Doc = ThisComponent 
AllSheets = Doc.Sheets 
' Find First and last days of the first week
Src = AllSheets.getbyName(SrcSht)
Dest = AllSheets.getbyName(DestSht)
FirstDOW = Dest.getCellRangeByName("Z1")
LastDOW = Dest.getCellRangeByName("Z2")
FirstROW = Dest.getCellRangeByName("Z3")
NextROW = Dest.getCellRangeByName("Z4")
DateCel = Src.getCellRangeByName("A2" )
CalCel = Src.getCellRangeByName("G2")
WkDate = Dest.getCellRangeByName("A2")
Wkn = Dest.getCellRangeByName("B2")
WkAve = Dest.getCellRangeByName("C2")

	ThisWk = WkNum(DateCel)
	FirstDOW.value = DateCel.value
	LastDOW.value = DateCel.value
Mstr = "Date      " &  "	Calories " & Chr(13)
If FirstROW.Value = 0 Then
	Srow = 1
	FirstROW.value = Srow
		Srow = NextROW.value
		'FirstROW.value = Srow
End If

For i = Srow to 100	'Row numbers. Start at row 2
	WNum = WkNum(DateCel)
	'Get values for week
	' Loop thru rows finding the last day of this week
	Do Until WkNum(DateCel) <> ThisWk
		Mstr =   Mstr & CDate(DateCel.value) & "		" & CalCel.value & " for week " & WNum & CHR(13)
		NextROW.value = i
		i = i + 1
		DateCel = Src.getCellRangeByName("A" & i)
		CalCel = Src.getCellRangeByName("G" & i)
	WkDate.value = LastDOW.value
	Wkn.value = WNum
	WkAveFstr = "'" & SrcSht &"'.$G$" & FirstROW.value & ":'" & SrcSht & "':$G$" & NextROW.value
	WkAve.Formula = "=Average(WkAveFstr)"
End Sub

Now I don’t know how to correlate the newly posted code to the lines from the original question.

You also missed to post this code as a clearly delimited block of Preformatted text. Now some parts are, others are not. And overall you find “typographical” quotes and/or apostrophes here and there. It’s a mess. (Obsolete since the Questioner edited his post above.)

Anyway, if trying to rectify that, I cant find a position actually explaining what you describe.
On the other hand the For-loop with control variable i isn’t closed.
(Still valid after thge mentioned editing. The Loop wit control variable i is still open.)

Also: The variable Mstr gets assignments, but seems to not deliver the result anywhere.

Please act on the advice by @Zizi64. We should get this clearer instead of longer.

Thanks FelipeAle
Using CHR(39) works!

1 Like

You are welcome!
I will describe it again here just for the record as a solution.