Смотреть видео онлайн

Excel VBA Basics


- только HD качество  

9:57 - Excel VBA Basics #1 - Getting started with Range object, running and debugging
Загружено 22 октября 2014
Learn some of the basics in Visual Basic for Applications, VBA for Excel. In this video we learn How To:Assign a value to a cell or cell range with VBA's Range object.Get the developer tab in Excel's ribbon. - IMPROVED AUDIONew Contest! Enter to win $20 and my new eBook! >Here is my link to my workbooks, past present and future.In order to easily and automatically get the latest files, get a free Dropbox account, click here:>For ac
9:40 - Excel VBA Basics #2 - Using the Cells object with and without range object, named ranges
Загружено 22 октября 2014
In this video, we learn how to:Use the CELLS objectUse Cells object with Range objectUse the Range object to affect a "named range" -IMPROVED AUDIOInteract and Follow me on:Facebook: https://>Twitter: https:///ExcelVbaIsFunGoogle Plus+: https://plus./112818147990997347920/
11:13 - Excel VBA Basics #3 - Using For and Next with variable, using loops for custom reporting
Загружено 22 октября 2014
In this lesson, we continue our basics series to discover:Using FOR and NEXT to loop a valueWorking with variables further. -IMPROVED AUDIOInteract and Follow me on:Facebook: https://>Twitter: https:///ExcelVbaIsFunGoogle Plus+: https://plus./112818147990997347920/
11:35 - Excel VBA Basics #4 - IF THEN statements within the FOR NEXT loop
Загружено 22 октября 2014
In this lesson, we learn how to:Use IF and THEN to analyze cells or other objects to see whether they meet your criteria. - IMPROVED AUDIO.Interact and Follow me on:Facebook: https://>Twitter: https:///ExcelVbaIsFunGoogle Plus+: https://plus./112818147990997347920/
12:45 - Excel VBA Basics #5 - Toolbars, Messageboxes, Object Browser, Autocomplete and Code Windows
Загружено 22 октября 2014
How to setup and use the Toolbars, Object Browser, using Autocomplete and the Code Window. Also, in this lesson, we learn the basics of making a Message box that pops up!Interact and Follow me on:Facebook: https://>Twitter: https:///ExcelVbaIsFunGoogle Plus+: https://plus./112818147990997347920/
4:29 - Excel VBA Basics #6 - Fun with the Immediate Window
Загружено 22 октября 2014
In this short video, we learn how to view the current value of variables, objects or even make quick changes in the middle of a macro using the Immediate Window. This is extremely useful when debugging (going line by line).Interact and Follow me on:Facebook: https://>Twitter: https:///ExcelVbaIsFunGoogle Plus+: https://plus./112818147990997347920/
8:21 - Excel VBA Basics #7 - Using specific sheets, Hiding, Unhiding and Selecting with VBA
Загружено 22 октября 2014
In this episode, we learn that macros can affect ANY worksheet, regardless of which one you're currently in. We learn how to control and direct the code toward a specific sheet so that our data isn't overwritten on the ACTIVESHEET. By default, excel vba will throw your Range() or Cells() object on the current sheet (Activesheet), but harnessing the power of Thisworkbook.sheets() will prove very effective.We'll even show you how to read and write on a HIDDEN worksheet. We also show how to programmati
10:18 - Excel VBA Basics #8 - Find the LAST ROW or COLUMN dynamically and clearing out your last report
Загружено 22 октября 2014
-- () method,using xlup, xldown, xltoleft, and xltoright.One of the most important lines of code you need to memorize is Cells(rows.count,1).end(xlup).rowCustom Ranges and Getting LastRow to Work with Copy and Paste - Excel VBAhttps://>Getting the Last Row in Excel: The Most Common Mistake! Speed Tiphttps://>Excel VBA Basics #8 - Find the LAST ROW or COLUMN d
8:4 - Excel VBA Basics #9 - Declaring variables with DIM and using SET to abbreviate worksheet name
Загружено 22 октября 2014
In this video, we learn how to tell Excel what type of variable we're using, and then it gives us feedback by allowing autofill dropdowns and giving previews of values.Setting an object, such as a worksheet, to be abbreviated to a shortened name will save you a lot of extra typing also.Interact and Follow me on:Facebook: https://>Twitter: https:///ExcelVbaIsFunGoogle Plus+: https://plus./112818
11:42 - Excel VBA Basics #10 - Looping through a database and analyzing cells based on criteria
Загружено 22 октября 2014
Loop through a sample database instantly to find a custom answer or message to the user!Here is my link to my workbooks, present and future.To sign up for Dropbox free service, to store and share your files in the cloud, click here:>For access to my workbooks, use this:https://>My Video Describing Dropbox links:>Facebook: https://w
8:14 - Excel VBA Basics #11 Create your Own Custom Functions with or without Arguments
Загружено 22 октября 2014
How to make a function using VBA. In this lesson, we make 2 functions from scratch, =Lastrow() and =LastrowC().Lastrow function, when typed into any cell, gives the row number of the bottom most cell in column one.LastrowC function allows the user to click on any cell for its argument and it will find the lastrow in whatever column the selection was in.Interact and Follow me on:Facebook: https://>Twitter: https
6:44 - Excel VBA Basics #12 Custom Functions - Making VLOOKUP Even Easier for Coworkers
Загружено 22 октября 2014
Custom functions! We make 3 Vlookup functions, =Desc(), = Status() and = DAdded. The user just types, for example =Desc(A1) in a cell and it will VLOOKUP the description (which is column 2). or =Status(A1) will give the status (looking up the item number in A1, giving the 4th column vlookup).This is one of a gazillion things you can do with the power of making your own functions using VBA. Please ask questions if you have any! -DanInteract and Follow me on:Facebook: https://
7:25 - Excel VBA Basics #13 Switching Between Workbooks Dynamically, Create New Workbooks
Загружено 22 октября 2014
In this lesson, we learn how to teach our macro which sheet to select and when using the Thisworkbook.Activate AND Workbooks object to select a different sheet (eg Workbooks("Book2").Activate ).We also show you how to make a new workbook on the fly and how to grab the new workbook's name so no matter what it is you can switch back and forth! Check it out!Interact and Follow me on:Facebook: https://>Twitter: https://twi
20:8 - Excel VBA Basics #14 QnA How to Combine Several Workbooks into ONE using VBA
Загружено 22 октября 2014
In this lesson, we take two workbooks and build a macro to 1. Open 2. Loop through all sheets and 3. Copy each sheet to a 3rd workbook to store them all. Check it out!Interact and Follow me on:Facebook: https://>Twitter: https:///ExcelVbaIsFunGoogle Plus+: https://plus./112818147990997347920/
4:56 - Excel VBA Basics #15 How to Use GOTO to Jump Around Your Macro
Загружено 22 октября 2014
In this lesson, we learn to utilize the command GOTO. You can skip entire pages of code by using Goto ____, where the underlined is your label. It's like using a teleport in your macro to zoom around as needed. Just be careful not to make it too confusing where your going with your code.Programmers call it "spaghetti" code when it jumps around too much and is incoherent to read or de-cipher later on. Be sure to label areas and make good notes!Interact and Follow me on:Facebook: https://
7:28 - Excel VBA Basics #16A ERRORS - Custom Code On Error, Using GOTO Labels
Загружено 22 октября 2014
Using "On Error Goto _____", you can define where the macro goes if there's an error in a particular segment of your code! This is easy, come try it!Interact and Follow me on:Facebook: https://>Twitter: https:///ExcelVbaIsFunGoogle Plus+: https://plus./112818147990997347920/
4:49 - Excel VBA Basics #16B ERRORS - Continue your macro Even with Errors Using On Error Resume Next
Загружено 22 октября 2014
Continuing our lessons on ERROR HANDLING, we show how to continue the code, even after an error occurs. Excel can silently ignore any and all errors if you want it to using "On Error Resume Next". This is a two-edged sword, so you gotta know when and how to use it correctly, but very powerful, can be a real time saver.Check it out!Interact and Follow me on:Facebook: https://>Twitter: https:///ExcelVbaIsFun
9:30 - Excel VBA Basics #16C ERRORS - Determine If There Was An Error, Which Type, and Define What To Do
Загружено 22 октября 2014
The title says it all! Come check out how to use the err object and find out how to use Err.Number. This is cool, check it out!
2:7 - Excel VBA Basics #17 To Declare Variable or Not To Declare - How To NOT HAVE TO DIM All Variables
Загружено 22 октября 2014
In this quick tip, we learn how we can disable or inactivate the feature that forces you to declare ALL VARIABLES, which can be a pain in the neck sometimes.Check it out!Interact and Follow me on:Facebook: https://>Twitter: https:///ExcelVbaIsFunGoogle Plus+: https://plus./112818147990997347920/
5:48 - Excel VBA Basics #18 - Change the Font Type, Size etc using VBA!
Загружено 22 октября 2014
In this lesson, we learn about the .FONT properties that can be easily manipulated in your Macro or code. Check it out!
11:9 - Excel VBA Basics #19 Using VLOOKUP in VBA
Загружено 22 октября 2014
Vlookup is pretty easy as a normal function, but is it hard in Visual Basic? No! Check it out!Interact and Follow me on:Facebook: https://>Twitter: https:///ExcelVbaIsFunGoogle Plus+: https://plus./112818147990997347920/
5:1 - Excel VBA Basics #19 Using VLOOKUP in VBA - Alternate Method
Загружено 22 октября 2014
Learn another approach to VBA worksheet functions - insert an actual VLOOKUP function into a Cell instead of the answer.New Contest! Enter to win $20 and my new eBook! >Here is my link to my workbooks, past present and future.In order to easily and automatically get the latest files, get a free Dropbox account, click here:>For access to my workbooks whether or not you're a Dropbox account user, use this:https://
11:27 - Excel VBA Basics #20 Using VLOOKUP in VBA for a Custom Message
Загружено 22 октября 2014
Message pops up saying "Product number LM045 is 'Meatloaf', since 03/25/2011 and is Active" depending on multiple vlookups. Simple!Interact and Follow me on:Facebook: https://>Twitter: https:///ExcelVbaIsFunGoogle Plus+: https://plus./112818147990997347920/
6:57 - Excel VBA Basics #21 Put VLOOKUP info in StatusBar using Application.Statusbar
Загружено 22 октября 2014
Utilize the status bar at the bottom instead of annoying message box popups. This is a great way to utilize screen real estate and still have the info immediately available instantly! Check this one out!Interact and Follow me on:Facebook: https://>Twitter: https:///ExcelVbaIsFunGoogle Plus+: https://plus./112818147990997347920/
1:4 - Excel VBA Basics #21a Restore StatusBar to Normal - Simple!
Загружено 22 октября 2014
Restore the status bar back to normal using VBA in Excel. This is soooo simple, you'll love it.
22:31 - Excel VBA Basics #22 SumIfs Using VBA
Загружено 22 октября 2014
Using an advanced function in VBA Editor. Eg: Sum the total sales for Region: South, Between 1/1/1999 and 12/31/2005. We can trap this in a variable and manipulate it further or just slap in in a cell. The point is, the user can't really mess up a function by typing over it or deleting the cell contents, because with VBA it will just find the answer again, even if deleted or overwritten! Check this out!Interact and Follow me on:Facebook: https://
10:59 - Excel VBA Basics #23 DATEADD - Add or Subtract Minutes, Months, Weekdays, Seconds whatever!!
Загружено 22 октября 2014
This is the super duper cheater way to easily calculate what a date or time might be like when you add or subtract a certain amount of time. You can add 3 months, or 15 quarters, or 7134.5 hours. Here's what you can add or subtract:s Secondsn Minutesh Hoursd Daysw Weekdaysww Weeksm Monthsq Quartersy Days of the yearyyyy Yearsso to add 1234 seconds to the
8:52 - Excel VBA Basics #24 DateDiff in VBA - Difference in Seconds, Hours, Weekdays, Quarters, Months, Etc
Загружено 22 октября 2014
Find the difference between two dates/times. Find out how many hours, seconds, quarters, months, years, weekdays, weeks, days, whatever!! You can calculate using similar descriptions as with DateAdd:s Secondsn Minutesh Hoursd Daysw Weekdaysww Weeksm Monthsq Quartersy Days of the yearyyyy Years
8:3 - Excel VBA Basics #25 Extract the DAY, WEEKDAY, MONTH, YEAR from a date. How to use DateSerial
Загружено 22 октября 2014
These are very similar to worksheet functions that do the same - extract the day of the month (1-31), or the month of a date (eg 2 if the month was February), or get the year of a date.How to use DateSerial is SUPER IMPORTANT so pay attention! Thanks!Interact and Follow me on:Facebook: https://>Twitter: https:///ExcelVbaIsFunGoogle Plus+: https://plus./112818147990997347920/
4:24 - Excel VBA Basics #26 WeekdayName and MonthName in VBA
Загружено 22 октября 2014
Easily get the short name or full name of the weekday or the month. Examples areanswer=WeekdayName("1/1/2013")the value for answer would be "Tuesday"answer=MonthName("2/14/2016")where answer would be "February"
10:3 - Excel VBA Basics #29 How to Use ME in Excel VBA (me dot object variable)
Загружено 22 октября 2014
What's up with people using "me" in Excel VBA? In this tutorial, we show how to utilize this built-in variable for several objects and how much of a time saver it is.Check it out!Interact and Follow me on:Facebook: https://>Twitter: https:///ExcelVbaIsFunGoogle Plus+: https://plus./112818147990997347920/
9:11 - Excel VBA Basics #30 How to Loop Through Each Worksheet in Workbook
Загружено 22 октября 2014
This tutorial shows you how to create a macro that can easily affect each sheet instantly. It cycles through them one at a time, so you can do commands. The basis of this is a simple "For Each" loop:For Each ws in Thisworkbook.Sheets 'your code hereNext wsInteract and Follow me on:Facebook: https://>Twitter: https:///ExcelVbaIsFunGoogle Plus+: https://plus./11281814799099734
14:31 - Excel VBA Basics #31 Send Sheets Straight to Printer using PrintOut Method
Загружено 22 октября 2014
Rather than triggering a print preview, sometimes you'll just want your report or sheet to go straight to the printer. With this trick, you can set it up on a worksheet, userform or in your actual report code, but you can send how many copies of whatever you want straight to the printer without any extra unnecessary clicks. Check it out!!
7:13 - Excel VBA Basics #32 Create Sample Database Almost Instantly
Загружено 22 октября 2014
In this lesson, we show some methods of making "mock" or sample data in a flash. A lot of it can be done using simple vlookups in combination with the Randbetween function.So if the random number was 1, it would find number 1 and vlookup column 2 of that table and grab that result, but if the random number was 2 or 3 or 4 etc, it would grab different items from your small (or large) table or list. This is much better than typing a fake database up yourself.Check it out!Interact and Follow me
12:18 - Excel VBA Basics #33 Importance of Naming Column Variables for Reports
Загружено 22 октября 2014
When you're taking a query or info from one sheet to another, it's good to have your columns numbered into variables so it's easier to make changes.If the date is on column 1 and category is col 2, consider setting up some variables, such as sDate=1sCat=2etc. So when you're using the Cells object, rather than using Cells(1,1) for date and Cells(1,2) for Category, you can use Cells(1,sDate) or Cells(1,sCat). Check out the video for a more in depth explanation!

Интересное в онлайне