VBA Property - Declare in Sheet or Modules?

Uhndun20

New Member
Joined
Mar 15, 2021
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
Hello,

I've been doing some reading recently and many people are saying the same thing with regards to putting code in a worksheet - "Declare only events in a worksheet, nothing more". The statement in itself isn't that crazy, but it makes me ponder if it's a hard set rule of if there are exceptions?

For instance, should a property or constant that applies exclusively to a particular worksheet still be declared in a module or would it be better to leave it in the worksheet? Moreover if I'm not declaring it in the worksheet, shouldn't it be in a class module instead of a basic module? A benefit of leaving code in the worksheet is it opens up to the "me." keyword. With this the user never needs worry if a worksheet codename is changed and it localizes it to the object reference.

Don't get me wrong I'm not trying to argue one way or another, but I ask this question because that is currently how I have some code setup and I can't help but wonder if I should move it to a module for overall more manageable code. Thoughts?

Example:
Worksheet:
VBA Code:
Const intSide1Length As String = "E46"

Property Get Side1Length()
    Side1Length = Me.Range(intSide1Length).Value
End Property

Property Let Side1Length(intSide1LengthValue)
    Me.Range(intSide1Length).Value = intSide1LengthValue
End Property

Module "mInitialize:
VBA Code:
mUtility.RoundUp((mUtility.FootToInch(Sheet1.Side1Length)

Module "mUtility:
VBA Code:
Function RoundUp(Number As Double, Digit As Double)
'Rounds Number up to decimal place indicated in Digit.

    'ROUND UP VALUE(S):
    RoundUp = Application.WorksheetFunction.RoundUp(Number, Digit)

End Function
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Uhndun20

New Member
Joined
Mar 15, 2021
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
Sorry forgot a snippet. It's not important to the overall question, but it would help read the code a little better.

VBA Code:
Function FootToInch(Feet As Double)
'Converts feet to inch(s).

    'CONVERT FEET TO INCH(S):
    FootToInch = Feet * 12

End Function
 

Forum statistics

Threads
1,148,249
Messages
5,745,627
Members
423,964
Latest member
Dustin M

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top