I want to get this information out to users and this is the only vehicle I
can find. I don't know how to add anything to the knowledge base, and I
received no response when I sent email to the support site staff.
Here's the thing: I wrote a macro that accesses the Value property of a
range. If the range is several contiguous cells, the Value property is an
array. I access the value of the first cell in each row, edit it, and put it
in another cell. In Excel 2000, this works fine. In Excel 2003, the macro
immediately returns a compile error complaining of invalid elements and will
not run. The workaround is to use Value2 instead of Value where accessing
the array is needed.
--
Assigning guilt doesn't solve the problem

post your code, so we may (dis)agree or advise

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


LabElf wrote :

I want to get this information out to users and this is the only
vehicle I can find. I don't know how to add anything to the
knowledge base, and I received no response when I sent email to the
support site staff. Here's the thing: I wrote a macro that accesses
the Value property of a range. If the range is several contiguous
cells, the Value property is an array. I access the value of the
first cell in each row, edit it, and put it in another cell. In
Excel 2000, this works fine. In Excel 2003, the macro immediately
returns a compile error complaining of invalid elements and will not
run. The workaround is to use Value2 instead of Value where
accessing the array is needed. --
Assigning guilt doesn't solve the problem

OK, for anyone who is interested, here is the code for the AddTest macro. It
calls some other macros, and makes some assumptions about the worksheet it is
called from, but I think it should be fairly clear.

Sub AddTest()
'Description:
' Get the Test Case ID from the top of the worksheet, remove any white space
' and convert to upper case. Determine which section the selected permuta-
' tions are in and choose corresponding test type code. Read the
NumberFormat
' property for the entire selection and adjust for special cases. Call
' AddSingle on each cell in the selection to create the test name in the
' target cell.
'Parameters: (none)
'Global Variables:
' xlTarget - the cell chosen to contain the next test case name
'Notes:
' Macro assumes that all selected cells are permutation numbers, but it
' only looks at the first column of an array, so it is fairly insensitive to
' users selecting entire rows or including cells to the right of permutation
' numbers.
' Excel 2003 throws an error when it sees the value parameter being ac-
' cessed as an array regardless of whether that code will be executed, so
the
' value2 property is used for those instances.
'Keyboard Shortcut: Ctrl+m
'Local Variables:
' SKY_BLUE - Color value used for test section titles
Const SKY_BLUE = 33
' iAnswer - Result of MsgBox call
Dim iAnswer As Integer
' strTcId - String to hold the TC ID from the worksheet
Dim strTcId As String
' bSectionFound - Flag for test section identified
Dim bSectionFound As Boolean
' strTestType - String to hold the test type code (P, N or E)
Dim strTestType As String
' xlSectionName - Cell that looks like a type section title
Dim xlSectionName As Excel.Range
' xlSearchStart - Cell from which to begin searching
Dim xlSearchStart As Excel.Range
' strError - Error information
Dim strError As String
' xlPnumRange - The selection of permutation numbers
Dim xlPnumRange As Excel.Range
' strFormatSpec - NumberFormat specification
Dim strFormatSpec As String
' strPnum - Permutation number as text
Dim strPnum As String
' xlCurrRange - Range object pulled from Areas collection (noncontiguous
' selection)
Dim xlCurrRange As Excel.Range
' lPnumIndex - index into array of permutation cell values
Dim lPnumIndex As Long

'If target cell has not been identified
If bTargetDefined = False Then
iAnswer = MsgBox("No destination is selected", vbExclamation,
"AddTest")
Exit Sub
Else
'GET THE TC ID - remove white space
strTcId = Trim(CleanText(Cells(1, 3).Value, bReplace:=False))
'Convert to upper case
strTcId = UCase(strTcId)
'Add underscore if not already present
If Right(strTcId, 1) < "_" Then
strTcId = strTcId & "_"
End If

'IDENTIFY SECTION
'Initialize section found flag
bSectionFound = False

