PDA

View Full Version : Group Variables together



Djblois
06-25-2010, 06:15 AM
I do not know if this is possible in VBA but I want to have a bunch of variable values grouped together. Here is an example:

The Variable would be stgReportType

If I create this group - Sales Reports (Detail Reports, Product Summary Reports, Customer Summary Reports, etc.)

Then I want to give Sales Reports certain Characteristics after that If I use the value of Detail Reports it will use all the Characteristics of Sales Reports and any Characteristics I have defined for Detail Reports themselves.

Is this possible? I think if it is possible it has to do with Object Oriented programming but I have done nothing in OO and do not know if Excel is capable of this.

Bob Phillips
06-25-2010, 06:29 AM
This sounds like you want arrays, UDTs, or maybe a custom class, but there is insufficient detail as to what you mean by grouping.

Djblois
06-25-2010, 06:41 AM
I want to perform specific tasks to all Sales Reports. I currently have been using two Separate Variables - one that would tell the program that it is a Sales Report and another for the specific type of Sales Report. Now, I want to simplify the code and want to only use one variable and let it inherit the attributes of the its parent.

Bob Phillips
06-25-2010, 07:04 AM
It is a Sales Report! What is IT?

What parent, what attributes?

You may know what you mean, we have no dea.

Djblois
06-25-2010, 07:13 AM
It is data in Excel that I define as a Sales Report.

I want Sales Report to be the Parent and the different types of Sales Reports to be the Child. I want all Sales Reports to have specific Page Setups, Specific way to format all the columns, etc.

Paul_Hossler
06-25-2010, 07:25 AM
1. There's guys here who are a whole lot better that I am at Classes (aka 'objects'), but here's a first shot

2. Defined a Report class, and 2 other classes (Sales and Expense) that inherit Report and add some Sales and some Expense specific properties

3. Simple ConOps demo:


Option Explicit

Sub drv()
Dim RptSales As New clsReportSales
Dim RptExpense As New clsReportExpense
With RptSales
.Report.Title = "Sales Report"
.Report.Footer = "Sales Report"
.SalesFigures = Array(1, 2, 3, 4, 5)
Call .Report.PrintReport
End With
With RptExpense
.Report.Title = "Expense Report"
.Report.Footer = "Expense Report"
.ExpenseFigures = Array(6, 7, 8, 9, 10)
Call .Report.PrintReport
End With

End Sub




'Report class
Option Explicit
Private sReportTitle As String
Private sReportFooter As String

Property Let Title(s As String)
sReportTitle = s
End Property
Property Get Title() As String
Title = sReportTitle
End Property
Property Let Footer(s As String)
sReportFooter = s
End Property
Property Get Footer() As String
Footer = sReportFooter
End Property

Sub PrintReport()
MsgBox "Printing report titled " & Title
End Sub




'report Expense Class
Option Explicit
Private cReport As New clsReport
Private aExpenseFigures As Variant
Property Set Report(R As clsReport)
Set cReport = R
End Property
Property Get Report() As clsReport
Set Report = cReport
End Property

Property Let ExpenseFigures(v As Variant)
aExpenseFigures = v
End Property
Property Get ExpenseFigures() As Variant
ExpenseFigures = aExpenseFigures
End Property


This might not be the most 'professional' way to do it, but I'm confident that some one will point that out to me :rotlaugh:

Actually, I really would like to get suggestions, etc.

Paul