CODEWITHSUNDEEP
excelvba
Excel Jr.
Excel Jr.

Reputation: 3

How do I make a constant global (work across all my functions) in VBA?

I have gone over most of stack's questions on the matter but can't seem to get it running with my code.

Goal: I have 3 functions: main, fun1, and clear. All three require the use of the datalink as a worksheet type. They are all found in the same module in the same workbook. I set up Workbook_Open in "ThisWorkbook"

Problem: I get the run-time error '424': object required at the "datalink.Cells(1, 10).Value = 0" line

Code:

'this is how I am setting up my global variable
Public datalink As Worksheet

Sub Workbook_Open()

    Set datalink = ActiveWorkbook.Worksheets("Sheet1")

End Sub

'deletes all sheets except Sheet1
Sub Clear()

    For Each Sheet In Sheets
        Application.DisplayAlerts = False
        If Sheet.Name <> "Sheet1" Then Sheet.Delete
    Next Sheet

    datalink.Cells(1, 10).Value = 0
    Application.DisplayAlerts = True

   'Sheet1.Columns(10).ClearContents
End Sub

EDIT:

I have edited my code above to what I am currently using. I had pasted dated code. My apologies, I should've been clear on that I am trying to prevent from changing all the "Sheet1"s in my modules so I decided to call them datalink, and that way I can just change what "datalink" will be equal to to affect all the module's values for that. Basically, I am trying to make it so I don't have to copy paste the following code in every single document when I change "Sheet1" into some other name

Dim datalink As Worksheet
Set datalink = Sheets("Sheet1") 'master sheet that contains PI data from datalink

Upvotes: 0

Views: 183

Answers (2)

ecramer
ecramer

Reputation: 341

Not sure if this is the exact issue but your 'Clear' function is assuming that ActiveSheet is Sheet1. If the ActiveSheet isn't Sheet1 and you try to 'Clear', Sheet1 will be deleted and therefore datalink will be pointing to a sheet that doesn't in fact exist.

Try changing your code to:

If Sheet.Name <> datalink.Name Then Sheet.Delete

Or even better, don't use the Workbook_Open function. It isn't necessary in this instance. Use the object Sheet1 (like you have in your commented out code):

If Sheet.Name <> Sheet1.Name Then Sheet.Delete

Sheet1 can be accessed everywhere as well.

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

First place the line:

Public datalink As Worksheet

as the first line in a standard module

Then:

Sub Clear()

    For Each Sheet In Sheets
        Application.DisplayAlerts = False
        If Sheet.Name <> "Sheet1" Then Sheet.Delete
    Next Sheet

    datalink.Cells(1, 10).Value = 0
    Application.DisplayAlerts = True

    datalink.Columns(10).ClearContents
End Sub

Upvotes: 1

Related Questions