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

 

[AlphaFrog]

I don't see anything wrong with this...
Set r = Sheets("Budget").Cells(16, colnum)
...assuming you've assigned a value to colnum prior to this line of code.

When it errors, hover your mouse over colnum and the pop-up context should display it's current value.

Is the sheet name correct? Is there a second workbook that may be active and it doesn't have the Budget sheet?

The UpdateCorrectColumn function is not technically a function; It's just another sub procedure. This is not your issue though.

 

[chazrab]

...assuming you've assigned a value to colnum prior to this line of code.
(what do you mean by this ?- the Public declaration already has defined colnum, has it not ? A separate line defining colnum anywhere should not be necessary as that would contradict the purpose of defining it as a Public variable in a new module! )


When it errors, hover your mouse over colnum and the pop-up context should display it's current value.
(hover over value colnum value = 0 . Weird! The correct value of colnum should be 50 = the 50th week of the year on my BUDGET sheet. Col A has line items; colnum starts at col. B)

Is the sheet name correct? Is there a second workbook that may be active and it doesn't have the Budget sheet?
(the sheet name is correct and there is no second workbook with that sheet name


The UpdateCorrectColumn function is not technically a function; It's just another sub procedure. This is not your issue though.
(how is this not a function if it is defined between Function -End Function)

 

[AlphaFrog]

...assuming you've assigned a value to colnum prior to this line of code.
(what do you mean by this ?- the Public declaration already has defined colnum, has it not ? A separate line defining colnum anywhere should not be necessary as that would contradict the purpose of defining it as a Public variable in a new module! )

The Public declaration simply declares the variable so its scope is global i.e. available to all procedures. Declaring a variable does not assign a value to it. After declaration, colnum equals zero by default until you give it a value using the line colnum = Round((Date - DateValue("12/20/2017")) / 7, 0)
If you don't give it a value >0, then Cells(16, colnum) throws an error because there is no column zero.

(how is this not a function if it is defined between Function -End Function)

Yes, you've set it up as a function, but it is not really a function. A function returns a value to the caller. Your function does not return a value to the caller.

It does work the way you've set it up as a Function, but it is only working like a standard Sub procedure.

This is an example of a true Function.

[COLOR=darkblue]Sub[/COLOR] Test()
 [COLOR=darkblue]Dim[/COLOR] MyName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
 MyName = WhatIsMyName
 MsgBox MyName
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Function[/COLOR] WhatIsMyName()
     WhatIsMyName = "AlphaFrog"
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

The Test procedure called the Function WhatIsMyName and then WhatIsMyName returned a value to the caller.

Your Function UpdateCorrectColumn set a value of a public variable. It does not return a value to the caller. That's the distinction between a Function and a standard sub procedure.

 

[chazrab]

AlphaFrog - many thanks for straightening me out on this. Let me see if I have this correct: In a new module then

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

 colnum = Round((Date - DateValue("12/20/2017")) / 7, 0) - setting the value after it is declared Public   outside of the Function End Function  but right brlow the End Function within the module

Please let me know if this correct.
Question: This is all in a new module. Can I put all of this same code in ThisWorkbook instead of a module ?


Thanks for all your help

cr

 

[AlphaFrog]

Not correct,

Put this in a standard code module...

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

Before you use colnum in any other code, call the UpdateCorrectColumn procedure so colnum is given a value.

Sub Example()
    UpdateCorrectColunm
    Msgbox "Today's column is " & colnum
End Sub

 

[RoryA]

Personally, I'd skip the variable and just use a function called colnum:

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

 

[chazrab]

Rory - where would you put this

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

In a new module ? I would prefer to put it in ThisWorbook at the very top (beginning ) of other code in This Workbook if it will work there. As mentioned in this thread, colnum HAS to be available to all code in the entire workbook. colnum's changes from week to week: this week it's 50. Next week it should be 51, etc. for the correct column values to be cumulated.


Thanks for all you help also

cr

Hence the reason for the discussion with Alphafrog in making colnum a Public or Global variable. I want to be able to change its value at EOY every year in just one place - not in code on 12 different Userforms. Obviously to both of you, my level of understanding of using a Public variable is limited at best. I'm just trying to make it easier and less laborious at the end of each year to change one line of code in many different areas.

 

[RoryA]

Put it in a new module. Why do you want to put it in ThisWorkbook?

 

[chazrab]

Put it in a new module. Why do you want to put it in ThisWorkbook?

My understanding is if put in ThisWorkbook Open, it will be available to all the code procedures in the entire workbook. I'm unsure if it will be visible and available by placing it in a new module

Secondly, the new module number is 131. I'd have to scroll all the way down and remember that it is stored in module 131. Put in ThisWorkbook, all I have to do is go to
ThisWorkbook and change it. So - tell me what's wrong with my logic ?

Thanks for your help
cr