37. VBA - Functions and Subroutines (Programming In Microsoft Access 2013) 🎓

[@ProgrammingMadeEZ]

Looking for DAILY news and commentary? Join us on my other channel “Coffee With Steve” for daily videos where we discuss Technology, Software Development, Politics, Culture, and many other things. Coffee With Steve: kzbin.info/door/eXAUvo5xxDY_b-lSknPC1A

[@77goanywhere]

I like the way you start with a method that works then show more efficient ways to doing the same thing. Sometimes I like to start out with something that simply works, then research better ways to do them afterwards, rather than get stalled and taking for ever to complete a project because I don't really understand the more sophisticated way to do it.

[@Mezik88]

If you sold any merch that said "4 divided by Steve" I would totally buy it. Great lesson!

[@zhiyangmao5573]

In fact I got the same problem as bustercrab and I tried to add "Exit function" right after "problem: end select" but it didn't work. Then I checked the comments and found your solution below. Problem solved! Thank you so much!! You are really great!!

[@x21tri5]

+ZHIYANG MAO Yeah, really great!

[@terrytavelli8254]

Hi Steve...Great videos. I see some comments on an issue that I also found. If an error is encountered, the program still runs after the error message is displayed and displays the value of 0 in the result message box. To prevent this I set up a string variable (strCalcError) and set the value to "Error" after the End Select in the Problem: area. Then I changed the Math() subroutine by adding the following: Private Sub Math() If Calculation = 0 And strCalcError = "Error" Then Exit Sub Else MsgBox Calculation End If End Sub This calls calculation first, finds the error and then exits the sub before Calculation is run with the MsgBox. Cheers

[@JRubi-on5up]

Excellent presentation Steve. Thank you, you’re a big help.

[@rashadkahwaji]

Very nice lesson. thank you for your efforts

[@YvesAustin]

Another great video Steve; thanks again. Hey, can you quickly expand on the first two very top rows of your code here: - Option Compare Database - Option Explicit I know what Explicit means but not sure about the first one. Thanks!

[@dianasharon5647]

Everything's clear except for the definition of function vs. subroutine. In your example towards the end, before you introduced function to replace subroutine, you have been using subroutine already to return value. So I'm quite confused on how you're defining subroutine as "does not return value." Please kindly clarify it.

[@jeanrioux8750]

Hi Steve, I enjoy very much your videos. About the issue of the result display of 0 after the error message, I tried the Terry Tavelli's solution and it worked well. However, in monitoring the flow of the program step by step, I found that the Sub Math() had to call the function twice. This is not very important for a short program like this one, but a more effective code would be the following because it calls the function only once : Private Sub Math() Dim dblCalc As Double dblCalc = Calculation If strCalcError = "Error" Then Exit Sub Else MsgBox dblCalc End If End Sub Note : The variable strCalcError must be declared at the very beginning of the code (under Dim Operator) in order to be recognized by both the function and the sub "Math". Also, the Error value is set after the label "Problem:" in the function.

[@twfeuser]

I was just curious why the dbl is only applied for the addition part. Why is the dbl not added to the subtraction, division, and minus parts of the code. These are string values? Thanks.

[@x21tri5]

Love the CONCEPT. Thanks steve! Looking forward for more videos. :-D

[@rohitharidas91]

Watched this lesson thrice to fully take in everything....Great job!!

[@thomaspahel1888]

