Purpose of using sub routines over functions

Question

I've been working with Access for a while now, and although I understand the obvious benefit of a Function over a Sub, been that it can return values as a result, I'm not sure as to why I should use a Sub over a Function. After all unless I'm mistaken; Functions can do everything Subs can do?

Note: I'm fully aware of how to use both Sub's and Function's so not looking for an explanation of how they work.

Banks

A call of a subroutine is a statement; a call of a function is an expression. Subroutines are for situations when you need to do something without producing a return value. – Aileenailene

All, thankyou for the responses, I appreciate the points made, however what I'm really trying to get to is, if I need to run a routine that does not return a value. Will a sub bring any benefits over a function, @TheNewOne has highlighted sub execution times may be better but it doesn't read as a proven conclusion? – Banks

I found a discussion on bytes.com which may be of interest. Not sure whether you will judge the (very slight) performance improvement with a Sub to be a proven conclusion. Personally, I use a simple criterion: the procedure will be a Sub unless I need it to return a value with a function's AS datatype assignment. bytes.com/topic/access/answers/… – Magalymagan

@Magalymagan I use the same criterion for selecting a Sub or a Function, I guess I was trying to get my head round why Microsoft gave us both to choose from as Sub doesn't seem essential. I'm not sure how old each method is, perhaps in the beginning there were only Sub's, and then they were left in for backwards compatibility. Either way the speed comparison (however slight) has been highlighted a couple of times so I'm going to take that as an answer. – Banks

I know that this thread is ancient. Nevertheless I'd like to share with you a significant difference that has cost me a lot of time and some hairs. I had programmed a recursion using code that had already worked in another macro. However, for the recursion function I used a sub instead of a function. After a lengthy debugging session I found out that the recursion was aborted in the middle (jumping back directly to the main sub without any error). When I changed the sub to a function, it worked all of a sudden... So there seems to be a difference in the internal organization. – Azrael

Answer 1

The main difference is not only the return value, it seems that subs are faster than functions (at least in .net) because the MSIL code of subs is much shorter when no value is returned. so overall subs are faster when no value is returned. oh i've just found a great source for it (talks about .net), maybe you would like to read further about it- Functions vs. Subroutines

Payable

As amazing as it is that the only difference would be a slight speed increase for routine's not returning values, that does appear to be the only answer, cheers. – Banks

@MattDonnan, while this answer is interesting, it is not relevant to your question because VBA is a completely different environment that has nothing to do with the .NET framework. This is a bit like making assumptions about the performance of a golf cart by examining the performance of a sports car. There are some similarities, but the differences are far more significant. – Alcinia

Hi there, of course faster is not by much and i found it correct also for vba and at 10,000,000 calls subs are faster by 1 second. cheers. – Payable

@Alcinia I'm not sure that's completely fair to TheNewOne, this answer plants the right idea; and with minimal searching afterwards will turn up similar comments for the VBA environment. – Banks

@MattDonnan fair enough, but the answer is much more helpful to the casual reader now that TheNewOne has noted that he tested the idea with VBA. The answer would be even more helpful if it described the performance experiment so others could reproduce it. (I have to think, though, that this performance gain is so slight that it would have a significant impact on perhaps 0.01% of programs in the world, and that anyone writing such a program should probably be writing native code rather than .NET, much less VBA.) – Alcinia

Answer 2

In terms of performance, this would not be any significant issue here.

The main difference is that user defined function can be used in expression in your code, where as a sub cannot.

This is really a HUGE Mount Everest of a difference here.

This difference is not really limited to Access, but tends to applies to every programing language and system I can think of that supports the creating of user defined functions.

The key advantage of using defined function are MANY but the most basic issue is that such function can be used in EXPRESSIONS.

For example, in an on click setting for a button on a form, you can generally have a single VBA [Event Code] routine attached to that button.

However you can ALSO place an expression in the property sheet like this:

=MyUserFunction()

The above is a handy tip, since then you can highlight 10 controls on a form, and type in the above expression and you just assigned the above function to those 10 buttons. You cannot do the above with a sub.

Another significant difference is you can use a function as a data source (expression) for a text box on a form or report (again you cannot do this with a sub).

Another significant difference is you can utilize these functions in SQL. This is a truly fantastic ability as then you can have code "run" for each row of a query. And this means you can extend the ability and functionally of SQL.

And you can even use this idea to display a VBA variable in a sql query as you simply build a public function that returns the VBA variable and this can be used in a query – you cannot however use VBA variables in a query!

And this extending of SQL opens up endless ideas:

So I can build a public function called ToMorrow()

Public Function Tomorrow() as date

   Tomorrow() = date() + 1

End Function.

Now in the query builder, I can go:

Select FirstName, lastName, Tomorrow() as NextDay from tblCustomers

And you can even make custom conversions such as:

Select FirstName, LastName, Celsius([DailyGreenHouseTemp]) from tblGreenHouse.

The above Daily temperature reading could in in Fahrenheit and you simply have to define a public function called Celsius like this:

Public Function Celsius(Temperature As Variant) As Variant

   Celsius = (Temperature * 1.8) + 32

