1 / 22

Excel and Visual Basic

Excel and Visual Basic. Outline. Data exchange between Excel and Visual Basic. Programming VB in Excel. Exchange data with .csv file. .csv file is “comma separated value” file. .csv file is plain text file.

jileen-caffrey
Download Presentation

Excel and Visual Basic

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. Excel and Visual Basic

  2. Outline • Data exchange between Excel and Visual Basic. • Programming VB in Excel

  3. Exchange data with .csv file • .csv file is “comma separated value” file. • .csv file is plain text file. • .csv file can only save one worksheet. For multiple worksheets in a workbook, you need to use several .csv files. • You need do some editing to remove titles or labels before you use VB to read it.

  4. Example

  5. Code Private Sub Command1_Click() Dim payment(1 To 50) As Single, commission(1 To 50) As Single Dim index As Integer Open App.Path & "\commission.csv" For Input As #1 index = 1 Do While Not EOF(1) Input #1, payment(index), commission(index) index = index + 1 Loop Close #1 End Sub

  6. Using VB in Excel • Excel uses a special version of Visual Basic, which is called Visual Basic for Application (VBA) • User can use VBA to create customized Excel function (VBA procedures), which can be much more powerful than the build-in Excel functions.

  7. Security issues • VBA procedures in Excel is also called Macros. • Macros are two-edged swords. • Macros viruses are malicious VBA procedures.

  8. Setting the security level

  9. Security Level • High (default) • Medium. We should set the security to this level to run our VBA procedure. • Low (not recommended)

  10. Medium level security • If you set the security level to medium, every time you open a Excel file containing macro, Excel will ask you if you want to enable the macro.

  11. Example

  12. VBA function code • Function Comm(Sales_V  As Single) as SingleIf Sales_V <500 Then Comm=Sales_V*0.03 Elseif Sales_V>=500 and Sales_V<1000 Then Comm=Sales_V*0.06 Elseif Sales_V>=1000 and Sales_V<2000Then Comm=Sales_V*0.09 Elseif Sales_V>=200 and Sales_V<5000 Then Comm=Sales_V*0.12 Elseif Sales_V>=5000  Then Comm=Sales_V*0.15 End If End Function

  13. Edit VBA program in Excel

  14. Insert modules • The VBA procedure should be defined in a module. • If you don‘t have an existing module, create one by using the insert->Module menu.

  15. Write code in visual basic editor

  16. Final result

  17. Passing a range of cells to VBA procedure • The previous example shows how to pass one value to a VBA function. • However, in Excel it is possible to pass several cells to a build-in function. E.g. SUM(a5:a7) • We call a5:a7 is a range. • User defined VBA function can also get a range of cells as input.

  18. Data type: range • VBA defines a specific data type: range • Range is just like a object: it has properties. • Important properties of range • Count : How many cells in the range • Value : Value of the cell (it works when range refers to only one cell)

  19. Accessing individual cells • We use a special form of For…Next loop to access each cell in a range Function meanvalue(InputRange As Range) As Single Dim cl As Range For Each cl In InputRange ‘some code here to get the value ‘cl.value keep the value of the cell Next cl End Function Red word are key word in Visual Basic

  20. Program task • Define a VBA function that calculate the average of a range of cells. • We define the function’s name as meanvalue.

  21. code Function meanvalue(InputRange As Range) As Single Dim cl As Range‘cl is used to get individual cell Dim index As Integer, sum As Long index = 1 ReDim inputarray(1 To InputRange.Count) As Single For Each cl In InputRange inputarray(index) = cl.Value‘save cell value into array index = index + 1 Next cl sum = 0 For i = 1 To InputRange.Count sum = sum + inputarray(i) Next meanvalue = sum / InputRange.Count End Function

  22. Use your defined VBA function

More Related