VBA - Coding Efficiencies
Hi,
I'm trying to clean up a project's code by making everything more readable. Instead of having 1 sub that goes for 100 lines that does 6 different actions, I want each action as its own sub.
I came to the issue where I found myself repeating defining certain variables over and over again (the lump code method, it's defined once). This is increasing the amount of coding required and I'm looking for a way to define the variable once and access it any where in my project.
I read if I use "Option Private Module" in the top lines then use "Public" to declare my variables, the variables can be used anywhere. However, this doesn't seem to retain the values. how would one be able to obtain the values from the variables anywhere in the project? Should I use "ByRef" such as
Sub SheetReference(ByRef srcVmasterData as Worksheet, ByRef srcVmasterCal as Worksheet, ByRef srcAmendData as Worksheet......)
Set srcVmasterData = sheet1
set srcVmasterCal = sheet2
Set srcAmendData= sheet3
end sub
Sub NewProcedure()
Dim x as worksheet, y as worksheet, y as worksheet
SheetReference srcVmasterData:=x, srcVmasterCal:=y, srcAmendData:=z
'more code here
end sub
I'm a little stuck on this. Any help would be much appreciated. Thanks.
I'm trying to clean up a project's code by making everything more readable. Instead of having 1 sub that goes for 100 lines that does 6 different actions, I want each action as its own sub.
I came to the issue where I found myself repeating defining certain variables over and over again (the lump code method, it's defined once). This is increasing the amount of coding required and I'm looking for a way to define the variable once and access it any where in my project.
I read if I use "Option Private Module" in the top lines then use "Public" to declare my variables, the variables can be used anywhere. However, this doesn't seem to retain the values. how would one be able to obtain the values from the variables anywhere in the project? Should I use "ByRef" such as
Sub SheetReference(ByRef srcVmasterData as Worksheet, ByRef srcVmasterCal as Worksheet, ByRef srcAmendData as Worksheet......)
Set srcVmasterData = sheet1
set srcVmasterCal = sheet2
Set srcAmendData= sheet3
end sub
Sub NewProcedure()
Dim x as worksheet, y as worksheet, y as worksheet
SheetReference srcVmasterData:=x, srcVmasterCal:=y, srcAmendData:=z
'more code here
end sub
I'm a little stuck on this. Any help would be much appreciated. Thanks.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
I think I figured out why it didn't work before. I tried to run test() and it returned an object not set error so I then tried to pass byref (and started this question). But this time, if I run Reference() first then run Test(), the values are retained! YES! . Since the values are retained and eats up memory, is there a way to free up the memory? I'm reading that Public variables are retained as long as the workbook is open, I was just wondering. THANKS
Option Explicit
Public intTotalRows As Integer, intTotalColumns As Integer
Public srcWorksheet As Worksheet
Sub References()
Set srcWorksheet = Sheet1
intTotalRows = srcWorksheet.Columns(1).Rows.SpecialCells(xlCellTypeConstants).EntireRow.Count
intTotalColumns = srcWorksheet.Rows(1).Columns.SpecialCells(xlCellTypeConstants, xlTextValues).EntireColumn.Count
End Sub
Sub test()
srcWorksheet.Cells(1, 1).Resize(intTotalRows, intTotalColumns).Select
End Sub
ASKER
I'm running into an error when I try the code below. "Select method of Range class failed". It's weird that the code above worked but a similiar implementation doesn't. Any suggestions?
Option Explicit
Public srcVmasterData As Worksheet, srcAmendData As Worksheet
Public srcVmasterCal As Worksheet, srcAmendCal As Worksheet
Public srcControls As Worksheet
Public trgAnalysis As Worksheet, trgTraderErrors As Worksheet
Sub Reference()
Set srcVmasterData = Sheet4
Set srcVmasterCal = Sheet2
Set srcAmendData = Sheet6
Set srcAmendCal = Sheet5
Set trgAnalysis = Sheet3
Set srcControls = Sheet1
Set trgTraderErrors = Sheet7
End Sub
Sub Test()
srcVmasterData.Cells(1, 1).Resize(10, 10).Select
End Sub
Sub DoBoth()
Call Reference
Call Test
End Sub
ASKER
Nevermind, sorry. I closed my excel and it worked when I restarted it.
ASKER
Hey I have a follow up question. I tried it and it didn't work but I was wondering instead of
Dim shtVmasterdata as Worksheet
set shtVmasterdata = sheet2
I saw that you can Dim shtVmasterdata as Sheet2, but the code didn't work statiang that the object wasn't set? is this method possible?
Dim shtVmasterdata as Worksheet
set shtVmasterdata = sheet2
I saw that you can Dim shtVmasterdata as Sheet2, but the code didn't work statiang that the object wasn't set? is this method possible?
sheet2 is not a valid object.
Dim shtVmasterdata as Worksheet
set shtVmasterdata = Activeworkbook.WorkSheets(
or set shtVmasterdata = Activeworkbook.WorkSheets(
Dim shtVmasterdata as Worksheet
set shtVmasterdata = Activeworkbook.WorkSheets(
or set shtVmasterdata = Activeworkbook.WorkSheets(
ASKER
I think I got it down. Correct me if I'm wrong since I'm still a little hazy on VBA. Thanks for your help tho. I couldn't find a great article that clarified it. Let me start recoding!