?
Solved

Event    VBA if then  Statement 1: Statement 2: Statement 3: endsub

Posted on 2013-11-11
15
Medium Priority
?
380 Views
Last Modified: 2013-11-17
Hi

I have several keypress events that I wish to process the same way.  The following code works fine.  I have quite a few similiar statements for the keypress event.
For this reason,  I would like the following code to be on one line. When I combine the the two lines, I get a compile error at runtime.

Private Sub fltrColour_KeyPress(KeyAscii As Integer): If KeyAscii = 32 Then fltrColour = "":   SetFilters:  fltrColour.SetFocus
End Sub

Open in new window

What am I missing here?
0
Comment
Question by:peispud
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39639654
it is better for readability to create separate lines for your conditions


Private Sub fltrColour_KeyPress(KeyAscii As Integer)
If KeyAscii = 32 Then fltrColour = "":   SetFilters:  fltrColour.SetFocus
If KeyAscii = 33 Then fltrColour = "":   SetFilters:  fltrColour.SetFocus

End Sub
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39639680
Did you remember to put a :  before the End Sub?  I can't test fully without your SetFilters procedure, but this general syntax works without error for me:

Private Sub fltrColour_KeyPress(KeyAscii As Integer): If KeyAscii = 32 Then fltrColour = "":   SetFilters:  fltrColour.SetFocus: End Sub

Open in new window


However I generally prefer multiple lines, for the same reasons Cap1 posted above.
0
 

Author Comment

by:peispud
ID: 39639683
This is true, but I have quite a few keypress events that behave exactly the same.   It's simply a matter of preference.  Can this be put on one line?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39639691
try terminating each IF conditions with ":"  without the quotes

If KeyAscii = 32 Then fltrColour = "":   SetFilters:  fltrColour.SetFocus: If KeyAscii = 32 Then fltrColour = "":   SetFilters:  fltrColour.SetFocus:
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39639698
I think we cross-posted... try my comment at http:#a39639680.  Cap seems to be suggesting the same thing.  If that doesn't work, post the code you tried.
0
 

Author Comment

by:peispud
ID: 39639723
I tried these 3 tests.  In the first one,  I substituted the call to "Setfilter".
Case 1 & 2 fail.
Case 3 works



    Private Sub fltrColour_KeyPress(KeyAscii As Integer): If KeyAscii = 32 Then fltrColour = "":    fltrColour = "":    fltrColour.SetFocus: End Sub
    Private Sub fltrColour_KeyPress(KeyAscii As Integer): If KeyAscii = 32 Then fltrColour = "":  If KeyAscii = 32 Then SetFilters:     fltrColour.SetFocus: End Sub
    Private Sub fltrColour_KeyPress(KeyAscii As Integer): If KeyAscii = 32 Then fltrColour = "":   SetFilters:   fltrColour.SetFocus
    End Sub

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39639741
so now you need to use the third format

 Private Sub fltrColour_KeyPress(KeyAscii As Integer): If KeyAscii = 32 Then fltrColour = "":   SetFilters:   fltrColour.SetFocus
    End Sub
0
 

Author Comment

by:peispud
ID: 39639772
If I have to use the 3rd format,  then so be it.  But that is two lines.

I would prefer one line.   But I can live with two lines of code.

I'll leave this question open just a bit longer..... just in case.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 39639779
i don't think you can have it in one line...
0
 

Author Comment

by:peispud
ID: 39640059
Thanks everyone for your input.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 1000 total points
ID: 39640489
You closed this one too soon.

When I tested this myself, I was getting no error because I had mistakenly left the Key Preview property set to no, so the code simply wasn't running.

Anyhow, if you are looking to get a block conditional statement into one line, use Select Case instead of If-Then-Else

Private Sub fltrColour_KeyPress(KeyAscii As Integer): Select Case KeyAscii: Case 32: fltrColour = "":   SetFilters:   fltrColour.SetFocus:  End Select: End Sub

Open in new window


We had a question posted some time ago by one of our top Experts as a challenge in unusual programming, which wound up focusing on one-line programming, and some of the code is really amazing:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23579815.html

Use the Request Attention button if you want to reopen this - you might get some input from additional Experts if you give it a little more time.
0
 

Author Closing Comment

by:peispud
ID: 39649460
So,  it can't be done in one line using the if / then statement.
But it can be don in one line using a select statement.

Both answers are correct.  I appreciate the insight.

Thanks all.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39649923
Yeah - sorry you didn't get more of a response.

Definitely agree that this won't work with an If-Then-Else block, but there are a variety of other conditional statements that WILL work and get you exactly the same results.

<<
 I have quite a few keypress events that behave exactly the same.   It's simply a matter of preference.
>>
I'm personally not a fan of one-line programming like this,  but I can easily see your point of view in wanting such similar procedures to be stacked up neatly in your code modules.

Another suggestion - kind of a hybrid approach (untested).  The idea here is to put your commands for Case 32 (or any other keys) into their own functions, which you can also write on single lines -- and call those functions from your one-line subs.  The benefit of that approach might be that however many similar keypress events you may have, you will only have to change the actual commands for specific keys in one place (ie: fnSpaceBar in this code).

So you get a similar overall appearance, but less maintenance if the code for handling the keys needs to be updated.

Private Sub Textbox1_KeyPress(KeyAscii As Integer): Select Case KeyAscii: Case 32: fnSpaceBar("Textbox1"):  End Select: End Sub
Private Sub Textbox2_KeyPress(KeyAscii As Integer): Select Case KeyAscii: Case 32: fnSpaceBar("Textbox2"):  End Select: End Sub
Private Sub fltrColour_KeyPress(KeyAscii As Integer): Select Case KeyAscii: Case 32: fnSpaceBar("fltrColour"):  End Select: End Sub

Function fnSpaceBar(sCtlName as string): Me.controls(sCtlName)  = "":   SetFilters:   Me.controls(sCtlName).SetFocus: End Function 

Open in new window

0
 

Author Comment

by:peispud
ID: 39654480
I have been using the hybrid approach in my coding in the situation that you describe.  

I am well satisfied with the replies including yours.  Thanks.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question