Declaring variables for use in a Module

  • Thread starter Thread starter Ayo
  • Start date Start date
A

Ayo

Is there a way to declare these statement within a Module so that I don't
have to do it in every Subroutine in the Module?

Set errorWS = Worksheets("Error_MarketList")
Set bulkuploadWS = Worksheets("Bulkupload Result")

Right now I have this:

Private Sub cmdbuildBulkUpload_Click()
Set errorWS = Worksheets("Error_MarketList")
errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row

Private Sub cmdupdateDates_Click()
Set errorWS = Worksheets("Error_MarketList")
errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Set siteWS = Worksheets("Site Milestone Dates")
Set errorWS = Worksheets("Error_MarketList")
errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row

Private Sub cmdupdateMarketlist_Click()
Set updateWS = Worksheets("Updated_MarketList")
Set errorWS = Worksheets("Error_MarketList")
errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row
updateWS_lastRow = updateWS.Cells(Rows.Count, "A").End(xlUp).Row - 2

Sub changeMarketList()
Set errorWS = Worksheets("Error_MarketList")
Set updateWS = Worksheets("Updated_MarketList")

Sub build_BulkUpload(Sdate As String, Sstatus As String, cRow As Integer)
Dim siteRow As Long
Dim bulkuploadWS As Worksheet, errorWS As Worksheet
Set errorWS = Worksheets("Error_MarketList")
Set bulkuploadWS = Worksheets("Bulkupload Result")
 
You could make them a Public variable.

Put this in a standard module.

Public errorWS As Worksheet
Public bulkuploadWS As Worksheet

Keep in mind this only declares the variable. You still need to set a
worksheet to it. Maybe you could use the Workbook Open Event.

Private Sub Workbook_Open()

Set errorWS = Worksheets("Error_MarketList")
Set bulkuploadWS = Worksheets("Bulkupload Result")

End Sub

At this point you can use your errorWS and bulkuploadWS variables in your
procedures. Note: If anywhere in your procedures you use the End method
your public variable will have to be re Set.

Hope this helps! If so, let me know, click "YES" below.
 
Back
Top