Global variables?

Hi all. @RustyNail, you might also consider moving your sub procedures to (the same?) standard module. Sheet and Workbook modules are traditionally reserved for event procedures and Class-related stuff.
http://www.cpearson.com/excel/codemods.htm

If you want that code to execute when the workbook opens, you must either put it in a Workbook_Open event procedure (which DOES belong in the ThisWorkbook module), or call it from same.

Private Sub Workbook_Open()
    Dim PARTS As Integer
    PARTS = 9
    NoOfParts = PARTS - 1
End Sub

...OR:

Private Sub Workbook_Open()
    setup
End Sub

BTW, there's no logical reason to define PARTS at the procedure level in this scenario unless you're calculating its value in multiple steps. If you need to retain its value in memory, declare it at the module level.

Hello leelnich,

I do your first one, but when VBA code runs in another sheet, NoOfParts is empty when its supposed to equal to 8 ????

here's what I do:

'================================ In This Workbook
Public NoOfParts As Integer

Private Sub Workbook_Open()
Dim PARTS As Integer

PARTS = 9
NoOfParts = PARTS - 1

'msgbox "I display on open"   ' <<<<<<<<< Message box pops up on Open

End Sub

'================================ A module in another sheet 

Sub ClrFilter()
Dim unmergeTheCells As Integer
Dim MyStr As String

unmergeTheCells = ((NoOfParts + 1) * 5) + 5     ' <<<<<<< NoOfParts is Empty?????
MyStr = "" & "B6" & ":E" & CStr(unmergeTheCells) & ""


'....

End sub

thanks for your reply

Public variables MUST be declared in a standard code module, not in a class module (ThisWorkbook, a sheet module, or a custom class).

This in the ThisWorkbook Object module:

Private Sub Workbook_Open()

NoOfParts = 8

End Sub

...and the rest, especially the variable declaration, in a standard (BAS) module:

Public NoOfParts As Integer   '<<< This in a standard module.

Sub ClrFilter()
Dim unmergeTheCells As Integer
Dim MyStr As String

unmergeTheCells = ((NoOfParts + 1) * 5) + 5     ' <<<<<<< NoOfParts is Empty?????
MyStr = "" & "B6" & ":E" & CStr(unmergeTheCells) & ""


'....

End Sub

To create a new BAS module, use the Insert menu, or press ALT+I, ALT+M.
Check this link for a discussion of VBA variable Scope:
http://www.cpearson.com/excel/Scope.aspx

You're welcome! If concluded, please mark your thread as SOLVED (Thread Tools above post #1). Regards – Lee

Why use a variable at all if you are going to set it to a fixed value in the Workbook_Open event? Simply put

Public Const NoOfParts As Integer = 8

at the top of a normal module and remove the Open event code.

Originally Posted by xlnitwit

Why use a variable at all ...

True IF the value isn't changed later... but why would you assume that?

I based the assumption on the fact the OP stated "I set a constant global variable"

OOPS!
...and quite a reasonable conclusion it is, sir!