I'm probably missing something simple, but ListRows.Add is giving me grief. Here's the function:
Sub addEmployee(employeeName As String, tableToAddTo As ListObject)
Dim newRow As ListRow
Set newRow = tableToAddTo.ListRows.Add()
newRow.Range.Cells(1, 1).Value = employeeName
tableToAddTo.Sort.Apply
End Sub
In most cases, this works fine. However, whenever the function runs on a certain table in my worksheet, the lines following the call to ListRows.Add are never executed (at least that's what the debugger indicates) and the row does not get added to the table. Any thoughts/ideas?
UPDATE:
Here's what I've learned since the post. If I feed the Sub with stub data it works fine. For example:
Sub driver()
Dim myTable As ListObject
Set myTable = getTableObject("myTableName")
Call addEmployee("myName", myTable)
End Sub
Note: getTableObject cycles through the worksheets and returns the ListObject with the matching name.
It seems to be an issue dealing with the context in which the code is called. In the case that fails, a formula (Function call) has been placed in various cells of various worksheets. The formula contains references to data in other cells. When the data in the other cells changes, the formula is invoked, which in turn calls the addEmployee Sub that is given above. This is the case that fails.
Answer
It appears that what I was trying to do is not possible.
When you develop custom functions, it’s important to understand a key distinction between functions that you call from other VBA procedures and functions that you use in worksheet formulas. Function procedures used in worksheet formulas must be passive. For example, code within a Function procedure cannot manipulate ranges or change things on the worksheet. An example can help make this clear.
You might be tempted to write a custom worksheet function that changes a cell’s formatting. For example, it could be useful to have a formula that uses a custom function to change the color of text in a cell based on the cell’s value. Try as you might, however, such a function is impossible to write. No matter what you do, the function won’t change the worksheet. Remember, a function simply returns a value. It cannot perform actions with objects.
That said, I should point out one notable exception. It is possible to change the text in a cell comment by using a custom VBA function.
Walkenbach, J. (2007). Microsoft Office Excel 2007 Power Programming with VBA. John Wiley & Sons, p 280.