Sub or Function?

Now this is a topic you can easily find answers online. So why do we bother mention it? Well, we believe there are a few more things that must be said.

If you read the posts around the subject, you will see that the main difference of a sub from a function is that "functions return things and subs don't". This is in fact true, but it may not even be the principal difference.

In our opinion, the core difference between a function and a sub is that a sub can be executed alone while functions need to be called from other functions or subs.
This means that if you want to execute, for example, a program by pressing a button (assigning a macro to a shape, for example), the code must be inside a sub and not a function (the sub must also be declared as public). If you have a function without parameters and try to execute it (clicking the Run button or pressing F5) the code will not run (it will actually ask you for a sub to run), but a sub will run normally (if a sub has parameters, it can not be executed like that).

On the other hand, functions, when declared as public in a module, can be called in cells like any other Excel default formula (like "average" or "vlookup"). This means that if you have a public function named, say, "multiply3", declared in a module that returns a number multiplied  by 3, you can call it, for example, by typing "=multiply3(A1)" in a cell.
This would pass the A1 cell as a parameter and execute the function, returning the value multiplied by 3 to the cell. This can not be done with subs. This is very useful when we have a big formulas combinations in a cell that has become too hard to understand and we can write a function that does exactly the same as the formulas combination, give it a nice name and use it in the cell instead. This is definitely a best practice and it avoids those unreadable formulas across the worksheets.

This would make us wonder then: what about when I have a problem to which the solution returns nothing and that will be called by another sub? This problem can be solved by both functions or subs. In that case, the programmer is free to choose from them, but if you want our opinion, in that case, we would use functions if the solution required parameters and subs otherwise. The reason for that is that subs with parameters have two syntax for being callled (with or without parenthesis around its parameters) and mixing those two syntax on a code is a little annoying - this is REALLY the only reason.

Bottom line: make sure you really understand the differences between functions and subs before coding. If you are comfortable with the concepts, you will be able to think in more solutions for individual problems such as big formulas combinations in a cell or calling programs from a button.