CODEWITHSUNDEEP
vba
Anon1378937
Anon1378937

Reputation: 29

Name error when trying to call a function

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.

Upvotes: 0

Views: 117

Answers (3)

Anon1378937
Anon1378937

Reputation: 29

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

Upvotes: 0

Keith Stein
Keith Stein

Reputation: 6754

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.

Upvotes: 1

Apafey
Apafey

Reputation: 66

You should try: =AddRowTableFunction("Tab")

You wrote: =AddRowTableFunction(Tab)

that is not fine.

Upvotes: 0

Related Questions