UBound not in Intellisense. References problem?

[Stephen Rasey]

I wonder if I have a corruption in my registration of References,
particularly with Visual Basic for Applications. I am using Excel 2003
Professional (11.6113.5703) I do not want to reinstall office if I do not
have to. I have been waiting for a lull in work before upgrading to
Windows XP SP2 and Office 2003 SP1.

In the VBE I wrote the line:
n1 = ubound(knownY, 1) 'knownY is a variant accepting a
range as a function argument.

Intellisense did not detect the "UBound(" and give me arguments.
The VBE correctly capitalizes it, so it recognizes it as a function.

If I type "VBA.U" I get UCase, UCase$, but no UBound.

I go to the Object browser. The libraries active are: "Excel", "Office",
"stdole", "VBA", "VBAProject"
I search for "ubound" -- "no items found"
I search for "ucase" --- found in VBA class String.

My original usage of " n1 = ubound(knownY, 1) ",
with knownY as a variant holding a range (2D array) fails.

when I use Ubound this way:
ReDim varXs(1 To n1, 1 To n2)
iv = UBound(varYs, 1)
it works. OK, maybe you cannot use UBound to interrogate the size of a
variant containing a range reference. I am surprised. I get what I need
this way:
n1 = knownY.Rows.Count

Things work, but I am concerned about something not quite right when I
cannot find UBound in the Object browser.

My rerefereces
Visual Basis for Applications: ... \VBA\VBA6\VBE6.DLL (version
6.4.99.69)
Microsoft Excel 11.0 Object Library ...\Microsoft
Office\Office11\excel.exe
Microsoft Office 11.0 Object Library ...\Microsoft
shared\Office11\MSO.DLL
OLE Automation \Windows\System32\TSDOLE2.DLL

There are 5 unchecked available references to "Visual Basic for
Applications"
VBAEND32.OLB
VBAEN32.OLB
msvbvm50.dll
VEN2232.OLB
msvbvm60.dll

If I try to check any of them I get "Name Conflicts with Existing Module,
Project, or Object Library. I cannot swear to it, but I thought I was able
to check more than one reference to different VBA libraries in the past. I
could be wrong about that.

One final clue...
When I went to "Settings -->. Add/Remove Programs" the wizard listed two
entries for Microsoft Office Professional Edition 2003. One size 631 MB,
the other size 636 MB. Both used "rarely" ! I use Office constantly, at
least SOME instance of Office. I will not attempt to uninstall one until
my project is finished.

Something is definately not right. Following the Hypocratic Oath of
"First, do no harm", the patient is not sick enough for major surgery
(reinstallation). Does anyone have some advice for improving the
situation?

Thanks in advance.

Stephen Rasey
WiserWays, LLC
Houston
http:\\excelsig.org

 

[Tom Ogilvy]

I would use rows.count, but you can produce an array by using Value

set rng = Range("A1:A10")
? ubound(rng.value,1)
10
? ubound(rng.value,2)
1

I wouldn't worry about it not appearing in intellisense. Since it works,
for an array (as designed) you don't need additional references.

 

[Stephen Rasey]

Thanks, Tom. I didn't think about the rng.value trick, since .value is the
default.

Could you verify a couple of things for me?
What library is the UBound in? You see it in your Object browser, don't
you?

Can you only select/check one Visual Basic for Applications reference in the
References dialog?

Stephen Rasey

 

[Tom Ogilvy]

I assume it is in the Visual Basic for Applications Library. No, I can't
see it in the object browser. But the object browser depends on the
typelib, so perhaps it isn't included in there - but that doesn't mean it
isn't implemented.

I would see no reason to have more than one reference for VBA.