End Function

Now while the above function is simple, it could do complex record set processing a complex algorithm to determine the moisture above a flower pot based on temperature and humidity.

So once we define such a public function, then the key concept is such a function can be used not only in VBA code as an expression, but ALSO can be used amazing enough this ability includes SQL.

So even in code, you can go:

If MyCustomfucntion(SomeVar) = lngTestValue then

Again in the above, you cannot use a sub in VBA expressions.

And even more interesting is when using custom XML for ribbons in Access, then if you use a function() expression for the "on action" attribute then you can avoid the need for ribbon call backs. Even better is the ribbon will call those functions() in the current form, not a public code module like you MUST do with ribbon call backs.

I could probably type on for another 10+ pages as to the difference, but I think that would start to be redundant and I don't want to appear condensing in any way here.

So the basic difference between a sub and function in VBA or in fact in most programming languages is quite much the same.

And the benefits of using a function in Access or just about any programing language are also much the same. For example I can define a user defined function in t-sql (scalar) – and again you then are free to use that t-sql function in any of your t-sql code or even quires that you create and use for sql server.

So this is basic and simple difference between a sub and a function, and I dare say those who have written computer code will in just about any programing language will instantly realize the above significant and useful differences between a subroutine and a function.

Grigg

Thanks Albert, I really do appreciate the amount of time you have taken to further explain the differences. It may be down to the phrasing of my question (and I apologise if it is) but I'm aware of the above and am experienced in this area (although I know this serves as useful information for whoever may pick up this thread) I know how great functions are and the posibilities they enable, all I was really trying to arrive at is why would the "Sub" ever be a better choice to take? – Banks

@MattDonnan, as I mentioned, Sub is the only choice for an event. – Bespeak

@Remou I see what you mean, if I use any of the built in Events it will insert the code as a Sub, however this is the built in behaviour of Access, presumably MS could have set Events to auto generate functions instead if they wished. Sadly the question I've asked isn't going to be of any great value to anyone, I just wondered what the purpose of a Sub's existence is, why are they not all just functions? – Banks

You CAN place =functionname() in the property sheet setting and thus event code can call a function() directly. A sub is obviously a better choice when you don't want to return a value. Even when you ignore the return value of a function() (eg: as a VBA statement) then you are ignoring the return value but that return value is still being placed on the program stack. Functions always return a value even if you don't use it. Such a choice is more really based on self-documenting code showing the developers intention but it begs why force the system to return a value when you don't need one? – Grigg

Answer 3

Yes, a Function is just a Sub that returns a value.

Lovage

Answer 4

I'm not absolutely sure, however, I think subs are faster than functions because the variables of a subroutine are defined upon creation of the subroutine and are accessed by referencing the memory location. Functions must allocate memory space every time they are accessed.

Subroutines modify the variables in the calling code and functions leave them intact. So a subroutine can provide several modified pieces of information to the calling code (as many changes as there are variables, including arrays) but a function can only provide one answer at a time for the values that are passed to it. Because of this difference, if it is important that a variable in a subroutine does not change its value, one must assign the value to a temporary variable defined within the subroutine itself.

Monoicous

Answer 5

FWIW (my theory ;) -

Lets think of the real world to understand this.

Lets say you want to get something done. There are (at least) 2 ways of doing this.

First way, send out requests for information to helpers and they will return with the information for you. so you remain in control ie all info is flowing back to you and you are deciding what to do next if any. this is more of the centrally controlled environment. this is the essence of 'function' in vba

Second way, divide up work into separate tasks and assign responsibility to your helpers to finish the task for you ie actual work is performed here by helpers in contrast to just gathering info. This is the essence of 'sub' in vba.

So think of what to do if code breaks. with function calls, you concentrate on the central command to look for reason of failure. With sub calls, you have to run into each sub's work and find out what they did wrong.

Of course, you can screw up the purpose and have functions do work and subs just get info but that would just be really confusing when things break! Oh but you cant do that, read this link - http://www.cpearson.com/excel/differen.htm, which states that Excel forbids functions changing cell values and subs being called from cells.

Saunders

you appear to be one of those who take fake news for real news... pls stop throwing around links and better just test what you say before saying it. Please. Sub a() Dim ret ret = f() End Sub Function f() Me.Cells(1, 1) = "f" Range("A3").Value = 123 End Function Executing sub a() makes function change cell values--there! – Stipple

Answer 6

You will note that events are always subs, never functions. However, in MS Access, it can be useful to create functions when you wish to use them as the property of an event:

On Close: = MyCloseFunction()

Subs can return a value ByRef.

Bespeak

Answer 7

I found one other difference, at least on Excel but likely other Office apps. If you want to customize the ribbon by adding a button to launch a VB program, when you choose Macros in the "Choose commands from" dropdown menu, it lists any Subs in your code but not Functions. Note that a Private Sub will also be hidden from the customize ribbon selection, as will a Public Function.

So to summarize, these will be available to add as buttons on the ribbon: Sub, Public Sub

And these will not be available to add: Function, Public Function, Private Function, Private Sub

Steatite