VBA enhancements
 

Microsoft could make error checking a lot easier, even possible in some
cases, it you would add the following to VBA:

1) Provide a function similar to UBound() and obj.Rows.Count that would work
on either Excel Range objects as arguments to Function Procedures, and
2) a function, call it Static2 for which a Static2 variable passes out of
existance when a VBA procedure returns control to Excel, or
3) a function, call it IsRangeObject, that can be used to test if an
argument to a VBA procedure is an Excel Range Object, as opposed to a VBA
array.

You coud use it in Excel, for example, instead of a #Value error, to tell
the user in a MsgBox that in the function MMult(x,y) the array x must have
the same number of columns as Y has rows. In the applications I write, or
would like to write but can't" this capability is very desirable or sometimes
aessential.

Thanks
--
Thanks for your help

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

VBA enhancements
 

Rewrite of the original post to correct typos:

-- 1) Provide a function similar to UBound() and obj.Rows.Count that would
work
on either Excel Range objects or VBA arrays as arguments to Function Procedures, and
2) a function similat to Static, call it Static2, for which a Static2 variable used in a re-entrant procedure like xx(xx(x,y),xx(w,z)) passes out of existance when a VBA procedure returns control to Excel, or
3) a function, call it IsRangeObject, that can be used to test if an
argument to a VBA procedure is an Excel Range Object, as opposed to a VBA
array.

You coud use it in Excel, for example, instead of a #Value error, to tell
the user, in a MsgBox, that in the function MMult(x,y) the array x must have
the same number of columns as y has rows. In the applications I write, or
would like to write but can't, this capability is very desirable or sometimes
aessential.

Thanks for your help


"LesHurley" wrote:

Microsoft could make error checking a lot easier, even possible in some
cases, it you would add the following to VBA:

1) Provide a function similar to UBound() and obj.Rows.Count that would work
on either Excel Range objects as arguments to Function Procedures, and
2) a function, call it Static2 for which a Static2 variable passes out of
existance when a VBA procedure returns control to Excel, or
3) a function, call it IsRangeObject, that can be used to test if an
argument to a VBA procedure is an Excel Range Object, as opposed to a VBA
array.

You coud use it in Excel, for example, instead of a #Value error, to tell
the user in a MsgBox that in the function MMult(x,y) the array x must have
the same number of columns as Y has rows. In the applications I write, or
would like to write but can't" this capability is very desirable or sometimes
aessential.

Thanks
--
Thanks for your help

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

VBA enhancements
 

have you looked at using the TypeName and/or IsArray functions?


"LesHurley" wrote:

Rewrite of the original post to correct typos:

-- 1) Provide a function similar to UBound() and obj.Rows.Count that would
work
on either Excel Range objects or VBA arrays as arguments to Function Procedures, and
2) a function similat to Static, call it Static2, for which a Static2 variable used in a re-entrant procedure like xx(xx(x,y),xx(w,z)) passes out of existance when a VBA procedure returns control to Excel, or
3) a function, call it IsRangeObject, that can be used to test if an
argument to a VBA procedure is an Excel Range Object, as opposed to a VBA
array.

You coud use it in Excel, for example, instead of a #Value error, to tell
the user, in a MsgBox, that in the function MMult(x,y) the array x must have
the same number of columns as y has rows. In the applications I write, or
would like to write but can't, this capability is very desirable or sometimes
aessential.

Thanks for your help


"LesHurley" wrote:

Microsoft could make error checking a lot easier, even possible in some
cases, it you would add the following to VBA:

1) Provide a function similar to UBound() and obj.Rows.Count that would work
on either Excel Range objects as arguments to Function Procedures, and
2) a function, call it Static2 for which a Static2 variable passes out of
existance when a VBA procedure returns control to Excel, or
3) a function, call it IsRangeObject, that can be used to test if an
argument to a VBA procedure is an Excel Range Object, as opposed to a VBA
array.

You coud use it in Excel, for example, instead of a #Value error, to tell
the user in a MsgBox that in the function MMult(x,y) the array x must have
the same number of columns as Y has rows. In the applications I write, or
would like to write but can't" this capability is very desirable or sometimes
aessential.

Thanks
--
Thanks for your help

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

VBA enhancements
 

LesHurley wrote:
Rewrite of the original post to correct typos:

-- 1) Provide a function similar to UBound() and obj.Rows.Count that would
work

on either Excel Range objects or VBA arrays as arguments to Function Procedures, and
2) a function similat to Static, call it Static2, for which a Static2 variable used in a re-entrant procedure like xx(xx(x,y),xx(w,z)) passes out of existance when a VBA procedure returns control to Excel, or
3) a function, call it IsRangeObject, that can be used to test if an
argument to a VBA procedure is an Excel Range Object, as opposed to a VBA
array.


