CODEWITHSUNDEEP
excelexcel-2010vba
EplusL
EplusL

Reputation: 223

Excel 2010 VBA - global variables aren't set properly

UPDATE: I know I can just rename the sheets in VBA Editor, but my first instinct was to use code. I looked it up, saw the solution in several places, and now that it doesn't work I still want to know why. The answer might mean I'll learn something critical for later programming in VBA.

I thought I found the answer here: https://stackoverflow.com/a/1179730/1370938, but it turns out that doesn't work for me, I don't know why. I'm new to VBA so I might be missing something, but I found this solution in multiple sites and I'm wondering if I missed a simple step that was not explicited.

I wanted to set global worksheet variables that I could call for all my functions and forms data manipulation and make the code look lighter. (all those Worksheets("name"). really fatten up the code when you jump from sheet to sheet).

Following the answer in the link, I wrote this in one of my three modules:

'Global variables
Public shT As Worksheet
Public shA As Worksheet
Public shC As Worksheet
Public shM As Worksheet
Public shP As Worksheet

'Setting global variables
Sub Workbook_Open()

   Set shT = ActiveWorkbook.Worksheets("Tasks")
   Set shA = ActiveWorkbook.Worksheets("Activity")
   Set shC = ActiveWorkbook.Worksheets("Closed")
   Set shM = ActiveWorkbook.Worksheets("Modifications")
   Set shP = ActiveWorkbook.Worksheets("Persistent")

End Sub

But When running this code in another module, the variable is set with "Empty" when trying to access ranges etc and I get runtime error '424' Object Required which points to the line .Rows(3).EntireRow.Insert.

Here is the code

Sub addModLogEntry(sTask As String, sOwner As String, dDate As Date, sType As String)
    Dim iRow As Integer
    With shM
        .Rows(3).EntireRow.Insert

        .Range("A" & 3).value = sTask
        .Range("B" & 3).value = sOwner
        .Range("C" & 3).value = dDate
        .Range("D" & 3).value = sType

        If Day(dDate) Mod 2 = 0 Then
             shM.Range("A" & 3 & ":D" & 3).Interior.Color = RGB(230, 230, 230)
        End If

    End With

End Sub

When in debug mode if I watch the value of shM it returns Empty. I tried putting a breakpoint on the first line of Workbook_Open() but the breakpoint isn't saved when I close the document to test opening it.

I tried puttin this code in the code for thisWorkbook and here the breakpoint works, workbook_open() is executed, but if I execute anything after it the variables are still no good.

Upvotes: 1

Views: 5349

Answers (1)

Gary's Student
Gary's Student

Reputation: 96791

Dim the variables in a Standard Module, not in the ThisWorkbook Module above the Open event code.

EDIT#1:

In the ThisWorkbook Module:

Private Sub Workbook_Open()
    Set shT = ActiveWorkbook.Worksheets("Tasks")
   Set shA = ActiveWorkbook.Worksheets("Activity")
   Set shC = ActiveWorkbook.Worksheets("Closed")
   Set shM = ActiveWorkbook.Worksheets("Modifications")
   Set shP = ActiveWorkbook.Worksheets("Persistent")
End Sub

and in a standard module:

Public shT As Worksheet
Public shA As Worksheet
Public shC As Worksheet
Public shM As Worksheet
Public shP As Worksheet

Sub durAL()
    MsgBox shT.Range("A1").Value
End Sub

running durAL will demonstrate the globals are available for common use.

Upvotes: 2

Related Questions