Hi all,
I have the following XSD load to XML Source of excel:
< xml version="1.0" >
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="stock">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="quote">
<xs:complexType>
<xs:sequence>
<xs:element name="symbol" type="xs:string" />
<xs:element name="price" type="xs:string" />
<xs:element name="lastTrade" type="xs:string" />
</xs:sequence>
<xs:attribute name="name" type="xs:string" use="required" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
Then I drag "symbol" and "price" from XML Source to the worksheet to form a lists. The display is fine up to this point.
Now I added the following VBA code to load the XMLMap:
Public loadCount As Integer
Public Function dummy(val As Integer) As String
Dim buf As String
buf = "< xml version='1.0' encoding='UTF-8' ><stock><quote name='Sybase'><symbol>syb</symbol><price>35.57</price><lastTrade>32.51</lastTrade></quote><quote name='IBM'><symbol>ibm</symbol><price>33.37</price><lastTrade>32.51</lastTrade></quote></stock>"
loadCount = val
'MsgBox ("calling")
dummy = LoadXMLData("stock_Map", buf)
'MsgBox ("done")
End Function
Public Function LoadXMLData(mapName As String, xmlBuffer As String)
Dim xmap As XmlMap
'MsgBox ("calling.." & loadCount)
On Error GoTo LoadXML_Err
' get the XmlMap
' name of the map should be loaded from config later
Set xmap = Application.ActiveWorkbook.XmlMaps(mapName)
'Import XML
Select Case xmap.ImportXml(xmlBuffer)
Case xlXmlImportSuccess
loadCount = loadCount + 1
'MsgBox ("Loaded XML")
Case xlXmlImportVElementsTruncated
MsgBox ("XML Data Truncated")
Case xlXmlImportValidationFailed
MsgBox ("Load Failed because of invalid XML: " & xmlBuffer)
End Select
LoadXMLData = loadCount
Exit Function
LoadXML_Err:
MsgBox ("loading XML Error: " & Err.Description & vbCrLf & Err.Number)
LoadXMLData = "loading XML Error: " & Err.Description & vbCrLf & Err.Number
End Function
Now invoking the the function from excel by doing =dummy(0)
Here's the result:
result:
103
symbol price
syb 35.57
ibm 33.37
Looks like excel is looping 103 times to get to the final display. Any explaination
Thanks !!
VBA XmlMap.loadXML problem - repeating call
Bfundy
Hi,
Our support engineer needs some clarifcations before he can proceed.
I ran his code but did not see where 103 came from. So I want to confirm. What is "103" refer to And what does "val As Integer" in dummy Function mean
-brenda (ISV Buddy Team)
Noah
I don't think this approach is going to work for me. The whole purpose of using RTD Server is getting real time update from messaging bus. The reason of using wrapper function is to hide technical details such as topic name, filters from end users. Since the message is in XML format, using XmlMap would seem to be a prefect fit to allow user select fields on demand.
But because of this weird behavior with XmlMap, I think I would have to come up with something else without using XmlMap...
Thanks !
Raven58
Here's some additional info from the engineer:
The global variable is like "Public loadCount As Integer" in your original code.
We can check that global variable when our Function runs, if it is more than one, it indicates that the Function has run once. We can stop calculating more (when exiting the Function, we can reset the global variable).
-brenda (ISV Buddy Team)
AU.T
Mark Kuether
the latest from the support engineer:
According to my further research, you should not be doing that from a worksheet function. Worksheet functions should only return values and should not affect the structure of the workbook. Most likely the LoadXMLData and ImportXML should fail when called from a worksheet function but sometimes methods that should fail in that context still work, especially when you call them in a secondary procedure.
Here is an article that describes this behavior.
170787 Description of limitations of custom functions in Excel
http://support.microsoft.com/default.aspx scid=kb;EN-US;170787
-brenda (ISV Buddy Team)
d6rth7ader
Thanks for the suggestion!
The problem appears to be related to loading XML. The dummy function only gets call for one time if the loadXML function get removed.
I could do a work around with the global count. But that doesn't explain why XmlMap and loadXML have to do with all the calls to my dummy function.
And what I really wanted to do is having RTD in a wrapper call and have the wrapper function update the XML lists when the cell gets update from RTD server.
There are two problems I have with this approach:
1. All cells in the column I have the wrapper function calling RTD gets involved in the calling. End up having 140 calls to loadXML when the first time RTD returns something. This is exactly what I have in the sample code.
2. After the first update, the wrapper function stop response to any new updates from RTD server. Without the loadXML function in the wrapper, it works fine with every updates from RTD.
Just wonder if this is a system limitation for the time being or there's something behind that could be configured.
Thanks !
josku_x
Per our support engineer:
I can reproduce the problem, though the value is always 104. The problem seems that sometimes when "=dummy(0)" is run, all cells in that column are re-evaluated, so the function is run multiple times.
I suggest that you use some global variable or data to check if you do not want to run multiple times.
-brenda (ISV Buddy Team)
goldsteinm
103 is shown in the cell where =dummy(0) returns
val As Integer is the initial value of count passed to dummy()
Everything time LoadXML is called, the counter increment by 1 and stored in global variable.
When I turn on the msg box, I can actually see pop message coming up 103 times after loadXML call even there's only one call to =dummy(0).
Any reason for that
hazemabolrous
Per the support engineer:
Thanks for elaborating your concerns.
I can ask our internal resources why the behavior is weird. However, it may take some time. By the way, in your situation, the global count may not be good. How about the following approach:
1. Do not use "directly calculate on cell input" approach. Instead, use a button to let user control when to evaluate.
2. You can input the value in some columns, but do not calculate on the fly.
3. When user clicks the button (or maybe macro), he can control to calculate a cell, a column, etc. Then VBA code can calculate each cell.
Does this approach work for you
-brenda (ISV Buddy Team)