Excel VBA Introduction Part 9 - Object Variables
HTML-код
- Опубликовано: 15 окт 2024
- If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link www.wiseowl.co... to make a donation. Thanks for watching!
You can buy our Introduction to Excel VBA book here www.lulu.com/s...
By Andrew Gould
www.wiseowl.co.uk - Object variables in VBA allow you to store references to objects in memory. They're slightly more complex to use than basic data-type variables, but well worth the effort and this video explains why! You'll learn how to declare object variables and how to set references to existing objects. The video also shows you how to return references to objects using the methods of other objects with examples including generating new workbooks and worksheets, as well as using the Find method to reference cells.
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!
I found happiness in life through this channel
Andrew Gould , you are a savior, at first I thought learning VBA would be very difficult as I am not a Programing person, but your tutorials encouraged me to learn in......thank you very much
***** Hi, Andrew I must appreciate all your efforts.. It's been really great need you help to understand how can one go better and better from here.. And also and another question where can I be able to find whole list of vba objects and there brief purpose and one example.. Thanks Rahul
@@pprahul2003 Press F2 on your keyboard so as to have access to all Variables in VBA
These are the best VBA tutorials I have found yet.
Thanks Chris, happy to hear you're enjoying them!
Set AndrewGould as Awesome
Run
Owned all problems
Thanks bro
LOL seconded
I searched for many places for vba lesson, nothing even comes close to your videos. Best vba lessons online by far.
He is the legend in vba...
A Recap of Basic Variables: 0:38
Declaring and Setting Object Variables: 1:39
Advantages of Using Object Variables: 5:13
Storing New Objects in Variables: 9:00
Creating and Referencing Objects: 11:57
Finding and Referencing a Range: 14:38
Thanks mate, this should be pinned
Thanks man.
Well what Can I say?You are a Genius .Simple , easy, straight to the point and informative.
Andrew, I am so grateful to you for these lectures. I would appreciate if you can upload a video on how to deal with Excel Tables in VBA.
I have been watching these tutorials and they are just amazing, well done! Very easy to follow.
Best, BY FAR, tutorials on web, well planned and edited, took forever to find one that didn't piss me off instantly, but, I finally found them,, thank you!!!
Great videos! Not only are these videos comprehensive but are also elaborative and in a proper order. Helpig a lot in learnig vba! Thanks a lot! :)
These videos are really helpful! Thank you very much for taking the time to spread some knowledge!
Hi Andrew, some time ago I bought a book 'VBA Basic' however after 3 chapters I stopped learning this, as couldn't really get too much. Your videos are just brilliant and I finished first 9 with you so far and I feel like learnt a lot and started to use some VBA in my work to be more efficient. Thank you so much! The best VBA tutorials I could only imagine!!
Thank you so much, you're amazing..! what i really like about your videos is you're explaining very well and with some examples, and i understand very well as even English is not my first language! please carry on, i cant wait for the next video.
God bless you and your family.
you are a very talented educator that makes learning very easy. thanks so much for your posts.
Great Vid. Consider adding a tip about clearing all variables at once using 'End' Statement
Andrew, thanks a lot. Your videos help me understand the basic to write the complex codes.
Excellent as usual. Going to watch Part 8 and 9 once more before moving on. Thank you!
'Clarity is power'!
Thanks Andrew :)
Really nice videos in this channel. You are one of the best VBA teachers online, keep on the good job. Congrats my friend.
Thank you for posting this great VBA series!
Just finished second round--thanks, thanks!
All Great videos from WiseOwl!
They're helping me a lot.
Thank you very much.
Great tutorials. Thank you Wise Owls!
Hi Andrew, your sequence of the entire video's is so well thought through... I had stopped learning vba but your videos have rekindled my desire to learn again... hats off to you. Your accent is so clear. Thank you.
Tried looking for a video where I could copy a entire existing entire file in a different location but couldn't narrow it down. Could u help.
workbooks.add "Insert file path here"
sheet1.activate
range("a1").currentregion .copy
workbooks.add "Insert the second file path here where ur gonna copying the data "
sheet1.activate
range("a").pastespecial
Great job you have here.
Definitely un excelente help to who wants learn VBA.
Thank you so much!!
I know what I am about to say is out of the Scope of the basics you are teaching in this video... but I feel compelled to comment...
I use Named Ranges in Excel extensively and what I have found is that I can use those Named Ranges in VBA for example:
@6:37 ~ you use:
Set FilmNameCells = Range("B3", Range("B3").End(xlDown)) to set the Range,
however; in a previous video we Named each of the columns so... I've been playing with those Named Ranges and found that we can use:
Set FilmNameCells = Range("Title") or even
Set FilmNameCells = [Title]
Not trying to upstage you in any way as I am learning so much from this series of training videos.
I am bringing this up to ask... do you cover using Named Ranges in VBA in later videos?
Hi there! Don't worry, I don't feel upstaged at all! But I appreciate you asking the question in such a way so as to spare my feelings 😀 We have covered referencing range names in a short section in an earlier video ruclips.net/video/c8reU-H1PKQ/видео.html (this might be the one you're referring to?). There isn't much more to it than that though! I'm a big fan of range names, I teach them on our Excel Advanced and Business Modelling courses and use them extensively myself. Of course, not everyone uses range names but everyone uses cell references so in the VBA videos I use the approach that suits the widest audience.
You can do plenty more with range names in VBA including creating, deleting and changing the cells which they reference, as I'm sure you'll discover 😀
That last example was pretty cool
Andrew, in previous videos when you add the xldown to the range like you do here at 6:30 or so I noticed you used Range("B3", Range("B3").End(xlDown)) where in other videos when making the same reference to the range of cells B3:B15 and beyond your syntax was Range("B3", Range("B2").End(xlDown)) did I miss something in a video does this make a difference. or was this just a typo. thank you for all you have done these videos are very easy and very informing. Cheers Don
Great videos :-) A question; how would you do if you don't just want to find the first occasion of your search word but all of them?
Hi, your tutorials are really amazing. I have one question: could you please tell me if there is a video in which you apply trim function within a range? Thank you in advance!
Hi Andrew awesome video thank you so much for the comprehensive explanations. Quick question if I may can the Sub FindingARange be modified to find all duplicates and return these in another worksheet - or alternatively better yet to remove all duplicates and return a clean range of data into a new worksheet? Much appreciate your help!
Once again great work, helped me out a lot. Just wondering how do you get the macro to move down a cell each time its ran? If I set End(xlDown).Offset(1, 0) I get an error so I have to use (xlUp) instead but that will only suffice for the first run. Any help would be greatly appreciated. Thanks
range("a1").end(xldown).offset(1,0).select
Thank you so much, Andy, for such nice tutorial. also could you please advise how to store the find result in different range instead of using msg box. i have tried but no luck. Please help.
The flaw in the sub FindingARange() is that it finds approx match.
Hence, if you find the movie TED, it gives The Hobbit: An unexpected journey as unexpected has the word ted !!
Please give a solution.
Saw all of your previous videos and they were all awesome and explained in detail.. Thanks... :)
Set c = .Range("A5:A350").Find(KPI, LookIn:=xlValues,lookat:=xlwhole)
Thank you for another great video!
Hi, Just wondering, for the range function. you have type in (cell , cell) rather than (cell : Cell) but it works perfectly.
I used to thought that (cell , cell) represent 2 cells for for ur case B3 and the last cell in column B rather than selecting the entire block from B3 to the last cell. Could you clarify this for me??? Awesome job by the way, it's so systematic to learn it from you!
Your video is really beautiful, I support
Thank you!
So good, wise owl!!!
You're videos are amazing!!! Please carry on with the good work!!! :D :D :D
Thank you sir. Your videos are very helpful.
Amazing tutorials..., just I have a question in the final example, What if the name of the film was duplicated, can I find the both of them?
Thanks, these videos are great!
Just an FYI - When I type in “Ted” in the message box for the “FindARange” sub the result came up as “The Hobbit: An Unexpected Journey”. The “ted” in unexpected is the cause. I guess it looks for the first result to the answer.
Thanks,
Rick May
Your right. You get Just the first option to answers. If u will love continue searching then you require to loop through till the last alternative.
Thankyou and so much respect for your effort Sir.....
I am a little confused by the set filmcell code - to me, that should return the cell reference in which the film was found, yet it is assigning the film name? Can you explain how that is working?
great tutorial! minor point: if you put 'ted' in the input box it will return 'The Hobbit: An UnexpecTED Journey', never Ted. How would you get around that?
Very useful, and thanks for the time doing this vid
Very good. But your speed is a bit fast in videos 8 onwards. The difference between custom made Objects and the Ones built-in is not very clear. Dont the custom Objects have any properties and methods?
May I refer to controls on a User form in this way? I have many scrollBars on my form and I would like to loop through them to set all of their values to 0, once a button is pressed, so.. in the button press method I have:
Dim MyScrollBars(3) As ScrollBar
Set MyScrollBars(1) = ScrollBar1
Set MyScrollBars(2) = ScrollBar2
Set MyScrollBars(3) = ScrollBar3
but I am getting a Type Missmatch error. WHY!? They are obviously the same type.
Hello, thank you for the videos! When I press space after the properties I don't get the atributes shown to me. Any ideas?
Check if u've DIM ur variables
Hi Im facing an error Kindly help : Filmtocell variable not showing any value showing nothing I could i am making mistake ..see the codes..
Dim FilmtoFind As String
Dim Filmcell As Ranges
FilmtoFind = InputBox("Enter the Film Name")
Set Filmcell = Range("B2", Range("B2").End(xlDown)).Find(FilmtoFind)
Local window the Filmcell value refernce varibale value showing blank..
Guys if someone facing similar kind of problem then here is my solution which is different but still find function not working when i packed the variable inside but insted of it i placed I combine find and Input function togerther it works find :
Sub Howtousefind()
Dim Celltofind As Range
Set Celltofind = Range("b2", Range("B2").End(xlDown)).Find(InputBox("Enter the Movie Name"))
MsgBox Celltofind.Value & " was found in " & Celltofind.Address
End Sub
+WiseOwlTutorials how could i make it so that finding the film is not case sensitive? 19:20
really most your videos are interesting and useful. when I practice this video I faced problem and I inter any name in inputbox the letters appear dis understand letters because my language is arabic
Hi Khalid, yes unfortunately the standard inputbox does not support non-Latin characters. You could create a user form with a textbox to get around the issue but that requires more work! You can see our series on user forms here ruclips.net/p/PLNIs-AWhQzckOzn3l2_VWUicXZvywNSI4&feature=shared
I hope it helps!
What’s difference between currentregion and usedrange? Don’t both grab what you need?
This threw me off just a bit. At 8:48, you activated sheet2 and then ran the FilmNameCells font changes. I expected the changes to take place on sheet 2 not back on sheet 1. What did I miss? Thanks.
Hi , Why do you use an object in the very last example to store the range value? Couldn't you do it with a Variable?
Hi there, in the example you mention, the FilmCell variable doesn't store the range value, it stores a reference to the range object whose Value property we want to access. The Find method returns a reference to a range object, not a value. We could have written this without using an object variable but it would be more difficult to deal with the error generated when the film name we're searching for doesn't exist in the list. I hope that helps!
Thanks for all this Great Videos.
But there is something i don't understand in this one.
I played a little bit with your Subroutine "Sub FindingARange ()"
and find out, that the search don't starts from Cell B3 to B15, but from Cell B4 to B15 and only then it is looking in Cell B3.
for example if you are searching for the Word "The"
The MsgBox will give the following aswer "The Dark Knight Rises was found in $B$4"
But the right answer should be "Marvel's The Avengers was found in $B$3"
Why is this so?
And what kind of routine should i use to get the right search order?
Thx for help me out
WiseOwlTutorials awesome. It works. Thanks a lot. You are the best. Greetings from Switzerland
Thank You So Much
Brilliant Tutorials
Thanks you very much.
Can you use the find function to search for an object? Sub user()
Dim var As Range
Set var = Sheets("Sheet2").Range("A1").Value
Sheets(1).Activate
ActiveSheet.Find(What:=var, After:=Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Select
ActiveCell.Offset(0, -1).Value = Sheets(2).Range("B1").Value
Cells(1, 1).Select
End Sub
Thank you!
You're welcome Joshua, thank you for watching!
I know its an exemple but theres is any advantage to use range objet instead of name the range
Range("b3", Range("b3").End(xlDown)).Name = FilmNameCells
thanks again for your videos !!!!
Sir, if We Can Declare
Dim NewSheet As WorkSheet
Set NewSheet = WorkSheets.Add
Then In That Case Why can't We Rename the Sheet Directly like
Set NewSheet=Worksheets.Add.Name = "XXX"
Thank You, Sir
How in the world does it know you mean Sheet1 when you set value with FilmNameCells? There's only a "B3" etc range reference that I can see in the code at one point.
Ahhhhhh! Thanks!
what is the difference between true and false in sheet 2?
thanks again
keep up the good work
THanks for yours video ... there's a little mistake while talking about the current region. The shortcut is ctrl+* not ctrl+a ...
Both CTRL + A and CTRL + * work. As does CTRL + SHIFT + Spacebar
@@WiseOwlTutorials yes those works, but doesn't do the same job. Yes in your exemple, they did the same result. Ctrl+a take all the cells in the sheets, ctrl+* take all the nearby cells in the sheets ...First time I've heard about ctrl+shift+spacebar, ill go check what it does
You can read the description here support.office.com/en-us/article/keyboard-shortcuts-in-excel-1798d9d5-842a-42b8-9c99-9b7213f0040f
The description of CTRL + A and CTRL + SHIFT + Spacebar reads "Select the current region if the worksheet contains data. Press a second time to select the current region and its summary rows. Press a third time to select the entire worksheet."
very good work
can u plz check out1) OtheerEgs() not working
Its Awesome
thank you
Hi Andrew, I have another question for u. I have 200 ppl working under me and u have a database where I store the queue they are in some of which are mgmt, cpd,plb, nsm, adv. The help I wanted was that each time I open the database I want it to list the count if each queue.
Kindly help
thank you :)
9:43 mynewsheet))
I still don’t understand the concept of using the ‘new’ keyword,
Using a New Keyword simply helps you hold in memory a range of objects unlike in basic data type variable where you can set a variable just to a specific unit.
amazing
Dude ! thanks, lol , 4 Videos today :( . I'll just go to sleep Zzzz
👍
A little mistake my dear teacher current region = ctrl + * not ctrl + a
Like seriously ! Please verify again
Oh. Duh! Had I listened for just 11 seconds more I would have gotten the answer. Sorry.
High quality of videos
Thankyou and so much respect for you efforts Sir.....
Thank you!