CODEWITHSUNDEEP
vbaexcelclassmodule
CBK
CBK

Reputation: 690

Excel VBA: Should I Use a Class Module?

Heylo, I am always looking to make my code cleaner, better, and more efficient. I am also always looking to make it easier to use and more maintainable. I have experience with Object-Oriented programming in C/C++, but I have been avoiding it with Excel VBA. I have been doing a lot of reading on Class Modules in VBA but I am unable to determine whether or not they are worthwhile for my situation.

I have a custom Excel Add-In that has a normal Module. This module (generically named 'Module1') contains about 18 subroutines that all do completely different things. Each of these subroutines is connected to a custom button in a custom tab using the ByVal control As IRibbonControl argument in each subroutine, and some Ribbon XML to drive the layout and function.

Anyways, as an example, I have written what I believe the Class Module and Normal Modules would look like in my case if I were to implement them:

' Class Module: TestClass
Private sumVal As Double
Private wsNames As String
Public Sub Add(myRange as Range)
   For Each myCell In myRange
      sumVal = sumVal + myCell.Value
   Next myCell
   MsgBox "The sum of your range is: " & sumVal
End Sub

Public Sub ChangeSettings()
   Application.Calculation = xlCalculationManual
   Application.ScreenUpdating = False
   Application.DisplayStatusBar = False
   MsgBox "The settings have been changed!"
End Sub

Public Sub PrintHello()
   MsgBox "Hellurrr!"
End Sub

Public Sub PrintSheetNames()
   Dim ws As Worksheet
   For Each ws In ActiveWorkbook.Sheets
      wsNames = wsNames + ws.Name + ", "
   Next ws
   MsgBox "The names of all sheets in this workbook are: " & wsNames
End Sub



' Normal Module: Module1
Sub RunAdd(ByVal control As IRibbonControl)
   Dim ClsObj As New TestClass
   Dim theRange As Range
   Set theRange = Selection
   ClsObj.Add theRange
End Sub

Sub RunChangeSettings(ByVal control As IRibbonControl)
   Dim ClsObj As New TestClass
   ClsObj.ChangeSettings
End Sub

Sub RunPrintHello(ByVal control As IRibbonControl)
   Dim ClsObj As New TestClass
   ClsObj.PrintHello
End Sub

Sub RunPrintSheetNames(ByVal control As IRibbonControl)
   Dim ClsObj As New TestClass
   ClsObj.PrintSheetNames
End Sub

Like this example, all of the subroutines I have are inherently different. My subroutines do not share variables or properties (so the iconic Car class is not applicable), nor does one subroutine execute several other subroutines.

Would reformatting my current Normal Module into a new Class Module + Normal Module (as the driver) be beneficial to my situation at all?

Thank you for any of your input in advance.

Upvotes: 0

Views: 2908

Answers (1)

Valon Miller
Valon Miller

Reputation: 1156

Based on the code you provided, it doesn't really make sense to use a class module.

Class modules are used to define custom objects.

Chip Pearson's site has good content on this topic: http://www.cpearson.com/excel/classes.aspx

Upvotes: 2

Related Questions