VBA efficiency question

[beacon]

Hi everybody,

On most of the forms I create that have date textboxes, I usually add a command for today's date and yesterday's date.

In the past when I've written the code for each of the buttons, I've written an individual private function for each button (I may have between 1-10 today and yesterday buttons on a given form).

I'm curious, is this way efficient or would it be better for me to write one public function in a module and call that function in the OnClick event for each command button and just pass the textbox as a parameter? I was thinking that I would still have to create a different set of command buttons for each textbox, so I wasn't sure which way would be the most efficient.

Thanks...

[DonRayner]

This is what I came up with. You can use one set of buttons to do all the date controls.

Placed in a module.

Public Function ChangeDate(ButtonVal as Boolean, ControlDate as Date) as Date
If ButtonVal Then
    ChangeDate = DateAdd("d",-1,ControlDate)
Else
    ChangeDate = DateAdd("d",1,ControlDate)
End If
End Function

Placed in the Forms Module

Private Sub PreviousButton_Click()
Me(ctlName) = ChangeDate(1, Me(ctlName))
Me(ctlName).SetFocus
End Sub
 
Private Sub NextButton_Click()
Me(ctlName) = ChangeDate(0, Me(ctlName))
Me(ctlName).SetFocus
End Sub
 
Private Sub Text0_GotFocus()
ctlName = Me.ActiveControl.Name
Me.PreviousButton.Enabled = True
Me.NextButton.Enabled = True
End Sub
 
Private Sub Text0_LostFocus()
ctlName = ""
Me.PreviousButton.Enabled = False
Me.NextButton.Enabled = False
End Sub

[beacon]

Hi Don,

I'm guessing from your suggestion that it would be more efficient to use a module and call the function from a private subroutine, as opposed to writing the same code in multiple private subs.

I actually had a function written to do something similar to what you wrote before, although it's not as nice as what you wrote, but was curious if this was the most efficient route to take.

Thanks for your help...

[DonRayner]

Your quite welcome beacon.

[NeoPa]

As far as efficiency goes, calling a public procedure is actually less efficient in terms of code execution (The effect would be entirely negligible though, especially when combined with human interaction).

HOWEVER, it is far, far more efficient as far as development time goes.

I would certainly recommend a public procedure above coding something over and over again.