Making code faster

[amateur1902]

Hello everyone,

Is it possible to make a VBA code faster through some lines of code?

Thanks

[Bob Phillips]

Yes.

[amateur1902]

This is the code to calculate correlations. But when i run it, it takes to long i think and i hope that i can get it to go faster.

 
 
Private Sub CommandButton1_Click()
'script weergave
Application.ScreenUpdating = False
    
'### START
sngStart = Timer                               ' Get start time.
Set objDataControl = New BlpData
' Set up the fields in an array
arrayFields = Array("PX_LAST")
' Set up the securities in an array
Range("b19").Select
Range(Selection, Selection.End(xlDown)).Select
nr_comp = Selection.Rows.Count
Dim arraySecurities() As String
ReDim arraySecurities(nr_comp)
Range("c15").Select
arraySecurities(0) = ActiveCell.Value
Range("b19").Select
i = 1
Do While i <= nr_comp
    arraySecurities(i) = ActiveCell.Value
    ActiveCell.Offset(1, 0).Select
    i = i + 1
Loop
' Set the periodicity to daily
objDataControl.Periodicity = bbDaily
' Make the request from date to date
    startd = Range("e4").Value
    endd = Range("e5").Value
'#### Bloomberg Connection ####
    objDataControl.GetHistoricalData arraySecurities, 1, arrayFields, _
    CDate(startd), _
    CDate(endd), _
    Results:=vtResult
'count the nr of dates
nr_of_dates = UBound(vtResult)
'arrange independent array
Dim arr_Id() As Variant
ReDim arr_Id(nr_of_dates)
For z = 0 To nr_of_dates
        arr_Id(z) = vtResult(z, 0, 1)
Next
'start loop, calc correl, put in excel
Dim arr_Dp() As Variant
ReDim arr_Dp(nr_of_dates)
Dim arrayCorrel() As Variant
ReDim arrayCorrel(nr_comp)
For a = 0 To nr_comp
        For b = 0 To nr_of_dates
                arr_Dp(b) = vtResult(b, a, 1)
        Next
        
        arrayCorrel(a) = Application.Correl(arr_Id, arr_Dp)
        'Range("C18").Offset(a, 0).Value = Application.Correl(arr_Id, arr_Dp)
        
        ReDim arr_Dp(nr_of_dates) As Variant
Next
'empty the area
    Range("E20").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
'Place data in table
nrCompz = UBound(arraySecurities)
corr = Range("G7").Value
For k = 1 To nrCompz
 If (arrayCorrel(k) = "1") Then
 ElseIf (arrayCorrel(k) > corr) Then
    Range("E20").Offset(k, 0).Value = arraySecurities(k)
    Range("G20").Offset(k, 0).Value = arrayCorrel(k)
 End If
Next

'sort the correl data
    Range("E20").Select
    Range(Selection, Range("G10000")).Select
    Selection.Sort Key1:=Range("G20"), Order1:=xlDescending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A1").Select

'### END
sngEnd = Timer                                 ' Get end time.
sngElapsed = Format(sngEnd - sngStart, "Fixed") ' Elapsed time.
'Set m_BlpData = Nothing
Call objDataControl.Flush
    
' message
    MsgBox sngElapsed & " seconds)"

End Sub

[Bob Phillips]

This is better than the original, but better savings may be had by knowing the app and understanding the data, by re-design of the approach

[vba]

Private Sub CommandButton1_Click()
'script weergave
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

'### START
sngStart = Timer ' Get start time.
Set objDataControl = New BlpData

' Set up the fields in an array
arrayFields = Array("PX_LAST")

' Set up the securities in an array
nr_comp = Range(Range("B19"), Range("B19").End(xlDown)).Rows.Count
Dim arraySecurities() As String
ReDim arraySecurities(nr_comp)
arraySecurities(0) = Range("C15").Value
With Range("B19")
i = 1
Do While i <= nr_comp
arraySecurities(i) = .Cells(i, 1).Value
i = i + 1
Loop
End With

' Set the periodicity to daily
objDataControl.Periodicity = bbDaily

' Make the request from date to date
startd = Range("E4").Value
endd = Range("E5").Value

'#### Bloomberg Connection ####
objDataControl.GetHistoricalData arraySecurities, 1, arrayFields, _
CDate(startd), _
CDate(endd), _
Results:=vtResult

'count the nr of dates
nr_of_dates = UBound(vtResult)

'arrange independent array
Dim arr_Id() As Variant
ReDim arr_Id(nr_of_dates)
For z = 0 To nr_of_dates
arr_Id(z) = vtResult(z, 0, 1)
Next
'start loop, calc correl, put in excel
Dim arr_Dp() As Variant
ReDim arr_Dp(nr_of_dates)
Dim arrayCorrel() As Variant
ReDim arrayCorrel(nr_comp)
For a = 0 To nr_comp
For b = 0 To nr_of_dates
arr_Dp(b) = vtResult(b, a, 1)
Next

arrayCorrel(a) = Application.Correl(arr_Id, arr_Dp)
'Range("C18").Offset(a, 0).Value = Application.Correl(arr_Id, arr_Dp)

ReDim arr_Dp(nr_of_dates) As Variant
Next

'empty the area
Dim rng As Range
Set rng = Range("E20")
Set rng = Range(rng, rng.End(xlToRight))
Range(rng, rng.End(xlDown)).ClearContents

'Place data in table
nrCompz = UBound(arraySecurities)
corr = Range("G7").Value
For K = 1 To nrCompz
If arrayCorrel(K) <> "1" And arrayCorrel(K) > corr Then
Range("E20").Offset(K, 0).Value = arraySecurities(K)
Range("G20").Offset(K, 0).Value = arrayCorrel(K)
End If
Next K

'sort the correl data
Set rng = Range("E20")
Range(rng, Range("G10000")).Sort Key1:=Range("G20"), Order1:=xlDescending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'### END
sngEnd = Timer ' Get end time.
sngElapsed = Format(sngEnd - sngStart, "Fixed") ' Elapsed time.
'Set m_BlpData = Nothing
Call objDataControl.Flush

' message
MsgBox sngElapsed & " seconds)"

With Application

.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
[/vba]

[amateur1902]

Can i ask what you have changed?

I am trying to learn about VBA and i can use every help i can get.

Thanks for youre help.

[gwkenny]

The key here is to determine how long it is taking to query bloomberg for data. You code will not go much faster than that.

You can get a sense of how long that takes by putting some msgbox's to bracket the querying of bloomberg.

The thing you can do to speed things up a lot is to write everything you want to an array, and then write the array in one shot to a range instead of looping through each element. That'll be much faster.

[amateur1902]

That i tried the first time but i could not make it work. Unfortunatly it was to difficult for me. I have been working with VBA for a week now and it works great but it requires a lot of help.

Thanks for your'e input

[Bob Phillips]

I took out all of the selecting, and also turned calculation off for the process.