5 More things I wish I Knew When I Started Using Excel VBA
HTML-код
- Опубликовано: 1 авг 2024
- 👉 Ready to master VBA?
- Check out my full courses: courses.excelmacromastery.com/
- Subscribe to the channel here: bit.ly/36hpTCY
- FREE CHEAT SHEET: Get the free cheat sheet on VBA arrays here: bit.ly/2MXsnz9
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
5 More things I wish I Had Known When I Started Using Excel VBA
Hello and welcome to this Excel Macro Mastery video.
The video "5 thing I wished I knew when I started usinng Excel VBA" was very popular when I released it.
I got many requests to do more. So here it is: 5 More things I wish I Had Known When I Started Using Excel VBA.
Related Articles
VBA Inputbox - A Complete Guide (excelmacromastery.com/vba-inp...)
Using ADO section: (bit.ly/2Sk9Qjs)
Useful VBA Shortcut Keys:
Shift + F2: Get the definition of the item under the cursor.
Ctrl + Shift + F2: Go to the last cursor position.
Ctrl + Space: AutoComplete Word.
Alt + F11: Switch between Excel and the VBA Editor.
Ctrl + R: View the Project Explorer Window.
Ctrl + Shift + 8(or Ctrl + *): Get the current region on a worksheet.
F4: View the Properties Window.
F5: Run the code from the current sub.
F9(or click left margin): Add a breakpoint to pause the code.
Tab: To move lines of code to the right(Indent)
Shift + Tab: To move lines of code to the left(Outdent).
Table of Contents:
00:00 - Introduction
00:28 - Complete Word
02:40 - View the Definition
06:14 - Marker 5
08:04 - Get the range from the user
09:13 - Reading from closed workbooks
10:30 - Marker 3
Most of that was new to me and seems very useful, thanks Paul.
Thanks. Glad you like this video Rafiullah.
WARNING - Watching Paul's videos will cause discontent with perfectly good VBA code. I have rewritten a fairly complicated sheet twice in the last week to take advantage of techniques that were new to me. My new favorite RUclips subscription. Thanks again!
lol - You're welcome Greg. Glad to see you're finding the techniques useful.
Your explanation of VBA code is just outstanding.
Thanks for your comment Rayhan.
I'm mostly self taught and been doing macros in excel for about 8 years... everything in this video was new to me and all your videos have had excellent, new to me, tips and tricks. Thanks for your contributions, they are awesome!
You're welcome David.
Thanks for your videos. Everything here saves tones of time for me everyday..
You have became my quick go to Guy after the video "running the code 1000 times faster". In fact, I optimized my worksheets your way and they run 10000 times faster
That's great to hear Rajasekhar!!
Much more fast using WorksheetFunction.Match instead of "for cells() next" or "Range.find" .
and also ARRAY instead of ranges .
My workbook improoved a lot . The Use of MicroTimer helped me understand the changes , what is fast and what is slow .
Your presentation is typically flawless and very informative. Thanks for taking the time to put these invaluable lessons together.
I just started working with VBA. I am an old school 'send to the compiler' programmer from last century. But I have some stuff I want to do in excel and watching your video's will give me a huge head start on setting up my environment and some of the concepts in this new to me environment. Liked, subscribed and saved...
The ADO thing is going to be my flavour of the week. I had a user lock his workstation yesterday with a shared workbook open. I think I see how to make that a thing of the past. Thanks!
I've been using VBA for years and still find your videos incredibly resourceful. I appreciate your help. Please post any Patreon links so we can donate if you are accepting!
Paul, you are the most advanced user of vba that I have ever learned from, and hands down the best teacher. Thanks for the videos!
Thanks Joe.
Paul, I think we need an ADO/SQL tutorial.
Agreed, would love to see a deeper on dive on that subject.
That's a good idea. I'll put it in my list.
Paul, please do one on ADO 🙏
@@adamploof3528 Fully agree wit you Adam
👍 Superb tips - especially the ADO connection. I'll try this with a speed test comparing opening workbooks then copying sheet data.
The things that you teach are completely new and makes life easy writing code. Thanks Paul. Love it!
Thanks!
Wow! What a great tutorial! I didn't know that Watch Window was editable! I always found it frustrating that for Range objects, the Address property is not present in the Watch Window. I would copy the Range variable to the Immediate Window and query its Address property there but thanks to this video I can save that step and simply add the Address property to the variable in the Watch Window. Neat!
I am familiar with using ADO to query Excel workbooks but I was operating under the assumption that the workbook being queried had to be open on the desktop first. Thanks for helping me understand that it's not necessary to open it. As you mentioned, using ADO to query a workbook on a server is especially useful since it can otherwise take some time to open and download a copy of a workbook from the file server to the local machine.
Some folks think VBA is just a simple scripting language but, as you constantly demonstrate in your videos, it is a very powerful programming platform. Thank you!
wow! you are very proficient sir. thank you so much. its very comprehensive.
Definition and Last Position....F2 and Shift+F2... Amazing
Sir, your tutorial on how to get data from a closed workbook is absolutely wonderful - I THANK YOU!
Ur a true Guru sharing knowledge that may be kept confidential or hidden.
Your videos are like magic spells, it just works so well with so little but correct information
Glad you think so Niyaz!
Thank you very much Paul!!
Copying data from Closed workbook is my thorny issue for a month. This video is very useful.
Glad to help
Thank you, very informative.
Been using VBA for years but you've shown me some great new tricks. Thanks Paul!
Happy to help Martyn.
Really impressed me these techniques!. Thanks Paul
You're welcome Ali
Wow. Great teaching and fantastic information!
Love your work. Really useful
Hi Paul,
Thank you for the following GREAT material. You are the class for your own.
Thanks Maciej
Very very very useful ... Thank you so much for sharing this ....
You are great. I Thank you so much for your helpful videos.
Thanks:-)
Real treasure here! Thank you so much!
Glad you enjoyed it!
Excellent tutorial, thank you for sharing, Cheers
You're welcome Janez
Excellent video, thanks Paul. Love the ADO, that's going to be very useful. How about making a series like this. I have learned many new skills in your first 10 tips.
I have written quite a bit of code and all of the tips in this video and the last are very helpful. Thank you
You're welcome Jonathan.
All of your videos are unique n commendable... I am an expert in VBA but still your videos are life-savers to me...I have No words to appreciate your work...Thanks from depth of my heart.. God bless you... May God fulfill your wishes and your offsprings'...Live long n healthy. Amen
Thanks Abid.
Always enjoyed VBA, mainly in Access, but ADO really appeals. SQL in Excel!
Awesome explanation... Previously i read ur article on the same topic which i got in mail.... But it was hard read.... But ur video explanation is absolutely interesting... Love to see more videos... I guess... U should make this channel BIG now...
All thr topics which u have in website should be made as a video series.. U will have big vba fan following for sure
Thanks for the feedback. Plenty more videos to come.
After viewing numerous videos from Sir Paul, I just have to state that if the high standards of the medical profession were enforced on the IT teaching profession, our technical outcomes would have a much higher success rate. Sir Paul, M.D. is our doctor of VBA. 👍
Thanks Houston.
Really great, thank you, but especially thank you for that last one, which, to be fair, is not really great, but flippin' Awesome!
Thanks Criston. Glad you liked it.
Great stuff. Never thought about using ADO to access closed workbooks. I think it is worth mentioning that adding the reference to the 6.1 library is optional - instead, late binding may be used (Set conn = CreateObject("ADODB.Connection")). There are pros and cons for both options, but I find it sometimes useful in order to mitigate compatibility issues, if it is a spreadsheet that will be run by other users.
Thanks Carsten
Nice video. I like the last part "ADOB". Last week it was the first time that I have used it (used it for a left join).
SQL is a powerrful way of dealing with queries. Definitely worth using.
Brilliant again thank you
Thanks Frik
Whow! Again :-) Thank you a lot!
Thanks Thorsten.
Hi Paul, I've been using the ADO and it works great so far. I've even been able to SELECT individual records from the database by learning SQL and reading them onto my worksheet.
I've just been wondering if there is any way to _(while running the _*_UseADO_*_ macro)_ place the individual row's data that I imported into cells of my choice :-
eg. data from "A2" is read into "A4"
data from "B2" is read into "C3"
data from "C2" is read into "E10" etc.
So basically, while firing the query and reading the data, I want to place the data in each column into cells of my choice.
Hope it's clear enough to understand.
You are awesome..!! I have made code with different method for the same requirement, but with this my code can run a 100 times faster..
Glad you like it.
Another great video 👌🏻
Thanks Graham
Tons of new information even after 4.5 years of vba 🙂 Definition, Parent, this infobox type, and adodb...
Thanks Dániel
What Can I Say..It's WoW!
Very good content! Thank you very much!
I made a fairly large database on Excel for recurring invoicing a couple of years ago... If only I had known this... The ADO thing sounds quite powerful... being able to run SQL queries on a Workbook means that most applications I need for business can be run on Excel... lol
If you find yourself looking for a video topic... an SQL database on Excel would be EPIC
Thanks Nicolas. Glad you like it.
Very useful tips man.... keep up the gud work.
Thanks, will do!
Perfeito ❤
Great video; thanks!
No problem Ventje.
Hi Paul,
I must say excellent contents and great way of explanation skills.... keep sharing knowledge it will definitely help people's like me.
Another thoughts is it possible to creat video on how to download attachments from perticular subject mail or perticular attachment name.
Also SQL videos are most welcome.
Sanket
Amazing
Good one... Thanks for the video
You're welcome.
You are amazing!
Thanks
Hello, for the ADO, I can't get the part of the code where it said "Dim conn As New ADODB.Connection" to work. Would appreciate your answer to this.
Very useful
Thanks Paul
very useful!!!!
Thanks Steven.
I have found these articles very interesting, thanks Paul. One question on the 5th item which was "Reading from closed workbook". When I try the exact example I get a Compile Error on the line "shResult.Cells.ClearContents" saying Variable Not Defined". Any idea why I get this error.
I appreciate all what you're doing . . . hope we can do something in return! Thank you very much!
Any time!
More great stuff, thanks Paul. While I was aware of ADO, I haven't worked with it to date. It will definitely solve some challenges I have been having. Rather than "copy/paste" the query results to another spreadsheet, could it be put directly into an array?
Recordset GetRows.
arr = rs.GetRows
thank you for very useful lesson
Thanks Paul , awesome
I wonder if you have time to make a video about difference between used range and current region , Thanks.
Used range is the range of all used cells on the worksheet. From the first used cell to the last used.
CurrentRegion is all the adjacent cells with data. The region ends at a blank row or column.
@@Excelmacromastery Thanks Paul
Can we create hyperlink which redirect to a specific cell of another csv file? If yes please share ur inputs
Thank you for this video. I am interested in working with closed excel files.
I followed your steps for the ADO and got Run-Time error '-2147467259 (8000040005)' Failure creating file. I looked online and says that I need to unprotect the sheet but I never protected the "5 more things Data.xlsx" file. I already checked the MS ActiveX Data Object 6.1 Library. The macro file and the data file are in the same folder. Do you have any suggestions?
is using ADODB faster than "Application.Workbooks.Open" when opening files? Or is it about the same?
Thanks Paul for great video!
Is it possiable to change the extention of excel files, on a folder, from .xls to .xlsx, without haveing to open each files on the code?
You can use the "Name" and "Dir" functions.
Name "C:\temp\data.xls" As "C:\temp\data\xlsx". Use Dir to get all the files in a folder.
@@Excelmacromastery
Thanks Paul!👍
Have just checked it and you cannot change the extension only, as excel popup massage that the file is corrupted. probebely the file must be open and save with the new extention.
The parent name property we can apply in immediate Window also right?
Yes.
That ADO code was such a powerful piece of information and explained very succinctly. I have one burning question though that I'd be grateful to you if you could address or answer: _Is there a way to write to a closed file using ADO? How would that code be?_
Same code, but just use an "Insert into" SQL query instead of the Select statement. I use it all the time. The only piece of advice I'd give is to ensure that you always set up your data in a single sheet (try to start in A1 with your headers) and make sure it is laid out in columnar format. You're effectively turning a spreadsheet into a DB, so it is very powerful. You may also notice that when you do an Insert into on a sheet with no existing lines of data that you sometimes get data type errors as Excel doesn't know what type the data in the column should be (dates etc might cause issue). I believe it takes the type from the data in the first row of your data table (for example, if you have a cell in the first row below the header that is a date, Excel will expect you to be inserting a date). Hope this makes sense!
Thanks for the useful advice Rico
@@ricos1497 Yeah, thanks!!
Hi Paul.. another excellent video. Thanks a million for these gold tips and tricks. Question on ADO and SQL: Is it possible to design an ADO connection and SQL query that returns to a target worksheet whatever is in a defined range of the closed source workbook/worksheet? So, let's say I want to copy everything contained in the source Workbook, Sheet1, Range A1:Z100 and that range is not set up as a data table or database, it just contains text and numbers in various cells within the range and some cells are even blank. What I am after is something similar to actually opening the source and then copying and pasting whatever is the contents of A1:Z100 to the target and then closing the source. Right now, I do this with code that allows me to pick the source file with Application.GetOpenFilename and then code that does the copy from source, paste to target, close the source. If I could do the same, but skip the open/close of the source file, that would be brilliant! Any thoughts? Thanks again and Thumbs up!!
ADO expects records to be in database form. If not then it will complain. The easiest thing to do is run a test query and see what happens.
@@Excelmacromastery "Database form" = Excel table with no line gaps or column gaps?
do you need to have data in a table format in order to access it via ADODB.connection?
Nope, not necessary
Amazing Paul - out of interest you do any vba classes or course in Ireland?
Thanks Brian. I don't do any live VBA classes at the moment. I may do some in the future.
@@Excelmacromastery cheers Paul. It's really hard to find any comprehensive class based courses on vba
Hey Paul, The below line is giving me an error while trying to read from closed workbook. Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data source=" & FileName & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes;"";"
Error is "Cannot update. Database or Object is read-only".
Try to use following as connection string:
Dim ConnStr as String
ConnStr = "DSN=Excel Files;DBQ=" & ActiveWorkbook.FullName
I implicitly assumed that the 'proper database' which ADODB understands is in your open workbook. If it is not the case, you just need to replace "ActiveWorkbook.FullName" part with the proper fullname of the workbook. Someting like:
C:\Users\w8\Desktop\The Work\Excel_Query_Workbook.xlsm
As always your videos are AMAZING! I have two questions on this video if that's ok
Q1) At time 6:15 this line of code confused me:
shorders.range("h1").currentregion.offset(1).clearcontents
What does the offset(1) do in relation to the currentregion?
Q2) What would be the difference between rng and rng2 here:
dim rng as range
dim rng2 as range
rng=thisworkbook.sheets("A1").currentregion
rng=thisworkbook.sheets("A1").currentregion.value
I'd really appreciate any help. Thank you!
Glad you like the videos.
Q1)
another way to write it would be
dim rg as Range
Set rg= shorders.range("h1").currentregion ' Get the current region range
Set rg = rg.Offset(1) ' Move the range down one row to avoid deleting the header.
Q2)
CurrentRegion returns a range
currentRegion.Value returns the array of values in the range
-Paul
@@Excelmacromastery wow that was lightning fast! Your are the MAN! Where were you when I started learning VBA???
I dearly would love to use these techniques, but I don't know which version of VBA for Excel Paul is using. I know that it's not the on I use: some of the code just doesn't work with my version. Knowing which version would really help, and, IMHO, is a necessary part of a tutorial.
Hi Anthony
This code works with all versions of VBA from 2007 onwards. If something is not working then it has nothing to do with the version.
-Paul
ADO sounds really useful and would probably speed up my code 100 times. Unfortunately I have to code VBA for Mac users and ADO is not supported on Excel VBA for Mac. The culprit is Active-X...
Have you tried using ODBC instead? Think it is supported in Excel 2016 for Mac, but I don't have a Mac so have never tested!
Unfortunately some of these useful libraries are only available on Windows.
@@Excelmacromastery This is painfully true. While 99% of my organization is on Windows... there of course a few MAC users which make my VBA life a nightmare... even more painful is they are high level execs. =/
Hi.
When you someone use the macro, he could have a problem by not having the dll i suppose ? or not exactly the last one.
However it a nice trick to read data from workbook.
I have already used it in a C# code, but i have never thank to use it in vba itself.
Which dll?
We need odbc dll to read data from a closed wb.
👌🏻
ADO is what I need mostly
ADO is faster or advance filter is faster?
Advanced Filter is faster if the data is in the current workbook.
ADO doesn't require a workbook to be open so in this scenario ADO will more than likely be quicker.
Right away I searched if u have made any course on udemy.... Sadly there isn't
www.udemy.com/course/excel-vba-how-to-write-like-a-professional/
How to get vba handy book sir
You can purchae the Excel VBA Handbook Course from here: www.theexcelvbahandbook.com/
Hi
Can we block vba code @ particular date
Example code should not work after one month
Yes. Just use an If statement. However it is very is to circumvent.
Excel Macro Mastery
Thanks for u r reply. Can I have one example
@@srisureshm1476 You're very welcome.
If inputBoxDate > your Cutoff Date then
Msgbox ("Please select a date less than (whatever).")
Exit sub
End if
@@Excelmacromastery Very easy? (Not very is.)
M
5 More things I wish I *HAD KNOWN*... Just helping out.
:) I know but "Knew" is a better title. I use "Had Known" throughout the video.
@@Excelmacromastery Hmmm. That is the same reasoning that let Ron Howard commit that misquote crime on the line of the century "Houston, we've had a problem". OK, yours isn't exactly heinous, but it's there.