'Start search for section name at cell to the right one and up one
from
'the first cell of the selected permutation(s)
Set xlSearchStart = Selection.Cells(1, 1).Offset(-1, 1)
Do While bSectionFound = False
'Search upwards for test group banner
Set xlSectionName = Cells.Find(What:=" Tests",
After:=xlSearchStart, _
LookIn:=xlValues, LookAt:=xlPart,
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False)
'If search failed
If xlSectionName Is Nothing Then
'Find section failure - indicate with message box and exit
iAnswer = MsgBox("Couldn't find test type title",
vbExclamation, _
"AddTest")
Exit Sub
Else
'(found possible banner text)
'If section name cell is not above permutation cell
If Selection.Row <= xlSectionName.Row Then
'Find section failed - indicate with message box and exit
strError = "Couldn't find test type title" & vbCr & _
"above selected permutation number(s)"
iAnswer = MsgBox(strError, vbExclamation, "AddTest")
Exit Sub
'If above permutation cell but wrong color
ElseIf xlSectionName.Interior.ColorIndex < SKY_BLUE Then
'Search for next match
Set xlSearchStart = xlSectionName
'If it contains "Positive Tests"
ElseIf StrComp(xlSectionName.Value, "Positive Tests", _
vbBinaryCompare) = 0 Then
'Add code "P" and end search
strTestType = "P"
bSectionFound = True
'If it contains "Negative Tests"
ElseIf StrComp(xlSectionName.Value, "Negative Tests", _
vbBinaryCompare) = 0 Then
'Add code "N" and end search
strTestType = "N"
bSectionFound = True
'If it contains "Error Recovery Tests"
ElseIf StrComp(xlSectionName.Value, "Error Recovery Tests", _
vbBinaryCompare) = 0 Then
'Add code "E" and end search
strTestType = "E"
bSectionFound = True
Else '(unrecognized section name)
'indicate with message box and exit
strError = "Found invalid test type title" & vbCr & "'"
& _
xlSectionName.Value & "'"
iAnswer = MsgBox(strError, vbExclamation, "AddTest")
Exit Sub
End If
End If
Loop

'Get selected cells
Set xlPnumRange = Selection

'If single cell selected
If xlPnumRange.Count < 2 Then
'Get good format spec
strFormatSpec = AdjustFormat(xlPnumRange)
'Make next name with this permutation
Call AddSingle(xlPnumRange.Value, strFormatSpec, strTcId &
strTestType)

'Else if noncontiguous selection
ElseIf 1 < xlPnumRange.Areas.Count Then
For Each xlCurrRange In xlPnumRange.Areas
'Get good format spec
strFormatSpec = AdjustFormat(xlCurrRange)
'If this range is a single cell
If xlCurrRange.Count < 2 Then
'Make next name with this permutation
Call AddSingle(xlCurrRange.Value, strFormatSpec, strTcId
& _
strTestType)
'Else if range is a contiguous group
ElseIf IsArray(xlCurrRange.Value) Then
For lPnumIndex = LBound(xlCurrRange.Value, 1) To _
UBound(xlCurrRange.Value, 1)
'Make next name with this permutation
Call AddSingle(xlCurrRange.Value2(lPnumIndex, 1), _
strFormatSpec, strTcId & strTestType)
Next lPnumIndex
End If
Next xlCurrRange

'Else if selection is a contiguous group
ElseIf IsArray(xlPnumRange.Value) Then
'Get good format spec
strFormatSpec = AdjustFormat(xlPnumRange)
'For each row in value array
For lPnumIndex = LBound(xlPnumRange.Value, 1) To _
UBound(xlPnumRange.Value, 1)
'Make next name with this permutation
Call AddSingle(xlPnumRange.Value2(lPnumIndex, 1),
strFormatSpec, _
strTcId & strTestType)
Next lPnumIndex
End If
'END if target cell defined (else clause)
End If
End Sub