Steve, I wish to thank you for your excellent tutorials. Although I am using Access 2016, your tutorials carry over quite nicely. I do have one question and one suggestion. The question is in reference to 'On Error' command. Where does the extent of the 'On Error' coverage stops? For example, in python, the 'try:' command is bracketed by the 'except:' command. What brackets the On Error command? Incidentally, VBA and python are very similar. The join, split, error handling, etc. are mirrors of each other. The suggestion is the following. It would be a nice if you put search words for search engines in that area right below the video. The part is that area which says 'SHOW MORE'. With these search words, people can find specific and relevant ideas to solve their problem. For example, this video is about functions and subroutines. But, it also shows a good example of error control so 'On Error' command would have been a good search word. Plus, you gave a very good example of 'cdbl' which is not advertised anywhere. The text 'cdbl' or the text 'an example of using cdbl' could be added plus a link to take you exactly to that part of the video in which you discuss it. For example, if you go to the below youtube link kzbin.info/www/bejne/hF6ri6OOo8-Lg5I then you shall hear the English suites 1 through 6 by Bach. However, if you wish to hear only Suite 3 then there is a place to click in the 'Show More' area to go right to that part of the video. I propose the same for your video. You have a lot more information then just the titles of your videos would suggest. Once again, thank you very, very much!

[@jay55patel]

you are great teacher

[@ProgrammingMadeEZ]

Jay Patel Thank you!

[@mchopra1989]

Truly Professional!

[@ProgrammingMadeEZ]

Thank you Manish! I appreciate the compliment.

[@Naked_Snake]

May be a silly question, but what was the point of having the Function for this specific problem? All I see it do is to change the word from "Msgbox" to "calculation" in the code. What was the practical purpose for this example?

[@yuliyanbadryzlovan]

Dear Steve, I greatly appreciate your immediate response to my earlier question. Sorry I’ve messed it all up in the comments, deleting and adding new posts. Now that I’ve fixed the apostrophes as you pointed out, the code debugs successfully, but it seems to produce no result. . I have a table, “tblDictionary” with the textfields “LemmaDict” and “Meaning”. And I have a form with a textfield “LemmaCorp” and a combo box “LemmaMeaning”. I want this combo box to display only those entries from “Meaning” where “LemmaCorp” and “LemmaDict” are identical. When I run the code below, the drop down lists in the combo box remain empty. What can I be doing wrong? Public Sub cboLemmaMeaning_AfterUpdate() Dim strMeaningSource As String strMeaningSource = "SELECT tblDictionary.Meaning FROM tblDictionary" & _ "WHERE tblDictionary.LemmaDict ='" & Me.txtLemmaCorp.Value & "'" Me.cboLemmaMeaning.RowSource = strMeaningSource Me.cboLemmaMeaning.Requery End Sub

[@ProgrammingMadeEZ]

I am afraid I cannot give you an answer at this point since the code looks fine. It must have something to do with the way you have your form set up or the values you're passing to your query.

[@yuliyanbadryzlovan]

Thank you so much, Steve, I'll keep trying. Thank you for your work!

[@weibinren92]

Hi, could someone tell me why when I have a breack into, my vba doesn't step over the calculation function? It just step over 4 rows like below: Private Sub btnDiv_Click() operator = "/" Math End Sub

[@hhasanat]

Dear Steve, thank you for your really very useful video, I really learnt a lot of them. One inquiry: Can I Use MsgBox Val(Me.txtvalue1) + Val(Me.txtValue2) instead of MsgBox CDbl(Me.txtvalue1) + CDbl(Me.txtValue2) Are they the same or not?

[@ProgrammingMadeEZ]

+Hussein Alhasanat What happens when you try it?

[@bustercrab6136]

Great,in-depth and informative tutorials. I was able to replicate the problem that "darkknight" found. I downloaded the 37.zip and used 4 and steve as values. When running the Math Subroutine Form, No Problem, but when using the Math Function Form, was able to get second message box value "0" for each math function. I think that passing the value to the MsgBox Calculation function somehow proceeds back to Private Sub Math() and executes it. Is there a way of ending a function after error message has been completed or another fix?. Doing the additional Calculation Function may have exasperated this extremely interesting code exercise? Kudos on this course :

[@ProgrammingMadeEZ]

