powerniso
10-10-2015, 03:26 AM
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
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