Access each cell's specific properties in a RangeAreas object in OfficeJS-CodePudding

I am converting my VBA Excel add-in to OfficeJS, and one function takes each cell in a range and: if the cell is hardcoded, turns the font blue if the cell has a formulas, turns the font black if the cell links to another sheet, turns the font green if the cell links to another file, turns the font red if the cell links to an external datasource, turns the font dark red.

In VBA, I can iterate over the cells in my selection and identify the cell's formula/value, then in the same iteration change the font color accordingly (VBA code below). As far as I understand, in OfficeJS I can load the cell's value/formula into an an array, or an array structured as RangeArea[Range[row[column]]], but I cannot load both the formula and the font into an array.

How can I access the formula in a cell, then change the cell font accordingly in OfficeJS?

Sub AutoColorCells()
  Dim cell As Range
  For Each cell In Selection
    If cell.HasFormula Then
      Dim letters As String
      Dim formula As String
      Dim cellRef As Boolean
      Dim i As Integer
      cellRef = False
      letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
      formula = cell.formula
      For i = 1 To Len(formula)
        If InStr(letters, Mid(formula, i, 1)) > 0 Then
          cellRef = True
        End If
      Next i
      If InStr(formula, "!") Then
        If InStr(formula, "[") Then
          cell.Font.Color = RGB(192, 0, 0)  ' link to file
        Else
          cell.Font.Color = RGB(0, 128, 0) ' link to sheet
        End If
      ElseIf cellRef = True Then
        cell.Font.Color = RGB(0, 0, 0)  ' cell reference formula
      Else
        cell.Font.Color = RGB(0, 0, 255)  ' math formula
      End If
    Else
      cell.Font.Color = RGB(0, 0, 255)  ' Something hardcoded- no "="
    End If
  Next cell
End Sub

I have tried using the .map() method, but that only works on the cell formula, not other properties. I have also tried loading arrays into two variables, one for formula and one empty, then using a for loop to get the RangeAreas.flormulas[range][row][column] address and create a parallel array with the appropriate formulas. The problem with this method is that I cannot change cell fonts with an array- only the entire RangeAreas object.

It might be possible to iterate over each cell in the RangeAreas object, call context.sync() within the iteration, and change the cells like that, but it would be slow. Is there even a way to iterate over individual cells in the RangeAreas object?

CodePudding user response：

This was actually quite an interesting question!