È questo il RegEx per far corrispondere qualsiasi riferimento di cella in una formula di Excel?

Ho cercato di creare un modello di espressioni regolari che corrisponda a qualsiasi riferimento in qualsiasi formula di Excel, inclusi riferimenti assoluti, relativi e esterni. Devo restituire l’intero riferimento, incluso il foglio di lavoro e il nome della cartella di lavoro.

Non sono stato in grado di trovare una documentazione esaustiva sulla notazione A1 di Excel, ma con molti test ho determinato quanto segue:

  • Le formule sono precedute da un segno di uguale “=”
  • Le stringhe all’interno delle formule sono racchiuse tra virgolette doppie e devono essere rimosse prima di cercare riferimenti reali, altrimenti =A1&"A1" si interromperanno le espressioni regolari
  • I nomi dei fogli di lavoro possono avere una lunghezza massima di 31 caratteri, escluso \ /? * []:
  • I nomi dei fogli di lavoro nei riferimenti esterni devono essere completati con bang =Sheet1!A1
  • I nomi delle cartelle di lavoro nei riferimenti esterni devono essere racchiusi tra parentesi quadre =[Book1.xlsx]Sheet1!A1
  • Percorsi di cartella di lavoro, che Excel aggiunge se un riferimento è a un intervallo in una cartella di lavoro chiusa, sono sempre racchiusi tra virgolette singole e alla sinistra delle parentesi per il nome della cartella di lavoro 'C:\[Book1.xlsx]Sheet1'!A1
  • Alcuni caratteri (spazio non di separazione, ad esempio) fanno sì che Excel racchiuda la cartella di lavoro e il nome del foglio di lavoro in un riferimento esterno tra virgolette singole, ma non conosco in modo specifico quali caratteri ='[Book 1.xlsx]Sheet 1'!A1
  • Anche se la notazione R1C1 è abilitata, Range.Formula restituisce ancora riferimenti in notazione A1. Range.FormulaR1C1 restituisce riferimenti nella notazione R1C1.
  • Lo stile di riferimento 3D consente una gamma di nomi di fogli su una cartella di lavoro =SUM([Book5]Sheet1:Sheet3!A1)
  • Gli intervalli nominati possono essere specificati nelle formule:
    • Il primo carattere di un nome deve essere una lettera, un carattere di sottolineatura (_) o una barra rovesciata (\). I caratteri rimanenti nel nome possono essere lettere, numeri, punti e caratteri di sottolineatura.
    • Non è ansible utilizzare i caratteri maiuscoli e minuscoli “C”, “c”, “R” o “r” come nome definito, poiché sono tutti utilizzati come una scorciatoia per selezionare una riga o una colonna per la cella attualmente selezionata quando si inseriscili in una casella di testo Nome o Vai a.
    • I nomi non possono essere uguali a un riferimento di cella, come Z $ 100 o R1C1.
    • Gli spazi non sono consentiti come parte di un nome.
    • Un nome può avere una lunghezza massima di 255 caratteri.
    • I nomi possono contenere lettere maiuscole e minuscole. Excel non distingue tra caratteri maiuscoli e minuscoli nei nomi.

