How to Return from a Function in 2 Minutes

Рет қаралды 16,798

Күн бұрын

How to Return from a Function in 2 Minutes
In this video I cover how to return a value, object or array from a function in VBA.
Returning these three items have slight differences so it is important to understand how each works.
#vbafunction #vbareturn #vbareturncollection #vbareturnvalue
SUBSCRIBE TO THE CHANNEL: bit.ly/36hpTCY
DOWNLOAD THE SOURCE CODE FOR THIS VIDEO: bit.ly/3kq7pZ6
Related Training
The Excel VBA Handbook Course(TheExcelVBAHandbook.com)
Webinar Archives - 60+ Hours of VBA training(excelmacromastery.com/excel-v...)
Free Excel VBA Resources
Excel VBA Articles (excelmacromastery.com/vba-art...)
Useful VBA Shortcut Keys
========================
Debugging:
Compile the code: Alt + D + L OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)
Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R
Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard)

Пікірлер: 25

@grzegorz2852 2 жыл бұрын

Paul, another great video. SUper helpful. When my professor asked me to write an essay about 'who do you want to be in the future?'. I wrote about u.

@iamjojo999 Жыл бұрын

Although clip is short,I see a lot of useful concept. Thanks Paul. One little suggestion, it would be better if you can show how much speed difference between two syntax

@NAEEM_MALIK 2 жыл бұрын

I was searching about that, this is amazing 😂

@jaimesastre6393 2 жыл бұрын

Very interesting the set and get différence with number or address.

@darray7472 2 жыл бұрын

Paul, love your channel! Thank you for all you do. This video was short, but packed full of useful information about functions, collections, and arrays. The style was good, but maybe make it a little longer and show your PrintCollection(coll) and PrintArray(arr) subs?

@Excelmacromastery 2 жыл бұрын

Thanks for the feedback.

@Luth1us 2 жыл бұрын

I would suggest you an approach considering those "best practices " Dos and Don'ts. In a practical way Declare or Not declare will not make a big difference regarding performance sake. Its more related to keep organised the code. The VBE was developed considering the absence of declaration. Would be great if you explore situations where the declarations really matter. Thank you for your videos.

@Excelmacromastery 2 жыл бұрын

Thanks for your suggestion Juliano. I have an article here on the Dim statement(excelmacromastery.com/vba-dim/). I might do a video on this in the future.

@Pedritox0953 2 жыл бұрын

Very interesting when you have a bulk of variables and want to alter them with external procedures

@Excelmacromastery 2 жыл бұрын

I've gone for a slightly different style in this video. Let me know if you think I should do more like this. Make sure to download the source code from the description below 👇

@GkhnA 2 жыл бұрын

Hey Paul, I don't mind the shorter videos, but (assuming there will be more of them) the previous, important ones will be lost among them. Maybe another channel for these and then one video here that will consolidate some short ones?

@Excelmacromastery 2 жыл бұрын

@@GkhnA Thanks for the feedback😀

@Rasenmaehermann89 Жыл бұрын

As a VBA beginner, I found it stressful to catch all the info at the first time watching.(listening to you and thinking through the code presented) it's not an instagram story - it's supposed to be an educational video. 😅 Though, I liked your other video "How to get the Last Row in VBA". Thank you!

@NaranRet 2 жыл бұрын

Hi Paul, Amazing videos, super clear and to the point. However I have to admit that I miss a little more explanation in last case (collections) for example, what is the point in pasing a collection than later is already wiped out (Function GetCollection (coll as collection) : set coll as new collection ...) and then use it in a parameterless call ... ( set coll = GetCollection [ ?] ) . I am little confused... moreover, isn't this also a VBE Syntax error because parameter is not set as optional?) Thanks in advance Paul!

@Excelmacromastery 2 жыл бұрын

They are not matching examples. The first shows passing an empty collection and returning a new one by using the parameter. The second(the call to GetCollection) shows the recommended way by returning the collection.

@mrsachu88 2 жыл бұрын

Hi. I want to learn VBA. Nice playlists. But when I see the videos I am confused where to start from,likewise which playlist I should do first. Your guidance will be appreciated

@homejonny9326 2 жыл бұрын

Start by knowing a lot of Excel, without VBA. Cell adress, named ranges, relative and absolute reference, formulas, funcions, charts, graphiscs, excel tables, pivot tables.... Then, go to VBA. I recomment Excel Bible 2019 book.

@vbaclasses3553 2 жыл бұрын

@@homejonny9326 solid suggestion.

@pbs36 2 жыл бұрын

I would just add that we can return values from a sub via ByRef parameters, which should be clearly named to show they're output parameters.

@Excelmacromastery 2 жыл бұрын

I covered that towards the end😀

@logic3686 2 жыл бұрын

I often times use subs instead of functions. Passing the array to the called sub program, having the subprogram update the array, and then when the sub exits the array is updated. Is this a bad practice and why?

@Excelmacromastery 2 жыл бұрын

As I show in the video it is better to use arrays this way as otherwise vba creates a copy which is not efficient.

@logic3686 2 жыл бұрын

@@Excelmacromastery Thanks, I review this. You are a Wiz!

@hariadi9994 Жыл бұрын

you are really master of vba,,, but sadly you speak too fast,,,,,

@thomasfergusen5144 2 жыл бұрын

Hello, i have a question: "thisworkbook.save" code takes alot of time (even i did "Application.CalculateBeforeSave = False" and "Application.Calculation = xlCalculationManual") so i decided to use that code only in "Private Sub Workbook_BeforeClose(Cancel As Boolean)" subroutine. But the problem is when electric is off or somehow programme get a runtime error vba does not read the code. ( i dont want to get recovery offering because of users not familiar that) so what to do? Thanks!