You coud use it in Excel, for example, instead of a #Value error, to tell . . . .

You could use what in Excel? You proposed 3 functions. If you mean 1)
above, how would you implement it? I.e., if

Function foo1(iArg, Optional n = 1)

returned the upper bound of the nth dimension if iArg were an array, or
the number of rows if iArg were a multi cell range and n=1, or the
number of columns if iArg were a multi-cell range and n=2, how would
that affect the MMult call?

Alan Beban

VBA enhancements
 

I don't use statics much at all, so I can't comment on your #2. For #1 and
3, these are actually pretty simple to do in VBA, and I use similar
functions frequently. I never thought it necessary for them to be built into
VBA.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"LesHurley" wrote in message
...
Microsoft could make error checking a lot easier, even possible in some
cases, it you would add the following to VBA:

1) Provide a function similar to UBound() and obj.Rows.Count that would
work
on either Excel Range objects as arguments to Function Procedures, and
2) a function, call it Static2 for which a Static2 variable passes out of
existance when a VBA procedure returns control to Excel, or
3) a function, call it IsRangeObject, that can be used to test if an
argument to a VBA procedure is an Excel Range Object, as opposed to a VBA
array.

You coud use it in Excel, for example, instead of a #Value error, to tell
the user in a MsgBox that in the function MMult(x,y) the array x must have
the same number of columns as Y has rows. In the applications I write, or
would like to write but can't" this capability is very desirable or
sometimes
aessential.

Thanks
--
Thanks for your help

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

VBA enhancements
 

LesHurley wrote:
Rewrite of the original post to correct typos:

-- 1) Provide a function similar to UBound() and obj.Rows.Count that would
work

on either Excel Range objects or VBA arrays as arguments to Function Procedures, and . . .
3) a function, call it IsRangeObject, that can be used to test if an
argument to a VBA procedure is an Excel Range Object, as opposed to a VBA
array.


You coud use it in Excel, for example, instead of a #Value error, to tell

the user, in a MsgBox, that in the function MMult(x,y) the array x must have
the same number of columns as y has rows. In the applications I write, or
would like to write but can't, this capability is very desirable or sometimes
aessential.

Distinguish and identify ranges and arrays:

Function RangeOrArray(iArg)
If TypeName(iArg) Like "*()" Then
RangeOrArray = "Array"
Else
If TypeName(iArg) = "Range" Then
If iArg.Count = 1 Then
RangeOrArray = "Single cell range"
Else
RangeOrArray = "Multi-cell range"
End If
Else
RangeOrArray = "Not Range or Array"
End If
End If
End Function

Return UBound of array or rows or columns of range:

Function UBorCount(iArg, Optional iDim = 1)
If RangeOrArray(iArg) = "Array" Then
UBorCount = UBound(iArg, iDim)
ElseIf RangeOrArray(iArg) = "Multi-cell range" Then
If iDim = 1 Then
UBorCount = iArg.Rows.Count
ElseIf iDim = 2 Then
UBorCount = iArg.Columns.Count
Else
UBorCount = "Second parameter must be 1 or 2"
End If
Else
UBorCount = "First parameter must be a multi-cell range or an array"
End If
End Function

Test procedu

Sub ab4()
Set x = range("B4:D5")
y = range("f4:h5")
'y=range("f4:h6")
If UBorCount(x, 2) = UBorCount(y, 1) Then
q = Application.MMult(x, y)
Else
MsgBox "The number x columns must equal the number of y rows"
End If
range("A21:C22").Value = q
End Sub

Alan Beban

VBA enhancements
 

Thank you all:

IsArray() wont work because it returns true for either a range object or an
array, but TypeName() works just fine for my applications. For Range
Objects, TypeName returns "Range" while for an array it returns "Variant". I
will be able to calculate either Range or Array dimensions by choosing
between UBound() and Rows.Count based on the result of TypeName(). I ReDim a
number of internal arrays in my Function procedures using those dimensions.

This is a simple work-around for many of my apps. I would still like to
have a Static2 function to count the numer of times a function is re-entered
in a single call.
Thanks for your help


"JMB" wrote:

have you looked at using the TypeName and/or IsArray functions?


"LesHurley" wrote:

Rewrite of the original post to correct typos:

