Join Curt Frye for an in-depth discussion in this video Using worksheet functions in a macro, part of Learning VBA in Excel (2010).
Many new Excel programmers spend a lot of unnecessary time and effort…creating subroutines that duplicate the functionality of Excel…worksheet functions.…The good news is that you don't have to.…Excel makes most of the worksheet functions available to you in VBA.…I'll show you, by pressing Alt+F11, and going to Visual Basic Editor.…Let's say that I wanted to create a subroutine or a function that finds a random…value between one and five.…In Excel 2007, there is the new RandBetween function, which allows you to set…the upper and lower limit between which you want to find a random value.…
I have this statement here, which uses the Application.WorksheetFunction object.…I'll start typing the line again, so you can see exactly how it works when you enter it.…So I'll type intWhichPrize.…This is simply for a prize drawing application, Prize =…Application.WorksheetFunction.RandBetween.…Now you'll notice that Rand isn't in this list.…I'll show you how to use it, though a little bit later, but in this case,…
- Working in the Visual Basic Editor
- Adding code to a macro
- Creating, exporting, and deleting code modules
- Declaring and using variables
- Managing variable scope
- Defining arrays
- Managing workbooks and worksheets with VBA
- Repeating tasks with loops
- Debugging VBA code
- Cutting, copying, and pasting cell data
- Running and triggering event procedures
Skill Level Advanced
Excel 2016: Power Query (Get & Transform)with Oz du Soleil1h 49m Intermediate
1. Introducing Visual Basic for Applications (VBA)
2. Defining Variables, Constants, and Calculations
3. Adding Logic to Your VBA Code
4. Debugging Your VBA Code
5. Managing Workbook Elements and Data in VBA
6. Adding Advanced Elements to Your Workbook
7. Using Excel Events in Your VBA Code
8. Putting It All Together
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.