How to get cell's formula without reference to other cell

Add-in Express™ Support Service
That's what is more important than anything else

How to get cell's formula without reference to other cell
 
KB LIM




Posts: 10
 Joined: 2018-01-01
Hi,

I'm trying to get all the formulas in the Excel'sheet.
However the returned formula in the cell does not provide the actual value that are referring to other cell or range.

I'm using the code below to retrieve all the formula that are using my UDF start with RevenueToday. 

For iLoopWB = 1 To AddinModule.CurrentInstance.ExcelApp.Workbooks.Count
            For iLoopWS = 1 To AddinModule.CurrentInstance.ExcelApp.Workbooks.Item(iLoopWB).Worksheets.Count
                Dim rng1
                rng1 = AddinModule.CurrentInstance.ExcelApp.Workbooks.Item(iLoopWB).Worksheets(iLoopWS).UsedRange
                If rng1.Cells.Count > 1 Then
                    Dim X = AddinModule.CurrentInstance.ExcelApp.Workbooks.Item(iLoopWB).Worksheets(iLoopWS).UsedRange.Formula
                    Dim lRow As Long
                    For lRow = 1 To UBound(X, 1)
                        For lCol = 1 To UBound(X, 2)
                            If (X(lRow, lCol).ToString.Contains("RevenueToday")) Then
                               'write to log file                         
                               Log(X(lRow, lCol))
                            End If

                        Next lCol
                    Next
                End If

            Next
        Next



The log file content:

RevenueToday($B$1,$B$1,H$2,$A3, "Tax")
RevenueToday("XYZ","XYZ", 2020,1,,,B3:B8)
RevenueToday(code,code,year,1,,,account)

-----------------------------------------

Refer to the log file content,

1.RevenueToday($B$1,$B$1,H$2,$A3, "Tax")

Cell $B$1 = "XYZ"
Cell H$2 = 2020
Cell $A3 = 1
I would like to get the formula = RevenueToday("XYZ", "XYZ", 2020, 1, "Tax")


2.RevenueToday("XYZ","XYZ", 2020,1,,,B3:B8)

Cell B3 = "A", B4="B", B5="C", B6="D", B7="E", B8="F"
I would like to get the formula = RevenueToday("XYZ", "XYZ", 2020, 1,,,"A,B,C,D,E,F".


3.RevenueToday(code,code,year,1,,,account)

Cell reference "code" = "XYZ", "year" = 2020, "account" = "Tax"
I would like to get the formula = RevenueToday("XYZ", "XYZ", 2020, 1,,,"Tax")

My question is how do I get all the formula in the Excel sheet without the cell id, cell range and cell reference inside the formula.

Thanks!
Posted 26 Sep, 2020 11:09:20 Top
Andrei Smolin


Add-in Express team


Posts: 18830
 Joined: 2006-05-11
Hello,

There's no way that the Excel object model provides. A formula is a "template". To get the actual data a given formula instance provides, you need to visit all the cells the formula instance refers to. A way to simplify this would be to construct the exact formula right in the RevenueToday() call. You can store the constructed formula in a Dictionary where the key is the cell address and the value is the formula.


Andrei Smolin
Add-in Express Team Leader
Posted 28 Sep, 2020 04:57:05 Top
KB LIM




Posts: 10
 Joined: 2018-01-01
Thank you for your reply.
Posted 28 Sep, 2020 21:41:09 Top
Andrei Smolin


Add-in Express team


Posts: 18830
 Joined: 2006-05-11
You are welcome.


Andrei Smolin
Add-in Express Team Leader
Posted 29 Sep, 2020 03:43:30 Top