Test for empty Listobject header

Question

How to test if an Excel (Version >= 2010) ListObject header is empty?

If I select such an empty header, then

?selection.value -> Column1 (or Spalte1, depending on client language)
?isEmpty(selection) -> false

empty header

So as far as VBA is concerned, it behaves like a non-empty cell.

Non-solution: Writing to the cell and then check if its value changed.

You say "empty" but as far as I know, Excel just creates a default header name of "Column1", etc. This is true whether you try to create the table with an empty header or you delete the text from an existing one. So, it seems like you'd just test for a string beginning with "Column". Also, I'm not sure what you mean by "disable headers." Headers are an integral and required aspect of an Excel table. Perhaps if you expanded your question to describe the bigger problem it would help. — Doug Glancy, Jun 06 '13 at 17:49
Right, but I cannot test for ColumnXY in every possible language. I removed the "disable headers" part, it was confusing. The bigger picture is: If a user gives a header a name, then a new column is created in a backend-database. So we have to find out which column has a non-empty (thus user-defined) header. — Wolfgang Kuehn, Jun 06 '13 at 18:54
Perhaps you could use a Worksheet_Change event to track whenever a header cell is changed. — Doug Glancy, Jun 06 '13 at 19:39
@Doug Glancy Tried it: That event is fired twice if a user deletes an already empty header. The cell value is 'Column1' both times. Unexpected, but doesn't help. — Wolfgang Kuehn, Jun 06 '13 at 19:47

score 1 · Answer 1 · answered Jun 06 '13 at 18:59

1

As said by Doug Excel just creates a default header name of "Column1".

If you want to read column header you can use the below code.

Sub sample()

    Dim tbl As Object
    Set tbl = Sheets("sheet1").ListObjects("Table1")
   MsgBox Trim(tbl.ListRows(1).Range.Cells(0, 1))

End Sub

answered Jun 06 '13 at 18:59

Santosh

12,175
4
41
72

That default header name I want to distinguish from a user provided or empty value. Retrieving the header value is not the problem. – Wolfgang Kuehn Jun 06 '13 at 19:10
@amadeus Do you want to make comparison between two values. If yes you can use `StrComp` function. – Santosh Jun 06 '13 at 19:13
I retrieve the header value. It says Column1 or Spalte1 or Columna1 or whatever language is set. How do I decide that the user has deleted the header (or has not provided a header value)? – Wolfgang Kuehn Jun 06 '13 at 19:19
@amadeus if its Column1 then either user has not set the header or deleted the header. If user has not provided a header value there is default header column1 set. Instead you can try to protect the header. – Santosh Jun 06 '13 at 19:27
The user must be able to edit the header. That is the whole point. And Column1 only helps in English. – Wolfgang Kuehn Jun 06 '13 at 19:31
@amadeus: What is the purpose of checking whether user is using Excel provided column name or of his/her choice? – shahkalpesh Jun 06 '13 at 19:57
@shahkalpesh If user deletes header or whole column means that this column is also deleted from backend-datastore. Actually the header value is the backend column ID. – Wolfgang Kuehn Jun 06 '13 at 20:08
@amadeus: How are you creating list-object? Also, how can you delete the header in a table (considering that it is like a name of the column in a dbms table)? – shahkalpesh Jun 06 '13 at 21:04
@amadeus you can protect the sheet leaving the header as editable (Locked = False). Now you can only edit the header but cannot delete header or whole column. – Santosh Jun 07 '13 at 02:26
@shahkalpesh The ListObject is inserted into the worksheet by the user with the Ribbon Insert->Table command. The column is deleted with "SQL Drop Column" – Wolfgang Kuehn Jun 07 '13 at 07:27
@Santosh With "delete header" I mean that user can select the header cell and press delete. – Wolfgang Kuehn Jun 07 '13 at 07:29
1

@amadeus: Can you help me understand, how do you do "SQL Drop column"? – shahkalpesh Jun 07 '13 at 07:32

score 0 · Accepted Answer · answered Jun 29 '13 at 13:49

We settled with this solution. Somewhere on the workbook insert a ListObject (aka Table) with one column, leaving the header empty. Give this header the name emptyHeader.

With the function below you can now check if a given range is really empty, that is has empty cell value or a value auto-set by the ListObject for empty headers.

The solution is not perfect. If a user sets the header value of the second column to Column42 (in case of english), then the function will erroneously return true.

Function isRealyEmpty(ra As Range) As Boolean
    If IsEmpty(ra.Value) Then
        isRealyEmpty= True
        Exit Function
    End If

    ' The value of the empty reference header
    emptyHeaderValue = ThisWorkbook.Names.Item("emptyHeader").RefersToRange.Value
    l = Len(emptyHeaderValue) - 1

    targetValue = ra.Value

    If Left(emptyHeaderValue, l) = Left(targetValue, l) Then
        postFix = Right(targetValue, Len(targetValue) - l)
        isRealyEmpty= IsNumeric(postFix)
    Else
        isRealyEmpty= False
    End If

End Function

score 0 · Answer 3 · answered Jun 29 '13 at 13:56

This is a fool-proofed way to test if a cell is really really empty. We test if the original value is equal to the value of the cleared cell.

The drawback of this solution is that we have to modify the workbook, which was not acceptable in our case.

Function isRealyRealyEmpty(ra As Range) As Boolean
    v = ra.Formula
    ra.Formula = ""
    isRealyRealyEmpty = (ra.Formula = v)
    ra.Formula = v
End Function

Test for empty Listobject header

3 Answers3