Excel VBA: how to check calculated column?

Question

In any case, check VBA if the specific table column (ListObject) in Excel is a computed column (as in http://office.microsoft.com/en-us/excel-help/use-calculated-columns-in-an- excel-table-HA010342380.aspx )?

Note that the calculated columns will not only have the same formula R1C1 for each row, but will also automatically expand when new rows are added (it will even be re-filled if you delete the entire range of the data body and then recreate several new rows) . Therefore, checking columns with consistent formulas is not the same as checking the calculated formula.

It is even possible to calculate a column, but so that one of the lines is overwritten by some other formula or value, and the automatic expansion functions are saved.

So, I'm pretty sure that this should be some kind of column property, I'm just not sure where I can access it through VBA. If it is not displayed through the VBA object model, is there some kind of desktop to get this information?

Thanks in advance, Carlos

EDIT: I searched the Excel Office Open XML files a bit, and it turned out that I was looking for the <calculatedColumnFormula> element in the <tableColumn> file definition of xl \ tables \ table * .xml. Is there any way to get to this through VBA?

EDIT2: Here is an example file with test cases that I could come up with. VBA should signal that columns 1, 2, and 3 are calculated columns, while columns 4 and 5 are not.

Answer 1

This gives relevant answers for your example.

Unfortunately, it has several potentially fatal flaws, depending on the situation. First, it calls recalc, so the random numbers generated using the formulas =RAND() in your example change.

The second drawback is that it modifies the worksheet to get the answers (it removes the modification that it does, but still it is modified). I can come up with a few only partially useful workarounds: (a) perform this operation as little as possible as necessary and cache results for all columns and (b) copy the table into a new workbook and run the routine (and delete the new workbook), At that time as the latter avoided the lack of modification, it still causes a repeat for the original book (and otherwise has its drawbacks). To add to this, copying a table to a new workbook will lose the table / ListObject if you do not copy the entire range (not just the headers); then it also apparently contributes to the 4th column (non-calc-consistent formulas) being calculated. Unfortunately, this promotion also occurs when copying the entire sheet.

Well, FWIW:

 Sub TestTable() Dim ans As String Let ans = "" Dim li As ListObject Set li = ActiveSheet.ListObjects(1) Dim rowCountBefore As Long Let rowCountBefore = li.ListRows.Count Dim lr As ListRow Set lr = Nothing On Error Resume Next Set lr = li.ListRows.Add(AlwaysInsert:=True) On Error GoTo 0 Dim rowCountAfter As Long Let rowCountAfter = li.ListRows.Count If Not (lr Is Nothing) And rowCountAfter = rowCountBefore + 1 Then Dim c As Long For c = 1 To li.DataBodyRange.Columns.Count Dim b As Boolean Let b = lr.Range.Cells(1, c).HasFormula ans = ans & "col " & c & ": " & b & "; " Next li.ListRows(rowCountAfter).Delete End If MsgBox ans End Sub 

Answer 2

Have not considered this before, but it looks like a ListObject column range property as follows:

 Dim wks As Worksheet Set wks = ActiveSheet Dim li As ListObject Set li = wks.ListObjects(1) Dim col As ListColumn Set col = li.ListColumns(2) ' assuming column 2 of the table has a calculated formula Dim r As Range Set r = col.DataBodyRange Let b = Not IsNull(r.FormulaArray) if b then Let b = Len(r.FormulaArray) > 0 ' case where r.FormulaArray = "", suspect it not a calculated column End If MsgBox b 

if IsNull (r.FormulaArray), then it does not have a calculated column, otherwise it does it.

Hth

---

Well, playing around with this a bit, and I see that the range object obtained using the above is different from the range object for any given cell, so if you have a given cell, I think you will need to get the corresponding ListColumn range through. DataBodyRange.

(For example, if you paste Set r = r.Cells(1,1) into the above, then the IsNull(r.FormulaArray) test IsNull(r.FormulaArray) no longer works to check the calculated column, but instead simply says if the range has a formula, but it can would be to calculate or not.)

Also, while r.FormulaArray is represented as a string when a column is being computed, if it is not (a computed column), then .FormulaArray gives null, which is not a valid string value (which makes it difficult to use this to capture the value you should use, not logical); I found that IsNull (r.FormulaArray) is working fine.

---

If I add a column to the right of an already calculated column, then r.FormulaArray = "" for this newly added column. If you put a value in one of its cells, the array of formulas immediately returns to the more expected NULL. So, I added a test for what I consider to be false.