1 / 14

Overview

Overview. Introduction to VBA The Visual Basic Editor (VBE) First Program Getting Started with VBA Subroutines Declaring variables Input boxes and Message boxes With construction. Introduction to the VBA.

erica-hardin
Download Presentation

Overview

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Overview • Introduction to VBA • The Visual Basic Editor (VBE) • First Program • Getting Started with VBA • Subroutines • Declaring variables • Input boxes and Message boxes • With construction

  2. Introduction to the VBA • VBA, Visual Basic for Applications, is an implementation of Microsoft's Visual Basic, an event driven programming language and associated integrated development environment (IDE) which is built into most Microsoft Office applications . • However, VBA and VB are not the same. • Similar in programming elements • VBA is the language to manipulate Excel (and other applications).

  3. Visual Basic Editor • Visual Basic Editor (VBE)is the Integrated Development Environment (IDE) for programming with VBA. • It can be accessed by pressing [Alt] + [F11] • Code window • Project explorer, Properties window, (just like IDE for VB.Net)

  4. VBE

  5. First Program • Open “FirstProgram.xls” downloaded from Web site. • Press [Alt] + [F11]

  6. FirstProgram (cont.) • Insert a module to the project • Start typing Sub CountHighSalesand hit [Enter] • You will see a Subroutine block is inserted automatically.

  7. FirstProgram (cont.) • Type the rest of the syntax in the module: Dim i As Integer Dim j As Integer Dim numberHigh As Integer Dim salesCutoff As Currency salesCutoff = InputBox("What sales value do you want to check for?") For j = 1 To 6 numberHigh = 0 For i = 1 To 36 If Range("SalesRange").Cells(i, j) >= salesCutoff Then _ numberHigh = numberHigh + 1 Next i MsgBox "For region " & j & ", sales were above " & _ Format(salesCutoff, "$0,000") & " on " & numberHigh & " of the 36 months." Next j

  8. FirstProgram (cont.) • Run the program from the VBE • Run -> Run Sub/USerForm • F5 • or • Type in a number in the InputBox, such as 150000.

  9. FirstProgram (cont.) • Run the program with a button • Right click tool bar and choose “Form” tool bar • Drag button form in somewhere on the right side of the data • Choose the macro you just created • Change the name as appropriate

  10. Subroutines Sub CountHighSales() End Sub

  11. Declaring variables Dim i As Integer Dim j As Integer Dim numberHigh As Integer Dim salesCutoff As Currency • Option Explicit – need to declare variables before you can use it

  12. Input boxes and Message boxes • InputBox(“prompt”, “title”) – returns user’s input as data • MsgBox “Prompt”, type, “title” • vbInformation – information display • vbYesNo – display information and Yes and No buttons; returns vbYes or vbNo • vbExplanation, etc. • Require parentheses if the box captures the result (input or yes/no answer)

  13. With construction • Properties are described top down, separated by “.” (period) e.g., Workboolks(“Sales”).Woksheets(“March”).Range(“A1”).Value Workboolks(“Sales”).Woksheets(“March”).Range(“A1”).Font.Name Workboolks(“Sales”).Woksheets(“March”).Range(“A1”).Font.Size • Using “With” key word, it may be structured without repeating the same. With Workboolks(“Sales”).Woksheets(“March”).Range(“A1”) .Value = “Sales for march” With .Font .Name = “Times New Roman” .Size = 14

  14. Exercise • The file ExamScores.xls contains scores for an exam in the range A1:A100. • Write a program that reports the average, standard deviation, minimum, and maximum of the scores in a message box. • Range is set in a module already. • Use Excel’s functions (with Application.WorksheetFunction. )

More Related