0

I'm still learning VBA so I might be doing a lot of very basic mistakes. At the moment I'm trying to make a macro which could count the rows of a table. This sub works perfectly (with a table named "Tab").

Sub AddRowTable()
ActiveSheet.ListObjects("Tab").ListRows.Add (2)
End Sub

However, when I try to convert this sub to a function so I can call it with the name of the table as variable, I get a name error when writing "=AddRowTableFunction(Tab)" in a cell.

Function AddRowTableFunction(TableName)
ActiveSheet.ListObjects(TableName).ListRows.Add (3)
End Function

I understand that it is just a problem of type, but I just cant find how to do it properly.

Thanks.

Anon1378937
  • 29
  • 8
  • A UDF function returns only in the cell where from it is called. It does not modify other ranges. – FaneDuru Oct 11 '20 at 16:21
  • So if it works with a sub but not with a function, it is completely normal ? – Anon1378937 Oct 11 '20 at 19:40
  • Yes, it is. You can use an event (Worksheet_Change, for instance) to trigger the existing function. I mean, instead of calling it from a cell (UDF does not allow changes of other cells, except the one the call came from) you can change a value in a specific range and configure such an event to call the function doing what you need. If using a button is not convenient. – FaneDuru Oct 11 '20 at 19:59

3 Answers3

1

At the moment I'm trying to make a macro which could count the rows of a table.

First off, that is not what your code is doing. Your code is adding a row to the table. The number of rows would be accessed using ListRows.Count.

when I try to convert this sub to a function so I can call it with the name of the table as variable...

You don't need a Function in order to include a variable. The differance between a Sub and a Function is that a Function returns a variable, while a Sub does not (i.e. a Function gives back a variable to the code that used it). Both Subs and Functions can take variables.

A Function that returns the number of rows in a given table would be something like this:

Function AddRowTableFunction(TableName As String)
    AddRowTableFunction = ActiveSheet.ListObjects(TableName).ListRows.Count
End Function

For adding a row, you would probably use a Sub, because the action of adding a row doesn't return any information:

Sub AddRowTable(TableName As String)
    ActiveSheet.ListObjects(TableName).ListRows.Add
End Sub

Finally, when using the Function in a formula, as Apafey pointed out, you need to write "Tab" (in quotes), not just Tab. "Tab" tells Excel to pass the text of the word Tab, while Tab tells Excel to look for a range named Tab, which probably doesn't exist.

Keith Stein
  • 6,235
  • 4
  • 17
  • 36
  • I thought that I needed to use a function because if I write the code with a Sub, "AddRowTable" doesnt show up when I write "=AddRowTable(" so obviously typing =AddRowTable("Tab1") gives me an error... By the way, as I said to Apafey, writing =AddRowTableFunction("Tab1") does not work either, I get the same error. – Anon1378937 Oct 11 '20 at 17:38
  • Edit : I think I just understood what's happenning. I'm trying to use the function in a cell but because my function does not return anything, I guess the error, so I am supposed to use a sub and use a button. – Anon1378937 Oct 11 '20 at 17:47
0

You should try: =AddRowTableFunction("Tab")

You wrote: =AddRowTableFunction(Tab)

that is not fine.

Apafey
  • 66
  • 7
  • Ok. Probably Function is not the best for this aim. A VBA function - to be used in a cell - should return a value and not changing the sheet. In general, you would need to add a line "AddRowTableFunction = ..." at the end of the function to get result in the cell. Or, it could run as you wrote and then it could delete the content of the active cell, so you can be sure it won't change your sheet later. – Apafey Oct 11 '20 at 17:01
0

As FaneDuru said, an UDF function can't change other cells, which explains the error). Thnaks !

Anon1378937
  • 29
  • 8
  • No, it doesn't. `#NAME!` means the function was not found, because apparently you put it in a worksheet module and not [in a standard module](https://stackoverflow.com/a/43379337/11683). Changing the sheet would get you [a `#VALUE!` or a wrong result](https://stackoverflow.com/q/3622137/11683). – GSerg Oct 12 '20 at 15:54