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

VBA efficiency question

beacon
100+
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.

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


DonRayner
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
  5.  
  6. Private Sub NextButton_Click()
  7. Me(ctlName) = ChangeDate(0, Me(ctlName))
  8. Me(ctlName).SetFocus
  9. End Sub
  10.  
  11. Private Sub Text0_GotFocus()
  12. ctlName = Me.ActiveControl.Name
  13. Me.PreviousButton.Enabled = True
  14. Me.NextButton.Enabled = True
  15. End Sub
  16.  
  17. Private Sub Text0_LostFocus()
  18. ctlName = ""
  19. Me.PreviousButton.Enabled = False
  20. Me.NextButton.Enabled = False
  21. End Sub
Feb 7 '09 #2

beacon
100+
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

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

NeoPa
Expert Mod 15k+
P: 31,186
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.