CS 1425 Lecture 1: VBA- 2D Arrays, Events with Workbook, ActiveX Controls
19 Feb 2019
School
Department
Course
Professor
• 2D Arrays
*Dim Music (1 To 5) As String
Music(1) = "The Beatles"
...
*Matrix: row x column
row column
Dim Films (1 To 5, 1 To 2) As String
Films (1, 1) = "Lord of The Rings"
Films (3,2) = "The Matrix"
'Column 1 is for Fantasy movies
'Column 2 is for Action
'Lord of the Rings in A1 in my Excel Spreadsheet
Films(1,1) = Cells(1,1).value
'No limit to numbers
'Smaller forms, almost instant
*Everything and every value is based on bits
'bits represent the kind of variables you can use and space you can take up
'16 bits = 65,536; 32 bits = 2, 147,483,647
Nested For Loop
For i = 1 To 2
For j = 1 To 5
Films (j, i) = Cells(i, j).Value
next j
Next i
'Nested For loops work by doing inner loop first, then goes to outer loop to repeat
'Inner loop working five times to go through five rows
'Then it goes to outer loop with columns a repeats inner loop two times
_____________________________________________________
• ActiveX
*software framework made by Microsoft
**Object Modeling and Linking Embedding: create easy objects and use drag and drop components
** first made 1996
*Windows, Internet Explorer, JavaBeans, Visual Studio
*Server Pages: integrated with macros
_______________________________________________
Workbook_Open
*Opens and runs things automatically when you open workbook
________________________________________________
List Box
*Developer Tab, click Insert
** ActiveX Controls, click ListBox
• *Add Stuff to List Box*
*Open Visual Basic Editor or right-click to view code
find more resources at oneclass.com
find more resources at oneclass.com
Document Summary
Dim films (1 to 5, 1 to 2) as string. Films (1, 1) = "lord of the rings" "lord of the rings in a1 in my excel spreadsheet. *everything and every value is based on bits. "bits represent the kind of variables you can use and space you can take up. "16 bits = 65,536; 32 bits = 2, 147,483,647. Films (j, i) = cells(i, j). value next j. "nested for loops work by doing inner loop first, then goes to outer loop to repeat. "inner loop working five times to go through five rows. "then it goes to outer loop with columns a repeats inner loop two times. **object modeling and linking embedding: create easy objects and use drag and drop components. *opens and runs things automatically when you open workbook. *open visual basic editor or right-click to view code. *project explorer-> left drop down window (project - vbaproject) **folder (in it sheet1 and name of workbook)
