By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,896 Members | 1,983 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,896 IT Pros & Developers. It's quick & easy.

VBA efficiency question

P: 579
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.

Feb 6 '09 #1
Share this Question
Share on Google+
4 Replies

Expert 100+
P: 489
This is what I came up with. You can use one set of buttons to do all the date controls.

Placed in a module.
Expand|Select|Wrap|Line Numbers
  1. Public Function ChangeDate(ButtonVal as Boolean, ControlDate as Date) as Date
  2. If ButtonVal Then
  3.     ChangeDate = DateAdd("d",-1,ControlDate)
  4. Else
  5.     ChangeDate = DateAdd("d",1,ControlDate)
  6. End If
  7. End Function
Placed in the Forms Module
Expand|Select|Wrap|Line Numbers
  1. Private Sub PreviousButton_Click()
  2. Me(ctlName) = ChangeDate(1, Me(ctlName))
  3. Me(ctlName).SetFocus
  4. End Sub
  6. Private Sub NextButton_Click()
  7. Me(ctlName) = ChangeDate(0, Me(ctlName))
  8. Me(ctlName).SetFocus
  9. End Sub
  11. Private Sub Text0_GotFocus()
  12. ctlName = Me.ActiveControl.Name
  13. Me.PreviousButton.Enabled = True
  14. Me.NextButton.Enabled = True
  15. End Sub
  17. Private Sub Text0_LostFocus()
  18. ctlName = ""
  19. Me.PreviousButton.Enabled = False
  20. Me.NextButton.Enabled = False
  21. End Sub
Feb 7 '09 #2

P: 579
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...
Feb 7 '09 #3

Expert 100+
P: 489
Your quite welcome beacon.
Feb 7 '09 #4

Expert Mod 15k+
P: 31,491
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.
Feb 8 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.