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

Answer 1

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.

Answer 2

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