Join Curt Frye for an in-depth discussion in this video Defining arrays, part of Learning VBA in Excel.
Most of the variables you deal with when you write Excel VBA code will be single…variables, such as item prices, shipping rates and so on;…however, if you must deal with a set of values of the same type, such as a set…of shipping rates, you can do so by creating an array.…In this worksheet, I have a set of six shipping rates, and they are all based on weight.…So a five-pound package will cost $10, and ten will cost you $20 and so on.…Now let's see how I represent those values in an array in Excel VBA.…To move over to the Visual Basic Editor, I'll press Alt+F11.…And here, I have a subroutine that will load values into a…one-dimensional array.…
You define an array like you would any other variable, using a Dim statement and…then the name of the variable.…The difference is that you have the number of values that you want within the…parentheses to the right of the value.…In this case, I have the number 5.…Something to keep in mind though is that in Excel, and the other Microsoft…Office programs when you write in VBA, when you create an array, the program…
- 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.