Calling a function from macro

Question

I am trying to use the function: Cells(Rows.Count, 14).End(xlUp).Row to determine the last row with data. This and similar formulas appear in many spots in my VBA code. My spreadsheet is quite complex and I often need to add columns. So rather than referring to Column 14, I'd like to use a value I can easily change in only one place. I am using a named Column of sorts that I only need to update in one place. Items sold appears in column N. I realize that this code is very simple, but I simplified it down for the sake of asking a question. When I try to run the Test sub, I a compile errror: ByRef argument type mismatch and the variable Item_Sold. The reason I am using a letter is that Range() needs a letter, but Cells().End(xlUp).Row needs a number.

It works if I move: Items_Sold = "N" to the sub from the function, but this means this code would have to go in every sub rather than in just one function.

Function:

Function ColNum(ColumnNumber As String) As Integer
Dim Items_Sold As String
Items_Sold = "N"
ColNum = Range(Replace("#:#", "#", ColumnNumber)).Column
End Function

Macro:

Sub Test()
Dim Total_Items_Sold As Integer
Total_Items_Sold = Cells(Rows.Count, ColNum(Items_Sold)).End(xlUp).Row
End Sub

http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba — David Zemens, May 27 '14 at 20:17
You already seem to be hardcoding the column letter - why not just use a int variable for the column number? — Dave.Gugg, May 27 '14 at 20:18
You may be getting an error because when you call the functino `ColNum(Items_Sold)`, `Items_Sold` is not a declared variable and so is treated as `Empty`, which is a numeric/int, but your function is expecting a string... — David Zemens, May 27 '14 at 20:19
@Dave.Gugg because in most places I will reference it with Range() which wants the letter, not a number. — user2242044, May 27 '14 at 20:23

score 4 · Accepted Answer · answered May 27 '14 at 20:35

4

Use a Global constant.

At the top of your module, outside of any Sub/Function:

Const COL_NUM As Long = 14

Usage:

Sub Test()
    Dim Total_Items_Sold As Long
    Total_Items_Sold = ActiveSheet.Cells(Rows.Count, COL_NUM).End(xlUp).Row
End Sub

answered May 27 '14 at 20:35

Tim Williams

154,628
8
97
125

score 0 · Answer 2 · answered May 27 '14 at 20:48

Add a global variable at the beginning of the code by writing

public Items_Sold as String

that will solve your ByRef problem. But still you will have problem with undeclared value of Items_Sold when invoking Test() unless you set the value of Items_Sold somewhere before.

Calling a function from macro

2 Answers2