Inventor and Excel

[TONELLAL]

Hello,

I use Excel and Inventor to manipulate properties. To have a more efficient code, I send an Excel area in a VBA array :

tab_Size1 = area_Size1.cells.value

where tab_Size1 is an array in Inventor VBA, and zone_Size1 is an area in Excel.

If I have an assembly containing several different parts, it's ok, the result is an array containing one line per part : 

But, if I have an assembly containing only 1 part, the result in Inventor is the value of the unique Excell cell, instead of an array containing only 1 value :

And so, because tab_Size1 is not an array, I can't use "for i=1 to ubound(tab_Size1)".

I can test the type of tab_Size1, to know if it is Array or not, and use 2 different treatments, but this is cheating... Is there a way to obtain in any case an array, even containing only 1 value ?

[WCrihfield]

It would be nice to see how you currently have your code laid out, to see how these variables & values are being declared,created,values set, and how they are being passed between any routines (Subs/Functions), etc.  This would give us a better idea of where to start from or how to suggest a more specific change in your existing code, to get the results you are wanting.  There are several ways to declare or create arrays, and several ways to set values to them, even multiple ways to pass them between different routines.  You can probably leave most of the 'guts' of the code out, if it's a long complex code, and just leave the most relevant stuff to this question in place.

[TONELLAL]

Here's a simplified code.  To use it, open the joined XL file then run the macro from Inventor.

Sub test()

Dim appXL As Excel.Application
Dim oArea1 As Excel.Range  'XL named area, containing 3 cells
Dim oArea2 As Excel.Range 'XL named area, containing 1 cell
Dim WB As Excel.Workbook

Set appXL = GetObject(, "Excel.application")
Set WB = appXL.ActiveWorkbook
Set oArea1 = WB.ActiveSheet.Range("Area1")
Set oArea2 = WB.ActiveSheet.Range("Area2")

Dim array1  'Not totally defined, because it can contain an array (for Area1) or a value(for Area2)

'**************************************
'Functionning because Area1 contains several lines

array1 = oArea1.Cells.Value

For i = 1 To UBound(array1)

'................

Next i
'**************************************

'**************************************
'Not functionning because Area2 contains only 1 line

array1 = oArea2.Cells.Value

For i = 1 To UBound(array1)

'................

Next i
'**************************************

End Sub

[WCrihfield · Accepted solution]

I'm not seeing a straight forward way to always have an Array type object to loop through, if there is only one cell in the specified Range, without maybe using a custom Function.  There is an Array function in VBA that can be used to help define an Array of values, but it is expecting a comma separated list of input values, not just one generic Object that might possibly represent an Array already.

The Range.Value is too dynamic.  It could return a single Object, a 1-dimensional array of Object, or a 2-dimensional array of Object, depending on how the Range was defined (one cell, multiple cells in a single row or column, or multiple cells in multiple rows & columns).  I don't think that simply using something like IsEmpty() or IsArray() would be considered 'cheating', because you've got to do whatever it takes to get the job done, without errors, but that's up to you.

If I know that a NamedRange  only contains a single cell, I just check its value directly, without using a loop.  If I don't know how many cells are in a Range, I would check the Range.Count.  If the count is more than 1, but I'm not sure if all the cells are from one Row or one Column, I would check the Range.Columns.Count and Range.Rows.Count properties.  Then I would likely prepare some code to deal with each type of situation in the needed manner.  It's just seems logical to me to handle those types of situations that way, to make sure the code is robust.

[TONELLAL · Accepted solution]

Thanks for your comments !

I think the simplest will be : 

If not IsArray(array1) then array1 = array(array1) 

'Now, I always obtain an array, and I can use :

For each XX in array1

'........

Next