ExcelandVisualBasic-MichiganStateUniversity.pptVIP

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. .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. Example 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 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. Security issues VBA procedures in Excel is also called Macros. Macros are two-edged swords. Macros viruses are malicious VBA procedures. Setting the security level Security Level High (default) Medium. We should set the security to this level to run our VBA procedure. Low (not recommended) 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. Example VBA function code Function Comm(Sales_V? As Single) as Single  If Sales_V 500 Then  Comm=Sales_V*0.03  Elseif Sales_V=500 and Sales_V1000 Then  Comm=Sales_V*0.06  Elseif Sales_V=1000 and Sales_V2000 Then  Comm=Sales_V*0.09  Elseif Sales_V=200 and Sales_V5000 Then  Comm=Sales_V*0.12  Elseif Sales_V=5000? Then  Comm=Sales_V*0.15  End If End Function Edit VBA program in Excel 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. Write code in visual basic editor Final result Passin