VBA Important Programs Class 1-5 Proposal

Course Link on Wiziq - http://www.wiziq.com/course/19620-vba-for-financial-engineering-and-modeling

Qutting a Loop after taking users permission

VBA programs outline:

VBA Class 1.2 (VBA for Financial Engineering)

Uploaded by Shivgan on WizIQ Tutorials

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
10k
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: )

http://www.youtube.com/watch?v=iSlBE3CWg5Q


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): http://support.microsoft.com/kb/324991



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

        Next

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

myWS.Range("Coverage_Data").Copy

Range("A1").PasteSpecial xlPasteValues

 Range("A1").PasteSpecial xlPasteFormats

Application.CutCopyMode = False

ActiveSheet.UsedRange.EntireColumn.AutoFit

Application.DisplayAlerts = False

 newWB.SaveAs Filename:="C:\Documents and Settings\shivgan\My Documents\Exported Spreadsheet.xls"

Application.DisplayAlerts = True

newWB.Close

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
    ContinueLoop:
  Next i

Do While i < Range("Range_X").Rows.Count
Error_control:
    
   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
    Else
       Exit Sub
    End If
    
 Loop
 
  
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 (http://www.wiziq.com/course/19620-vba-for-financial-engineering-and-modeling) & get 15% discount. Ask for discount code, email - info@qcfinance.in.

Course Link on Wiziq - http://www.wiziq.com/course/19620-vba-for-financial-engineering-and-modeling