Ahhh, I see. So after the initial error message comes up you get a second message that gives the value 0. This is because there is no return value being set for the Calculation value. Numeric variables must always contain a value and so when they are DIM'd they start out with a value of 0. So when the error routine causes no value to be set for the return value, 0 is displayed instead. The solution is to put an explicit "End" command in the Problem: section to halt the execution of any code beyond the error. So now your Calculation function would look something like this: Private Function Calculation() As Double On Error GoTo Problem Select Case Operator Case Is = "+" Calculation = CDbl(Me.txtValue1) + CDbl(Me.txtValue2) Case Is = "-" Calculation = Me.txtValue1 - Me.txtValue2 Case Is = "*" Calculation = Me.txtValue1 * Me.txtValue2 Case Is = "/" Calculation = Me.txtValue1 / Me.txtValue2 End Select Exit Function Problem: Select Case Err.Number Case Is = 11 MsgBox "Cannot divide by Zero." Case Is = 13 MsgBox "Both values must be numeric." Case Else MsgBox "Could not perform function. Please try again." End Select End End Function Notice the extra "End" just before the end function. An End all by itself like this causes all VBA code to halt execution, including the original subroutine that called the Calculate function.

[@bustercrab6136]

Programming Thanks for the quick turn-around and your explanation will be another tool in my Vba toolbag

[@x21tri5]

+bustercrab Same experience here. :-D

[@jamesconroy382]

Could you not just go "MsgBox Me.txtValue 1 & operator & Me.txtValue2", instead of using a select case for each operator?

[@Mrthedarknight10]

Hello Mr Bishop When I use the function, I still get a result even though the value area is empty. Actually I get a 0 in the message box right after I close the "Cannot perform operation.." message box. Is there any solution to fix this issue? Thank you again for your incredible effort & these great videos

[@ProgrammingMadeEZ]

It is tough to say without seeing your code. I suggest you compare what you have with the code I have available at drive.google.com/folderview?id=0BzRSeswr8T9kZUtXRmJ1VS1yQUU&usp=sharing

[@Mrthedarknight10]

Programming I compared your code with mine & ran it, same result. Let me give you the whole picture: for example when you text "1" in the 1st text area, & you leave the 2nd one empty, & click to any operator, you get a message box "Could not perform function. Please try again." . Once you click on "Ok" another message box shows right away "0"

[@ProgrammingMadeEZ]

Mrthedarknight10 I cannot seem to recreate your problem. I type 1 in the first box and leave the second empty. When I click on any of the operators I just get the message I should which says the operation cannot be performed. That's what it should do. I don't get any subsequent message that says 0.

[@spiderko]

Programming Great tutorial Steve, thank you very much for all work. There is a problem with frmForm (Math Function), as Mrthedarknight10 mentioned, if for example you try to divide by zero or type text string, then error msg appears, which is good, but after it new msgbox shows with result "0". I've checked it with your Service Inc. file. Is there any way to stop after "problem" section to not perform Private Sub Math() section?

[@Anoop08953]

Let me thank you for this video, it was a fine one, i use this method for my db but when it call a function from a subroutine after execution of the fuction it exits the vba with out doing any further in my subroutine. Please give me solution for this strave it will be very helpfull for me

[@Anoop08953]

And i also notice that in this video the end subroutine is still pending to execute after end function

[@516cr]

Thank you so much

[@Phillsen]

Don't you think to use a global variable is a bit dangerous? I would have used a parameter of the math function to make sure it can't hold a wrong value.

[@mikeking683]

Hey, I would like to send you my database I've been working on for a music collection and have you tell me what you think.

[@abumusab2945]

respect

[@ahkowy]

Can't we remove the "Math" subroutine and just replace the call for the "Math" subroutine in each of the arithmetic sub with "MsgBox Calculation"?

[@ProgrammingMadeEZ]

There are literally hundreds of ways you can accomplish most tasks. I am just doing things this way for demonstration purposes.

[@meesam55]

I want to enable enter key sound in ms access please help me anyone

[@rockzfunk1661]

Read a decent book on Access You will save time