Looking for a cleaner way store groups of data 3

[davidd31415]

I have 7 "memory" buttons on my form. When one of them is pressed several textboxes on the form are filled with pre-set values. I have been storing the values in constants but I am hoping there is a cleaner way to write code to handle this. Here is an example of what I am currently doing (only two memory buttons shown for example):

Private Const PortraitMem1 As Boolean = False
Private Const LandscapeMem1 As Boolean = True
Private Const ScaleMem1 As Double = 0.12
Private Const NumAcrossMem1 As Integer = 6
Private Const NumDownMem1 As Integer = 2
Private Const SpacingAcrossMem1 As Integer = 115
Private Const SpacingDownMem1 As Integer = 95
Private Const StartTopMem1 As Integer = 110
Private Const StartLeftMem1 As Integer = 15
Private Const OutputSheetMem1 As String = "OutputSheet"

Private Const PortraitMem2 As Boolean = True
Private Const LandscapeMem2 As Boolean = False
Private Const ScaleMem2 As Double = 0.21
Private Const NumAcrossMem2 As Integer = 2
Private Const NumDownMem2 As Integer = 2
Private Const SpacingAcrossMem2 As Integer = 225
Private Const SpacingDownMem2 As Integer = 180
Private Const StartTopMem2 As Integer = 120
Private Const StartLeftMem2 As Integer = 15
Private Const OutputSheetMem2 As String = "OutputSheet"

Private Sub btnMemory1_Click()
    frmMain.optionPortrait = PortraitMem1
    frmMain.optionLandscaped = LandscapeMem1
    frmMain.textScale = ScaleMem1
    frmMain.textNumAcross = NumAcrossMem1
    frmMain.textNumDown = NumDownMem1
    frmMain.textSpacingAcross = SpacingAcrossMem1
    frmMain.textSpacingDown = SpacingDownMem1
    frmMain.textStartTop = StartTopMem1
    frmMain.textStartLeft = StartLeftMem1
    frmMain.textOutputSheet = OutputSheetMem1
End Sub

Private Sub btnMemory2_Click()
    frmMain.optionPortrait = PortraitMem2
    frmMain.optionLandscaped = LandscapeMem2
    frmMain.textScale = ScaleMem2
    frmMain.textNumAcross = NumAcrossMem2
    frmMain.textNumDown = NumDownMem2
    frmMain.textSpacingAcross = SpacingAcrossMem2
    frmMain.textSpacingDown = SpacingDownMem2
    frmMain.textStartTop = StartTopMem2
    frmMain.textStartLeft = StartLeftMem2
    frmMain.textOutputSheet = OutputSheetMem2
End Sub

Can anyone advise a better way to do this?

I was thinking about making a class and creating an object for each group of memory settings but even this doesn't seem very efficient to me...

Thanks,

David

 

[rmikesmith]

David,

You don't mention which host application you're working in. If Excel, then a flexible solution would be to store the presets in a worksheet that will subsequently be hidden. Set up columns for each paramter category. Each row will represent all the presets for a given button. You can easily make changes to paramters, if necessary. It would be extensible as well; say, if those 7 presets became 20 (in which case you'd ditch the button approach and use a dropdown, perhaps).

Regards,
Mike

 

[Rofeu]

Hi,

once again, if you're in Excel, I'd use Mike's suggestion for sure.

What I'd also do, is head the columns with the names of the button the values relate to.

You can then just write a single sub that fills the fields:

Private Sub btnMemory2_Click()

Call FillFields(Me.btnmemory2.Name)

End Sub

Sub FillFields(MemID As String)

Dim Col As Integer
Col = Application.WorksheetFunction.Match(MemID, MemValSheet.Rows(1), 0)

With MemValSheet
    frmMain.optionPortrait = .Cells(1, Col).Value
    frmMain.optionLandscaped = .Cells(2, Col).Value
    'etc....
End With

End Sub

You could even relate your formfields to rownumbers in the tag property and loop though the formfields. Something like:

For Each Obj In frmMain.Controls
    With Obj
        .Value = MemValSheet.Cells(.Tag, Col).Value
    End With
Next

Cheers,

Roel

 

[Chance1234]

I would say in a class and a collection

That way, you can create two instances of the same class , loop through and populate your forms.

will also be more memory happy as well




Chance,

F, G + _HH

 

[davidd31415]

Thanks for the replies everyone. I am working in Excel so using the worksheet itself to store the data sounds perfect.

I am still interested in the class/collection method though, mostly because I am trying to learn to think in more of a object-oriented way... I haven't used collections much Chance1234, are you suggesting containing the classes within a collection?

 

[davidd31415]

I keep getting the error "Unable to get the Match property of the WorksheetFunction class" when trying to use WorksheetFunction.Match. I've tried simply using:

Col = WorksheetFunction.Match("Test","1:1",0)

with the same results. I verified Test was in a cell in the first row. I tried changing the cell type to text and re-entering the value- still no luck.

This is with Excel 2000. I'll try with Excel 2003 when I get home. For now I'm going to pass a nubmer to the function- still a great improvement!

 

[rmikesmith]

David,

You need to supply the lookup range as an actual range object, unlike when using this as a true worksheet function; something like the follwing:

Col = WorksheetFunction.Match("Test", Range("A1:A65536"), 0)

Also, I discovered that you can't use construct "1:1" to signify the entire column, for whatever reason, hence my use of A1:A65536.

One other note: You should Declare the Col variable as a Variant data type since the Match function will return an error if the lookup value is not found. You can test for that using

If Not IsError(Col) Then
  ...
End If

Regards,
Mike