Help accessing Nematrian Web Services through
Microsoft Excel: Frequently Asked Questions (FAQ)
[this page | pdf | back links]
If you experience trouble accessing Nematrian web functions
and the following questions and answers do not help then please contact us so that we can
answer your query and maximise the benefit others can get from our site.
Question: What do I do if I’m
trying to access Nematrian web functions using Microsoft Excel spreadsheets and
I see lots of ?NAME and don’t seem to be able to get any of the macros to work?
Answer: Try enabling the macros within the
spreadsheet. By default Microsoft Excel often opens spreadsheets containing
macros with them disabled. Alternatively, use a spreadsheet without macros and
load in the equivalent Nematrian add-in, as the macros in these will then be
enabled.
Question: What do I do if I’m
trying to access Nematrian web functions using Microsoft Excel spreadsheets
that I download from the Nematrian website and the macro falls over at a
statement involving something like ‘Dim xmlhtp As New XMLHTTP60’ (usually
commented out) or ‘Dim xmlhp As Object’ and ‘Set xmlhtp =
CreateObject(“MSXML2.XMLHTTP”)’ (usually now the default)?
Answer: These few lines form the only place in the
spreadsheet VBA module that refers to software that is not specifically part of
Excel and therefore may not be loaded when Excel first starts. Specifically,
the two lines now included as default assume that the Excel program on your PC
has access to a Microsoft XML library. The module will then load whatever your
PC views as the default version of this library. If your PC doesn’t recognise
these statements then you may need to include a manual reference to, say,
version 6 of the Microsoft XML library (using Tools>References in the VBA
editor) and then replace the two lines beginning ‘Dim xmlhp As Object’ with a
line like ‘Dim xmlhtp As New XMLHTTP60’. However, Excel may not be able to
understand what “XMLHTTP60” refers to depending on the XML library you may have
access to. If a reference to an older XML library is available then you could
try disabling that reference and enabling a newer version of the XML library or
try replacing XMLHTTP60 with any one of XMLHTTP, XMLHTTP30, XMLHTTP40, … that
does successfully compile. If there are no XML libraries available then check
that you have installed all of Microsoft Excel perhaps after searching through
the object browser. Different legal versions of Excel generally do come with at
least one XML library version.
Question: What do I do if I am
trying to access Nematrian web functions using Microsoft Excel spreadsheets and
I get a lot of #VALUE errors appearing that I do not expect?
Answer: Functions can return #VALUE for a variety of
reasons. Often this is because the spreadsheet has not recalculated itself when
first loaded. You can trick the spreadsheet into doing so by deleting a row
(e.g. the row after the last row in which any cells are non-blank. Sometimes
the existence of #VALUE entries reflect the computations being carried out. For
example the fractional power of a negative number is not well defined, so the
relevant Nematrian web function that returns the result of such a calculation
returns such a value. At other times, it may be because you have entered the
wrong number or the wrong sorts of parameters. Sometimes the Nematrian website
or links to it may not be working properly. If you set “show detailed error
messages” to TRUE in the standard configuration of example spreadsheets then
the spreadsheet should provide an error message each time it comes across an
error. However, be warned! If your spreadsheet is showing #VALUE in lots of
places then there may be lots of error messages that the spreadsheet throws
back to you.
Question: What do I do if I am
trying to access Nematrian web functions using Microsoft Excel spreadsheets and
I get a lot of #VALUE errors appearing that I do not expect even after sorting
out all errors as above?
Answer: Excel spreadsheets sometimes recalculate
themselves when first opened and sometimes do not. Check that the option
recalculate is not set to manual or if it is then recalculate the spreadsheet
manually. If there are still #VALUE errors appearing try recalculating
individual formulae by hand. For example you could click on the cell, press F2
(to begin as if to edit the formula) but then immediately ENTER so that you do
not actually amend the formula in the cell but it is recalculated. Pease bear
in mind that some Nematrian web functions produce array output spanning several
cells, so in these cases you need to edit/recalculate the entire output range
all at once.
Question: How do I access
Nematrian web functions using other spreadsheet packages such as OpenOffice or
Apple or Google products?
The Nematrian Web Service Code
Generator page contains details of how to access Nematrian web functions
through OpenOffice. Individual functions can be accessed through Google Drive,
see spreadsheet downloads available from the relevant function page. At the
time of writing spreadsheets typically bundled with Apple products did not
support a WEBSERVICE function or the equivalent making it more challenging to
access the Nematrian web functions through Apple products except via a browser.
NAVIGATION LINKS
Contents | Prev | Next