Monday, April 8, 2013

VBA Important Programs Class 1-5 Proposal

Course Link on Wiziq -

Qutting a Loop after taking users permission

VBA programs outline:

Class 2:
Copying data validation using range
for and while loop in the same program
param array
exporting file contents into a new file
creating dynamic data tables
clearing values of range selectively based on logic
Using capital IQ, factset data bases

Class 3:
Concepts of Financial Engineering
Adjustment to data

Class 4:
Intro to Monte Carlo in Excel
Cho decomposition

Class 5:
Revision Project open session / Pulling data from yahoo case study (Ref: )

Areas that we will talk on:
On erro goto next should be avoided use goto in case you need to. on error goto 0 removes the effect for Future of on erro resume next
Named ranges all cases that could happen, seaching, copying, string, address not found, etc.Resetting charts Where required, ignore charts that is not there.
Correlated residuals to be used on the future values, why residual smatter in monte carlo? how to get these Correlated residuals in Monte Carlo...
Interview questions in VBA
#na from local us #na from a link?
Factset codes, sources, and adjustments
Searching #NA picked from toher values, power of NA()
Updates in term of both pages e and i
Example of array formula f ctr shift enter (matrix multiplication)
VBA notes on linking with power point
Power point advanced charting, flow charts,etc
OFFset function (example):

Some codes that we will discuss:


Option Explicit
Sub SetFormat()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    With ws
              ws.Cells.Font.Name = "Arial"
        End With
    Next ws
    End Sub


m = Range("range").Count
For j = 0 To m
      If Range("range")(j).Value = "O" Then
      Range("range2")(j, 0).EntireRow.Clear
       Range("range")(j).Value = "O"
       End If
   Next j


Private Sub Reset()
   Dim objCht As ChartObject
   For Each objCht In ActiveSheet.ChartObjects
   With objCht.Chart
        With .Axes(xlValue)
       .MinimumScaleIsAuto = True
      .MaximumScaleIsAuto = True
  End With
      End With
   Next objCht
End Sub


Sub Highlight_Cells_With_Text_or_Formulas()

'Highlights all cells with text or formulas on the active sheet
'Will remove color from cells without formulas or text

Dim r As Range

With ActiveSheet.UsedRange
    .Interior.ColorIndex = xlNone
    For Each r In .Cells
        If r.Value <> "" Then r.Interior.ColorIndex = 3
End With

End Sub


Code for playing with data validation

Private Sub Worksheet_Activate2()
    Dim rng As Range, cell As Range
    With Range("BL38:BL38")
        Set rng = Intersect(.SpecialCells(xlCellTypeAllValidation), .SpecialCells(xlCellTypeBlanks))
    End With
    If Not rng Is Nothing Then
        Application.EnableEvents = False
        For Each cell In rng
            cell.Value = Sheets("Assumptions").Range("B7:B9")(1).Value
        Next cell
        Application.EnableEvents = True
    End If
End Sub


Private Sub Set_Default_values() 
  Dim rng As Range
    Set rng = Range("Default_Values").SpecialCells(xlCellTypeAllValidation)
   Range("Range_name").Value = Range("Default_Values")(1).Value
End Sub


Sub AddNewWBKs()
Dim myWB As Workbook, newWB As Workbook
Dim myWS As Worksheet
Set myWB = ThisWorkbook
Application.ScreenUpdating = False
Set myWS = myWB.Sheets("Assumptions")
Set newWB = Workbooks.Add
Range("A1").PasteSpecial xlPasteValues
 Range("A1").PasteSpecial xlPasteFormats
Application.CutCopyMode = False
Application.DisplayAlerts = False
 newWB.SaveAs Filename:="C:\Documents and Settings\shivgan\My Documents\Exported Spreadsheet.xls"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Public Sub Count()
  Dim count_columns_selection, i, iReply As Integer
  Dim range_name_string As String
  For i = 1 To Range("Range_X").Rows.Count

Code for anything that may cause trouble

    On Error GoTo Error_control
  Next i

Do While i < Range("Range_X").Rows.Count
   iReply = MsgBox(Prompt:="Foudn Error?", _
            Buttons:=vbYesNoCancel, Title:="BLANK???")
   On Error GoTo -1
    If iReply = vbYes Then
     GoTo ContinueLoop
    ElseIf iReply = vbNo Then
      Exit Sub
       Exit Sub
    End If
End Sub


Range("G6").GoalSeek Goal:=0, ChangingCell:=Range("G7")

End Sub

Sub reset()

Range("G7").Value = 0

End Sub


Join our VBA for financial engineering course ( & get 15% discount. Ask for discount code, email -

No comments:

Post a Comment