"keepITcool" wrote:

post your code, so we may (dis)agree or advise

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

the bug is not a bug.. imo your syntax is erroneous..


'Make next name with this permutation
Call addsingle(xlPnumRange.Value2(lPnumIndex, 1), _
strFormatSpec, strTcId & strTestType)


already part of some 'cumbersome' code to loop a multiarea range
this will very likely go bust..

if xlPnumRange is a 1 row, multicolumn range (may be 1 area of your
entire selection) the Value or Value2 of that range will be returned as
a 1 dimensional array.

dim v(1) = debug.print v(1,1) will produce an error.

Thus you might use:
Call addsingle(xlPnumRange.Cells(lPnumIndex, 1).Value, _
strFormatSpec, strTcId & strTestType)

Note that when enumerating ROWS inside a range you cannot abbreviate
getting the first cell of the row range like xlRow(1,1).. you must use
xlRow.Cells(1,1). To avoid confusion avoid the abbreviated syntax
altogether.


But even then I'd code your entire for each differently.

After your loop:

'Get selected cells
Set xlPnumRange = Selection

Dim xlArea As Range, xlRow As Range
For Each xlArea In xlPnumRange.Areas
For Each xlRow In xlArea.Rows
'Get good format spec
strFormatSpec = adjustformat(xlRow)
'Make next name with this permutation
Call addsingle(xlRow.Cells(1,1).Value, _
strFormatSpec, _
strTcId & strTestType)
Next
Next

Can't test but that should do the same as your code.


HTH

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


LabElf wrote :

OK, for anyone who is interested, here is the code for the AddTest
macro. It calls some other macros, and makes some assumptions about
the worksheet it is called from, but I think it should be fairly
clear.


SNIP


"keepITcool" wrote:

post your code, so we may (dis)agree or advise

--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam

