Bugzilla – Attachment 175316 Details for
Bug 144766
Constant reference to cell address using "$$" prefix notation.
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
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:
Basic Macro that implements the notation described in the bug.
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 >
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Raw
Actions:
View
Attachments on
bug 144766
: 175316