Attachment 175316 Details for Bug 144766 – Basic Macro that implements the notation described in the bug.

Basic Macro that implements the notation described in the bug.

file_144766.txt (text/plain), 3.21 KB, created by Mchael on 2021-09-28 12:41:32 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Mchael

Created: 2021-09-28 12:41:32 UTC

Size: 3.21 KB

patch

obsolete

>REM  *****  BASIC  *****
>Option Explicit
>
>Function startsWithCellAddress(byVal part as String, byRef rowIndex as Integer) as Boolean
>	Dim ch as Integer
>	Dim partLength as Integer
>	
>	REM expecting at least 2 characters - letter for column, number for row.
>	partLength = Len(part)
>	if partLength < 2 Then
>		startsWithCellAddress = False
>		Exit function
>	End IF
>	
>	REM expecting letters as column index followed by numbers as row index.
>	ch = ASC(UCase(Mid(part, 1, 1)))
>	
>	REM Mid treats string as 1-based index, rowIndex will be used with that function
>	rowIndex = 1
>	REM 'A'=65 'Z'=90
>	Do while ch >= 65 And ch <= 90 and rowIndex < partLength
>		rowIndex = rowIndex + 1
>		ch = ASC(UCase(Mid(part, rowIndex, 1)))
>	Loop
>	
>	REM Checking that column index (letters) was followed by number(s), which are row index.
>	REM what follows the numbers is not checked.
>	REM Not making assumptions regarding expected formula syntax.
>	REM '0'=48 '9'=57, rowIndex > 1, iif there was column cpecified.
>	startsWithCellAddress = ch >= 48 And ch <= 57 And rowIndex > 1
>End Function
>
>Function redistributeDollars(formula as String) as String
>	Dim resultAccumulator() as String
>	Dim formulaParts
>	Dim index as Integer
>	Dim formulaPartsLength as Integer
>	
>	formulaParts = Split(formula, "$$")
>	
>	
>	REM check the string "$$" was used.
>	REM UBound is highest index, so 0 means there is 1 element; 1 means there are 2.
>	formulaPartsLength = UBound(formulaParts)
>	if formulaPartsLength < 1 Then
>		redistributeDollars = formula
>		Exit Function
>	End If
>	
>	REM Created array is by 1 longer then given parameter.
>	ReDim resultAccumulator(4*formulaPartsLength)
>	
>	REM Preparing array for join call, first part is everything up to "$$".
>	REM Following parts are formed as ["$", Column string, "$", Row string and the rest of the formula  up to next "$$"]
>	resultAccumulator(0) = formulaParts(0)
>	for index = 1 To formulaPartsLength step 1
>		Dim rawIndex as Integer
>		Dim digitIndex as Integer
>		Dim partMatchesCellAddress as Boolean
>		
>		digitIndex = 0
>		
>		REM if there is unexpected syntax with $$ in place, leave original text.
>		partMatchesCellAddress = startsWithCellAddress(formulaParts(index), digitIndex)
>		if not partMatchesCellAddress Then
>			redistributeDollars = formula
>			Exit Function
>		end if
>		
>		rawIndex = 1 + (index - 1)*4
>		resultAccumulator(rawIndex) = "$"
>		REM Length is 1 less then 1-based index
>		resultAccumulator(rawIndex + 1) = Left(formulaParts(index), digitIndex-1)
>		resultAccumulator(rawIndex + 2) = "$"
>		resultAccumulator(rawIndex + 3) = Mid(formulaParts(index), digitIndex)
>	Next index
>	
>	redistributeDollars = Join(resultAccumulator, "")
>End Function
>
>REM In the calc document in Sheet menu, Select "Sheet events", choose "content change", point to this macro.
>sub cell_onChange(selectedRange as Variant)
>	Dim formula as String
>	REM Do not touch ranges
>	If not selectedRange.supportsService("com.sun.star.sheet.SheetCell") Then
>		Exit Sub
>	End If
>
>	formula = selectedRange.FormulaLocal
>	
>	REM Check if it's actually a formula. No point in changing free text
>	if (Left(formula, 1) <> "=") then
>		Exit Sub
>	End if
>	
>	selectedRange.FormulaLocal = redistributeDollars(formula)
>End sub
>

Actions: View

Attachments on bug 144766: 175316