Making a Public variable work as it should

[chazrab]

I'm trying to use this as a Public or Global variable

colnum = Round((Date - DateValue("12/20/2017")) / 7, 0)

I did this:
Insert -> Module:

Public colnum As Integer
Function UpdateCorrectColumn() As Integer
 colnum = Round((Date - DateValue("12/20/2017")) / 7, 0)
End Function

Result:
Run time error 1004: Application defined or object defined error at this line in my VBA code

Dim r As Range
Case Is = "Food"
      Set r = Sheets("Budget").Cells(16, colnum) <----bugs out here highlighted yellow

Can anyone tell me what's wrong ? Seems simple enough to declare a Global variable only once in one place only. I keep copying

 colnum = Round((Date - DateValue("12/20/2017")) / 7, 0)

in VBA code in buttons on12 different Userforms. Having to change each one to a new colnum value at EOY is a real pain.


Thanks for anyone's help.

cr

 

[RoryA]

First, it doesn't belong there. It belongs in a normal module (which you can rename, incidentally).
Second, if you put it into Thisworkbook, you have to remember to refer to it as Thisworkbook.colnum every time.