Solved: Design Advice & VBA Usage

[macleanb]

Hi

Long time no see - and as you might have guessed I am in trouble!

I am creating a "P&L explain" workbook that presents a top level summary of P&L and then allows the user to drill down into each row. Each row is linked to a sepearate worksheet.

I would dearly like to structure my code so that each subsiduary worksheet exposes a similar set of methods/properties which enable me to access/control them (like an interface).

At present I have individually named subs like so:

Factor1_Refresh
Factor2_Refresh
Factor3_Refresh/Factor3_Display...

and so on. What I would like to do is more along the lines of:

Worksheets("Factor1").Refresh
Worksheets("Factor2").Refresh
Worksheets("Factor3").Refresh

Now the important part is this would then allow me to substitute "FactorX" with a variable, so that I could run the same functions/methods against an object that I reference at runtime.

Anyone got any good ideas (or am I missing something obvious)


Many, many thanks


Ben

[Norie]

Ben

Why not just pass the worksheets as parameters to subs?

[macleanb]

Hi Norrie

thanks for the reply.


All the subs need to do fifferent things depending on what they are explaining, so I just end up with a big case statement - which doesnt get me much further.

It's not that I cant do the actual coding thats required, I just wanted it to be a bit more, well, object orientated.


ttfn benm

[macleanb]

Hi Guys

just managed to figure out a way to do what I want - the problem I had was I was trying to do was the equivalent of this:

Dim wsTmp as worksheet
wsTmp = worksheets("FooBanana")
wsTmp.MyGenericFunction()

But of course the compiler knew that worksheets dont have a method called "FooBanana" so I get an error. SO the cure? should have been obvious:


Dim wsTmp as object
wsTmp = worksheets("FooBanana")
wsTmp.MyGenericFunction()

Now the compiler has no idea what methods are supported, and I am now well on my way....


Thanks For looking....

[lucas]

Glad you worked it out and thanks for posting your solution. Nice job Ben.
ttfn

[macleanb]

My Actual Code below:

[VBA]
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim HelperStr As String
Dim CCYString As String
Dim TargetWS As Object

HelperStr = ActiveSheet.Cells(Target.row, [DrillDownHelper].Column)
CCYString = ActiveSheet.Cells([DrillDownHelper].row, Target.Column)

Set TargetWS = GetWSFromCName(HelperStr)

If HelperStr <> "" Then

If Len(CCYString) <> 3 Then CCYString = "All"
TargetWS.Visible = True
TargetWS.Activate
TargetWS.RefreshMe CCYString

End If


Cancel = True
End Sub
[/VBA]