Thank you, keepITcool, I will try your suggestions; however, I would like to
clarify some facts:
The code I posted works correctly in both Excel 2000 and Excel 2003. The
syntax is not erroneous.
The code is cumbersome because the user may have selected a single cell, a
range of contiguous cells, or a disjoint selection containing individual
cells or several contiguous selections (or both). The structure of range
object for the selection is different in each of these cases. (But see my
final paragraph below.)
If the range contains a contiguous group of rows of one column each, the
Value2 property does not contain a one-dimensional array, but rather a
two-dimensional array. The second subscript range is 1 to 1. (I checked
this again in Excel 2003 to make sure I'm reporting the behavior accurately.)
The debugger in Excel 2003 shows a Value2 property, but no Value property,
although obviously it is there in some form because the remaining references
to Value work.
I still think the Excel 2003 interpreter is in error for objecting to syntax
that is not incorrect.

That said, I think I can see where iterating through the Areas collection
could make a simpler loop. I wasn't using the Areas collection when I
started writing this macro because I didn't know how to use it then. I'll
let you know how it works out.

"keepITcool" wrote:


the bug is not a bug.. imo your syntax is erroneous..


'Make next name with this permutation
Call addsingle(xlPnumRange.Value2(lPnumIndex, 1), _
strFormatSpec, strTcId & strTestType)


already part of some 'cumbersome' code to loop a multiarea range
this will very likely go bust..

if xlPnumRange is a 1 row, multicolumn range (may be 1 area of your
entire selection) the Value or Value2 of that range will be returned as
a 1 dimensional array.

dim v(1) = debug.print v(1,1) will produce an error.

Thus you might use:
Call addsingle(xlPnumRange.Cells(lPnumIndex, 1).Value, _
strFormatSpec, strTcId & strTestType)

Note that when enumerating ROWS inside a range you cannot abbreviate
getting the first cell of the row range like xlRow(1,1).. you must use
xlRow.Cells(1,1). To avoid confusion avoid the abbreviated syntax
altogether.


But even then I'd code your entire for each differently.

After your loop:

'Get selected cells
Set xlPnumRange = Selection

Dim xlArea As Range, xlRow As Range
For Each xlArea In xlPnumRange.Areas
For Each xlRow In xlArea.Rows
'Get good format spec
strFormatSpec = adjustformat(xlRow)
'Make next name with this permutation
Call addsingle(xlRow.Cells(1,1).Value, _
strFormatSpec, _
strTcId & strTestType)
Next
Next

Can't test but that should do the same as your code.


HTH

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


LabElf wrote :

OK, for anyone who is interested, here is the code for the AddTest
macro. It calls some other macros, and makes some assumptions about
the worksheet it is called from, but I think it should be fairly
clear.


SNIP


"keepITcool" wrote:

post your code, so we may (dis)agree or advise

--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam

your code will stop if any area in your collections happens to
have 1 row and 2 or more columns.

MsgBox Range("a1:e1").Value2(1, 1)

will gen error number 9: subscript out of range
in ANY excel version.

THIS is much more logical and will NOT generate an error.
MsgBox Range("a1:e1").Cells(1,1).Value2



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


LabElf wrote :

Thank you, keepITcool, I will try your suggestions; however, I would
like to clarify some facts:
The code I posted works correctly in both Excel 2000 and Excel 2003.
The syntax is not erroneous.
The code is cumbersome because the user may have selected a single
cell, a range of contiguous cells, or a disjoint selection containing
individual cells or several contiguous selections (or both). The
structure of range object for the selection is different in each of
these cases. (But see my final paragraph below.)
If the range contains a contiguous group of rows of one column each,
the Value2 property does not contain a one-dimensional array, but
rather a two-dimensional array. The second subscript range is 1 to
1. (I checked this again in Excel 2003 to make sure I'm reporting
the behavior accurately.) The debugger in Excel 2003 shows a Value2
property, but no Value property, although obviously it is there in
some form because the remaining references to Value work.
I still think the Excel 2003 interpreter is in error for objecting to
syntax that is not incorrect.

That said, I think I can see where iterating through the Areas
collection could make a simpler loop. I wasn't using the Areas
collection when I started writing this macro because I didn't know
how to use it then. I'll let you know how it works out.

"keepITcool" wrote:


the bug is not a bug.. imo your syntax is erroneous..


'Make next name with this permutation
Call addsingle(xlPnumRange.Value2(lPnumIndex, 1), _
strFormatSpec, strTcId & strTestType)


already part of some 'cumbersome' code to loop a multiarea range
this will very likely go bust..

if xlPnumRange is a 1 row, multicolumn range (may be 1 area of your
entire selection) the Value or Value2 of that range will be
returned as a 1 dimensional array.

dim v(1) = debug.print v(1,1) will produce an error.

Thus you might use:
Call addsingle(xlPnumRange.Cells(lPnumIndex, 1).Value, _
strFormatSpec, strTcId & strTestType)

Note that when enumerating ROWS inside a range you cannot abbreviate
getting the first cell of the row range like xlRow(1,1).. you must
use xlRow.Cells(1,1). To avoid confusion avoid the abbreviated
syntax altogether.


But even then I'd code your entire for each differently.

After your loop:

'Get selected cells
Set xlPnumRange = Selection

Dim xlArea As Range, xlRow As Range
For Each xlArea In xlPnumRange.Areas
For Each xlRow In xlArea.Rows
'Get good format spec
strFormatSpec = adjustformat(xlRow)
'Make next name with this permutation
Call addsingle(xlRow.Cells(1,1).Value, _
strFormatSpec, _
strTcId & strTestType)
Next
Next

Can't test but that should do the same as your code.


HTH

--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


LabElf wrote :

OK, for anyone who is interested, here is the code for the AddTest
macro. It calls some other macros, and makes some assumptions
about the worksheet it is called from, but I think it should be
fairly clear.


SNIP


"keepITcool" wrote:

post your code, so we may (dis)agree or advise

--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam

Thank you for the example of using for each row in Rows within for each range
in Areas. I used the code almost exactly as you supplied it, and it makes my
AddTest macro much simpler, handling single, contiguous and disjoint
selections equally well, and avoids problems with the Value subscript by not
using it.

I tested the old version of the code in Excel 2003 with a selection
containing an area with 1 row and 3 columns. It didn't fail. I also
inserted your example using the MsgBox function and my xlPnumRange object
from the selection. Using Value(1,1) it gave error "Compile error: Wrong
number of arguments or invalid property assignment". Using Value2(1,1) it
displayed the specified data in the box. I still say the Excel 2003
interpreter shouldn't be giving that error for the Value property.

Thanks again for your help.

"keepITcool" wrote:



your code will stop if any area in your collections happens to
have 1 row and 2 or more columns.

MsgBox Range("a1:e1").Value2(1, 1)

will gen error number 9: subscript out of range
in ANY excel version.

THIS is much more logical and will NOT generate an error.
MsgBox Range("a1:e1").Cells(1,1).Value2



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


LabElf wrote :

Thank you, keepITcool, I will try your suggestions; however, I would
like to clarify some facts:
The code I posted works correctly in both Excel 2000 and Excel 2003.
The syntax is not erroneous.
The code is cumbersome because the user may have selected a single
cell, a range of contiguous cells, or a disjoint selection containing
individual cells or several contiguous selections (or both). The
structure of range object for the selection is different in each of
these cases. (But see my final paragraph below.)
If the range contains a contiguous group of rows of one column each,
the Value2 property does not contain a one-dimensional array, but
rather a two-dimensional array. The second subscript range is 1 to
1. (I checked this again in Excel 2003 to make sure I'm reporting
the behavior accurately.) The debugger in Excel 2003 shows a Value2
property, but no Value property, although obviously it is there in
some form because the remaining references to Value work.
I still think the Excel 2003 interpreter is in error for objecting to
syntax that is not incorrect.

That said, I think I can see where iterating through the Areas
collection could make a simpler loop. I wasn't using the Areas
collection when I started writing this macro because I didn't know
how to use it then. I'll let you know how it works out.

"keepITcool" wrote:


the bug is not a bug.. imo your syntax is erroneous..


'Make next name with this permutation
Call addsingle(xlPnumRange.Value2(lPnumIndex, 1), _
strFormatSpec, strTcId & strTestType)


already part of some 'cumbersome' code to loop a multiarea range
this will very likely go bust..

if xlPnumRange is a 1 row, multicolumn range (may be 1 area of your
entire selection) the Value or Value2 of that range will be
returned as a 1 dimensional array.

dim v(1) = debug.print v(1,1) will produce an error.

Thus you might use:
Call addsingle(xlPnumRange.Cells(lPnumIndex, 1).Value, _
strFormatSpec, strTcId & strTestType)

Note that when enumerating ROWS inside a range you cannot abbreviate
getting the first cell of the row range like xlRow(1,1).. you must
use xlRow.Cells(1,1). To avoid confusion avoid the abbreviated
syntax altogether.


But even then I'd code your entire for each differently.

After your loop:

'Get selected cells
Set xlPnumRange = Selection

Dim xlArea As Range, xlRow As Range
For Each xlArea In xlPnumRange.Areas
For Each xlRow In xlArea.Rows
'Get good format spec
strFormatSpec = adjustformat(xlRow)
'Make next name with this permutation
Call addsingle(xlRow.Cells(1,1).Value, _
strFormatSpec, _
strTcId & strTestType)
Next
Next

Can't test but that should do the same as your code.


HTH

--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


LabElf wrote :

OK, for anyone who is interested, here is the code for the AddTest
macro. It calls some other macros, and makes some assumptions
about the worksheet it is called from, but I think it should be
fairly clear.


SNIP


"keepITcool" wrote:

post your code, so we may (dis)agree or advise

--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam