CODEWITHSUNDEEP
vbaexcel
Rapid
Rapid

Reputation: 1502

Split a cell's formulaic elements into seperate cells

So I have a cell with the formula =A+B, A and B are external values for which the external spreadsheet is no longer open. I was wondering if excel remembers the formula elements and if it was possible to get those element values into other cells. i.e.

Row   Formula           Value             
1     =A+B              45
2     =ELEMENT(A1, 1)   10  
3     =ELEMENT(A1, 2)   35

I can't imagine it being that simple though? I could seperate out the formula in vba using the + as a pivot, but this is not ideal as it would require the external spreadsheet to be reopened. If the external spreadsheet has to be reopened then I needn't bother trying to seperate the formula in the first place. I hope this makes sense and has an answer.

Upvotes: 1

Views: 164

Answers (1)

David Zemens
David Zemens

Reputation: 53623

Your formula is already accessing those values in order to sum them in the open workbook. You do NOT need to re-open the workbook in order to evaluate the reference values, nor to parse the formula in the active workbook.

Assuming your formula references a closed workbook, with a simple sum function like:

='C:\Users\david_zemens\Desktop\[test.xlsx]Sheet1'!$A$1 + ='C:\Users\david_zemens\Desktop\[test.xlsx]Sheet1'!$B$1

You can parse the formula either in VBA or using string functions on the worksheet. As you note, for this example, parsing by the + operator will suffice. Since I think you understand how to do this, my example does not demonstrate how to parse the formula.

As long as you know or can obtain the references from the formula, you should be able to access those cells' values via formula, or programmatically in VBA using ExecuteExcel4Macro.

Sub GetValsFromClosedWorkbook()

    Dim fileName As String
    Dim filePath As String
    Dim sheetName As String
    Dim cellref As String
    Dim myFormula As String

    'This example might be one of the two arguments in your "A+B" formula:
    '   'C:\Users\david_zemens\Desktop\[test.xlsx]Sheet1'!A1

    '## Assume you can properly parse the formula to arrive at these:
    fileName = "test.xlsx"
    filePath = "C:\Users\david_zemens\Desktop\"
    sheetName = "Sheet1"
    cellref = "A1"

    'Concatenate in to R1C1 notation
    myFormula = "='" & filePath & "[" & fileName & "]" & sheetName & "'!" & _
      Range(cellref).Address(, , xlR1C1)

    '## First, demonstrate that we can evaluate external references:
    With Range("B1")
        .Value = myFormula
        MsgBox .Value
        .Clear
    End With

    '## Evaluate the formula using ExecuteExcel4Macro:
    MsgBox ExecuteExcel4Macro(Replace(myFormula, "=", vbNullString))

End Sub

Additional info

http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/

Update

Based on OP's question of how this works (comments, below), I am not certain of how it works behind the scenes but here is my guess/explanation:

Firstly, keep in mind that a cell may have various properties like .Text, .Value, .Value2, .Formula etc. which in one way or another represent its contents.

The values aren't in the active file until the formula is evaluated, at which point Excel queries the external file and returns the current Value at that reference. Now the active file contains that Value as well as a Formula reference the external file.

As you continue working the active file preserves the Value and the reference, but may only update the value when prompted. For example, when you re-open the linked workbook, you will be prompted whether to "update external links".

  • if you say no, it will use the previously stored values (still preserves the Formula, it just doesn't evaluate it.
  • if you say yes, it will re-evaluate the formula (in case the value in the external file has changed) and return the new .Value

So, that's what I suspect is happening. If you're asking "How does Excel access the data inside an un-opened workbook" I don't really know how I just know that it does.

Upvotes: 1

Related Questions