Ecco cosa ho trovato avvolto in una procedura VBA per i test. Ho aggiornato il codice per gestire anche i nomi:

 Sub ReturnFormulaReferences() Dim objRegExp As New VBScript_RegExp_55.RegExp Dim objCell As Range Dim objStringMatches As Object Dim objReferenceMatches As Object Dim objMatch As Object Dim intReferenceCount As Integer Dim intIndex As Integer Dim booIsReference As Boolean Dim objName As Name Dim booNameFound As Boolean With objRegExp .MultiLine = True .Global = True .IgnoreCase = True End With For Each objCell In Selection.Cells If Left(objCell.Formula, 1) = "=" Then objRegExp.Pattern = "\"".*\""" Set objStringMatches = objRegExp.Execute(objCell.Formula) objRegExp.Pattern = "(\'.*(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\'\!" _ & "|(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\!)?" _ & "(\$?[az]{1,3}\$?[0-9]{1,7}(\:\$?[az]{1,3}\$?[0-9]{1,7})?" _ & "|\$[az]{1,3}\:\$[az]{1,3}" _ & "|[az]{1,3}\:[az]{1,3}" _ & "|\$[0-9]{1,7}\:\$[0-9]{1,7}" _ & "|[0-9]{1,7}\:[0-9]{1,7}" _ & "|[a-z_\\][a-z0-9_\.]{0,254})" Set objReferenceMatches = objRegExp.Execute(objCell.Formula) intReferenceCount = 0 For Each objMatch In objReferenceMatches intReferenceCount = intReferenceCount + 1 Next Debug.Print objCell.Formula For intIndex = intReferenceCount - 1 To 0 Step -1 booIsReference = True For Each objMatch In objStringMatches If objReferenceMatches(intIndex).FirstIndex > objMatch.FirstIndex _ And objReferenceMatches(intIndex).FirstIndex < objMatch.FirstIndex + objMatch.Length Then booIsReference = False Exit For End If Next If booIsReference Then objRegExp.Pattern = "(\'.*(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\'\!" _ & "|(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\!)?" _ & "(\$?[az]{1,3}\$?[0-9]{1,7}(\:\$?[az]{1,3}\$?[0-9]{1,7})?" _ & "|\$[az]{1,3}\:\$[az]{1,3}" _ & "|[az]{1,3}\:[az]{1,3}" _ & "|\$[0-9]{1,7}\:\$[0-9]{1,7}" _ & "|[0-9]{1,7}\:[0-9]{1,7})" If Not objRegExp.Test(objReferenceMatches(intIndex).Value) Then 'reference is not A1 objRegExp.Pattern = "^(\'.*(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\'\!" _ & "|(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\!)" _ & "[a-z_\\][a-z0-9_\.]{0,254}$" If Not objRegExp.Test(objReferenceMatches(intIndex).Value) Then 'name is not external booNameFound = False For Each objName In objCell.Worksheet.Parent.Names If objReferenceMatches(intIndex).Value = objName.Name Then booNameFound = True Exit For End If Next If Not booNameFound Then objRegExp.Pattern = "^(\'.*(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\'\!" _ & "|(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\!)" For Each objName In objCell.Worksheet.Names If objReferenceMatches(intIndex).Value = objRegExp.Replace(objName.Name, "") Then booNameFound = True Exit For End If Next End If booIsReference = booNameFound End If End If End If If booIsReference Then Debug.Print " " & objReferenceMatches(intIndex).Value _ & " (" & objReferenceMatches(intIndex).FirstIndex & ", " _ & objReferenceMatches(intIndex).Length & ")" End If Next intIndex Debug.Print End If Next Set objRegExp = Nothing Set objStringMatches = Nothing Set objReferenceMatches = Nothing Set objMatch = Nothing Set objCell = Nothing Set objName = Nothing End Sub 

Qualcuno può rompere o migliorare questo? Senza una documentazione esaustiva sulla syntax della formula di Excel è difficile sapere se questo è corretto.

Grazie!

jtolle mi ha guidato nella giusta direzione. Per quanto posso dire, questo è quello che stavo cercando di fare. Ho provato e sembra funzionare.

 stringOriginFormula = rangeOrigin.Formula rangeOrigin.Cut rangeDestination rangeOrigin.Formula = stringOriginFormula 

Grazie jtolle!

Grazie Ben (Sono nuovo per postare qui, anche se Stackoverflow ha attirato la mia attenzione per anni per materiale tecnico di alta qualità, quindi non sono sicuro di aver letto correttamente questa pagina per l’autore J)

Ho provato le soluzioni pubblicate (test, test aggiornati e quello che utilizza range.precendents (che come indicato correttamente, non copre i riferimenti ad altri fogli o altre cartelle di lavoro) e ho trovato un difetto minore: il nome del foglio esterno è racchiuso tra ‘virgolette singole’ solo se è un numero, se contiene spazio (e possibilmente altri caratteri come Ben (?) elencati nel post originale. con una semplice aggiunta al regEx (apertura [) questo può essere corretto (aggiunto ” [“, vedi il codice sotto) Inoltre, per il mio scopo ho convertito il sub in una funzione che restituirà un elenco separato da virgole con i duplicati rimossi (nota, questo rimuove solo la notazione di riferimento identica, non le celle che sono incluse in più gamme):

 Public Function CellReflist(Optional r As Range) ' single cell Dim result As Object: Dim testExpression As String: Dim objRegEx As Object If r Is Nothing Then Set r = ActiveCell ' Cells(1, 2) ' INPUT THE CELL HERE , eg RANGE("A1") Set objRegEx = CreateObject("VBScript.RegExp") objRegEx.IgnoreCase = True: objRegEx.Global = True: objRegEx.Pattern = """.*?""" ' remove expressions testExpression = CStr(r.Formula) testExpression = objRegEx.Replace(testExpression, "") 'objRegEx.Pattern = "(([AZ])+(\d)+)" 'grab the address objRegEx.Pattern = "(['\[].*?['!])?([[A-Z0-9_]+[!])?(\$?[AZ]+\$?(\d)+(:\$?[AZ]+\$?(\d)+)?|\$?[AZ]+:\$?[AZ]+|(\$?[AZ]+\$?(\d)+))" If objRegEx.Test(testExpression) Then Set result = objRegEx.Execute(testExpression) If result.Count > 0 Then CellReflist = result(0).Value If result.Count > 1 Then For i = 1 To result.Count - 1 'Each Match In result dbl = False ' poistetaan tuplaesiintymiset For j = 0 To i - 1 If result(i).Value = result(j).Value Then dbl = True Next j If Not dbl Then CellReflist = CellReflist & "," & result(i).Value 'Match.Value Next i 'Match End If End If 

Fine Funzione