UDF result wrong when cell with UDF copied to range

[powerniso]

Hi everyone.
I have an issue. My UDF below works fine for one cell only. When I try to copy the cell with UDF to another cell, it works too. BUT... if I try to copy the cell with UDF to the range of the cells at the same time, many of them result in 0, unless I press F2 and Enter on that cell. Is there any mistake or is it the bug of the Excel.


Function KursAZN(ByRef Tarix As Date, Optional Valyuta As String = "USD") As Variant

'// Ibrahim NOHBALAYEV terefinden tertib olunub.
'// Bu funksiya AR Merkezi Bankinin valyuta kurslarini tarixe göre internetden alaraq
'// Birinci parametrde ('Tarix') gosterilen tarix ucun ikinci parametrde (vacib deyil, gosterilmezse 'USD' alinacaq)
'// ('Valyuta') gosterilen valyutanin kursunu verir

Const cstriXPath As String = "/ValCurs/ValType/Valute"
Dim xmliDoc As MSXML2.DOMDocument
Dim xmliElement As MSXML2.IXMLDOMElement
Dim xmliSelection As MSXML2.IXMLDOMSelection
'Dim ii As Long
Dim striUrl As String

'Delete all understrikes ( _ ) from the line below. I have added them as this forum thinks I am posting a hyperlink, but it is just the address of the XML file.
striUrl = "h_t_t_p_:_/_/_w_w_w_._c_b_a_r_._a_z_/_c_u_r_r_e_n_c_i_e_s_/" & Format(Tarix, "dd.MM.yyyy", vbMonday, vbFirstJan1) & ".xml"

Set xmliDoc = New MSXML2.DOMDocument
xmliDoc.async = False
xmliDoc.Load striUrl

Set xmliSelection = xmliDoc.SelectNodes(cstriXPath)
'Debug.Print "xmliSelection.Length: " & xmliSelection.Length
For Each xmliElement In xmliSelection
If xmliElement.getAttribute("Code") = UCase(Valyuta) Then
KursAZN = xmliElement.SelectSingleNode("Value").Text
Exit Function
End If
Next xmliElement
'ActiveSheet.Calculate
End Function

[p45cal]

Exploring this it seems to be a site issue, even with .async=false, the site sometimes returns nothing along with a .readystate of 4.
Tinkering with timings between calls to the site seems to return something more consistent; not able to use Application.Wait because it doesn't have the possibility of waiting for less than a second I resorted to putting in a loop immediately before Exit Function:
For i = 1 To 36600000: Next i
This is for my machine, of course so the 36600000 may need to be larger if you have a faster machine. The delay required is less than a second.

Of course, this doesn't bode well if you have many of these functions on a sheet. If you have many different countries for rates on the same date, and they're in a contiguous range on the sheet, the function could be changed to an Array-Entered one which will return many values from one site call.