This issue isn't only seen in 2003, but also in 2002. However, even for
as much VBA writing that I do including having other programs
interacting with it such as Acrobat Adobe Pro or Host Access Class
Library (used to control IBM's Personal Communications program that
interacts with the main DB system), I only rarely use this immediate
window and generally only small things. If you try to use it for larger
things, you will notice that this window will only retain so much of the
last text that goes into the window while the rest of it is dropped.
I have noticed this to happen with a lot of MS's add-ins though the
Analysis Toolpak (both the regular one and the VBA one) to be very bad
about this. I do have to agree it's not a good thing to disable those 2
Add-Ins cause it means having to completely rewrite the code and I don't
look forward to doing that. It's already bad enough I have to do that
for fiscal periods, especially given the fact that management don't
stick to the same pattern, thus once every 3 years or so, I'm having to
redo the formulas, if not have it one way one year and then revert back
to the original formulas the next year. Finally, I had enough of
redoing it so much to the point that I created a stand alone module to
specifically deal with these fiscal date periods including putting in
various options.
Even though this means that I have to tie all such formulas to the set
of UDFs that I have created for these issues, it has saved me some time.
However, one major draw back to this, Excel remembers the absolute path
that you used when you created the formula to reference the UDF. This
means you can only use either the mapped drive every time or the
universal naming convention(UNC) everytime. It certainly would be nice
to be able to use relative references to the workbooks rather than just
plain old absolute references cause here's the problem when using
network drives to store files:
Most people use mapped drives, and at least within the company that I
work in, the "O" drive is mapped to their local organization folder,
which means, each user's "O" drive is mapped to the server that is on
the same site as they work at primarily (or at least considered as their
home site).
Problem with using mapped drives in larger organizations: Person A at
Site A is mapped to the their local organization folder which assume has
the file on it. Person A would be able to open it fine.
Person B at Site B is mapped to their local organization folder which is
NOT the same server, which means, Person B won't be able to use the
function cause the file isn't being located. Even if person B does open
it from the proper server, it's either via another mapped drive or the
UNC method, which is NOT the same path name as the function is
expecting.
Problem with using UNC. While the UNC would resolve the above issue,
most people use mapped drives, thus if the person opens it via a mapped
drive, the function once again won't function properly cause it's NOT
seeing the same path as the function is expecting.
Obvoiusly, I wouldn't want to necessarily have these types of files
stored on all users PCs either cause then when updates are applied, it
doesn't carry down to the PCs. It would be nice to be able to use some
sort of an indirect function to refer to such UDFs similar to how I been
able to use the "INDIRECT" function to refer to cells within specific
workbooks without regards to what network path that was used to open the
file, though this method doesn't work within Charts as Charts doesn't
allow for such functions.
The only way I been able to figure out how to get around this issue is
to setup a UDF in each individual workbook that relies on the main
workbook, but that's also undesireable for other reasons.
*** Sent via Developersdex
http://www.developersdex.com ***