-- 1) Provide a function similar to UBound() and obj.Rows.Count that would
work
on either Excel Range objects or VBA arrays as arguments to Function Procedures, and
2) a function similat to Static, call it Static2, for which a Static2 variable used in a re-entrant procedure like xx(xx(x,y),xx(w,z)) passes out of existance when a VBA procedure returns control to Excel, or
3) a function, call it IsRangeObject, that can be used to test if an
argument to a VBA procedure is an Excel Range Object, as opposed to a VBA
array.

You coud use it in Excel, for example, instead of a #Value error, to tell
the user, in a MsgBox, that in the function MMult(x,y) the array x must have
the same number of columns as y has rows. In the applications I write, or
would like to write but can't, this capability is very desirable or sometimes
aessential.

Thanks for your help


"LesHurley" wrote:

Microsoft could make error checking a lot easier, even possible in some
cases, it you would add the following to VBA:

1) Provide a function similar to UBound() and obj.Rows.Count that would work
on either Excel Range objects as arguments to Function Procedures, and
2) a function, call it Static2 for which a Static2 variable passes out of
existance when a VBA procedure returns control to Excel, or
3) a function, call it IsRangeObject, that can be used to test if an
argument to a VBA procedure is an Excel Range Object, as opposed to a VBA
array.

You coud use it in Excel, for example, instead of a #Value error, to tell
the user in a MsgBox that in the function MMult(x,y) the array x must have
the same number of columns as Y has rows. In the applications I write, or
would like to write but can't" this capability is very desirable or sometimes
aessential.

Thanks
--
Thanks for your help

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

VBA enhancements
 

I, obviously, cant modify the MMult function but "you" = "Microsoft" could do
it to give that functionality. An end user might not be able to decipher
what #Value means or know how to correct the error. Why keep 'em in the dark?
--

"Alan Beban" wrote:

LesHurley wrote:
Rewrite of the original post to correct typos:

-- 1) Provide a function similar to UBound() and obj.Rows.Count that would
work

on either Excel Range objects or VBA arrays as arguments to Function Procedures, and
2) a function similat to Static, call it Static2, for which a Static2 variable used in a re-entrant procedure like xx(xx(x,y),xx(w,z)) passes out of existance when a VBA procedure returns control to Excel, or
3) a function, call it IsRangeObject, that can be used to test if an
argument to a VBA procedure is an Excel Range Object, as opposed to a VBA
array.


You coud use it in Excel, for example, instead of a #Value error, to tell . . . .

You could use what in Excel? You proposed 3 functions. If you mean 1)
above, how would you implement it? I.e., if

Function foo1(iArg, Optional n = 1)

returned the upper bound of the nth dimension if iArg were an array, or
the number of rows if iArg were a multi cell range and n=1, or the
number of columns if iArg were a multi-cell range and n=2, how would
that affect the MMult call?

Alan Beban

VBA enhancements
 

... "you" = "Microsoft" ...

Please note that while these forums are hosted by Microsoft, it is
exceedingly rare to see any participation by a Microsoft employee. The
people responding to questions in these forums are volunteers. The happy
talk in the web interface about posting feature suggestions seem to me to be
a placebo.

In any case, the online help for MMULT clearly states that

"The number of columns in array1 must be the same as the number of rows in
array2"

Whenever a user receives an error while using any function, the first step
should be to the documentation to make sure they're using the function
correctly, and to the arguments of the function, to make sure they are
appropriate to the function being used. Should every function attempt to
give detailed feedback about the error right in the worksheet?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"LesHurley" wrote in message
...
I, obviously, cant modify the MMult function but "you" = "Microsoft" could
do
it to give that functionality. An end user might not be able to decipher
what #Value means or know how to correct the error. Why keep 'em in the
dark?
--

"Alan Beban" wrote:

LesHurley wrote:
Rewrite of the original post to correct typos:

-- 1) Provide a function similar to UBound() and obj.Rows.Count that
would
work

on either Excel Range objects or VBA arrays as arguments to Function
Procedures, and
2) a function similat to Static, call it Static2, for which a Static2
variable used in a re-entrant procedure like xx(xx(x,y),xx(w,z)) passes
out of existance when a VBA procedure returns control to Excel, or
3) a function, call it IsRangeObject, that can be used to test if an
argument to a VBA procedure is an Excel Range Object, as opposed to a
VBA
array.


You coud use it in Excel, for example, instead of a #Value error, to
tell . . . .

You could use what in Excel? You proposed 3 functions. If you mean 1)
above, how would you implement it? I.e., if

Function foo1(iArg, Optional n = 1)

returned the upper bound of the nth dimension if iArg were an array, or
the number of rows if iArg were a multi cell range and n=1, or the
number of columns if iArg were a multi-cell range and n=2, how would
that affect the MMult call?

Alan Beban