PDA

View Full Version : Solved: Re-use Automation Object Variables vs. release and re-Create



Dr.K
10-08-2007, 10:18 AM
I have a bunch of Functions that pull in financial data from Yahoo Finance. I've been using Internet Explorer objects, but that was slow and quirky. I recently discovered that the Windows HTTP service has its own object model that you can directly address from VBA: "WinHttp.WinHttpRequest.5.1"

This is WAY faster then using Explorer, and it's object model lets you adjust things like time outs and so forth. I love it.

However, its brought me to a deeper question, a more philosohpical programming issue. Maybe some of you hard core programmer types can shed some light.

If you need to use an Automation Object several times in one routine, is it better to just reuse the Object, or to expressly set it to "= nothing" and then re-create it? Does it matter?

The piece of code below pulls historically-adjusted closing-prices for stocks and mutual funds. Since the market is only only about 4.5 days a week, if there is no closing price for a particular date, it adjusts the date back by one day and tries again. It runs through a for loop, giving up afte r 7 days. This is why I need to reuse the Object.

Note that I'm using late binding becuase I am not sure whether or not the Object model is available on all potential end-users' machines.

Function YFGetPrice(Ticker As String, Price_Date As Date) As Currency

Dim URL As String
Dim strHTML As String
Dim StartPos As Long
Dim EndPos As Long
Dim CellNo As Integer
Dim x As Integer
Dim NewDate As Date
Dim WinHTTP As Object


For x = 0 To 7

Let NewDate = DateAdd("d", -x, Price_Date)

Let URL = "http://finance.yahoo.com/q/hp?s= + Ticker + Date"

GetHTTP:

On Error Resume Next
Set WinHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
If WinHTTP Is Nothing Then
Set WinHTTP = CreateObject("WinHttp.WinHttpRequest.5")
End If

On Error GoTo HTTPErr
With WinHTTP
.Open "GET", URL
.Send
strHTML = .ResponseText
End With
Set WinHTTP = Nothing
On Error GoTo 0

'data is in string then extract it and end function, else loop
If InStr(1, strHTML, yfNoAvail, vbTextCompare) = 0 Then

'EXTRACT PRICE - Code removed

Let YFGetPrice = Mid(strHTML, StartPos, EndPos - StartPos + 1)
Exit Function
End If
Next x

Exit Function
HTTPErr:
If Err = -2147012867 Then
Set WinHTTP = Nothing
Resume GetHTTP
End If

Set WinHTTP = Nothing
Err.Raise Err

End Function


Here is the same code, but with reusing the Object Variable:

On Error Resume Next
Set WinHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
If WinHTTP Is Nothing Then
Set WinHTTP = CreateObject("WinHttp.WinHttpRequest.5")
End If
On Error GoTo 0

For x = 0 To 7

Let NewDate = DateAdd("d", -x, Price_Date)

'Set URL
Let URL = "http://finance.yahoo.com/q/hp?s= + Ticker + Date"

GetHTTP:

On Error GoTo HTTPErr
With WinHTTP
.Open "GET", URL
.Send
strHTML = .ResponseText
End With
On Error GoTo T0

'data is in string then extract it and end function, else loop
If InStr(1, strHTML, yfNoAvail, vbTextCompare) = 0 Then

'EXTRACT PRICE - Code removed

Set WinHTTP = Nothing
Let YFGetPrice3 = Mid(strHTML, StartPos, EndPos - StartPos + 1)
Exit Function
End If

Next x

Set WinHTTP = Nothing
Exit Function
HTTPErr:
If Err = -2147012867 Then Resume GetHTTP

Set WinHTTP = Nothing
Err.Raise Err

After some time testing (with a few hundred repitions each), it does not appear that one method is consistently faster then the other, which was VERY surpirising to me.

Comments, feedback appreciated.

johnske
10-09-2007, 03:07 AM
if the object created is an application level object (Word, Access, Powerpoint, etc) you should quit (in some cases - close) it before setting it equal to nothing. if the object is not an application level object there is no point in setting it equal to nothing as it's automatically destroyed at the end of the procedure anyway.

as far as the question about re-using it is concerned, IMO just re-use it...

Dr.K
10-09-2007, 01:07 PM
AHA, that makes a hell of a lot of sense... WinHTTP is a SERVICE, not an Application... You arn't shutting it down when you set it = to zero, you are just destroying the object that points to the service.

Thanks for the input.

Do you know of any good references where I can read up on the finer points of Automation Objects?

johnske
10-09-2007, 01:26 PM
yes, Microsofts office 2000 visual basic programmers guide (http://xlvba.3.forumer.com/index.php?showtopic=97)

johnske
10-09-2007, 01:34 PM
there's also a shorter discussion here (http://www.vbaexpress.com/forum/showthread.php?t=13237&highlight=automation)