array values type mismatch?

B

bwreath

Hi,

I would appreciate help with the following.
It's probably something simple but it doesn't make sense
to me.

This line gives me the error

ActiveWorkbook.Worksheets("Sheet1").Cells(y + 1, x + 1).Value
myarray(y, x)


Variable uses an automation type not supported in visual basic.
I get the same error when I trying to do

msgbox myarray(y, x) & " "

I think it has something to do with putting two different types
together but I'm not too sure.

thanks
 
T

Tom Ogilvy

rw = Ubound(myarray,1) - Lbound(myArray,1) + 1
col = Ubound(myArray,2) - Lbound(myArray,2) + 1

ActiveWorkbook.Worksheets("Sheet1") _
.Cells(y + 1, x + 1).Resize(rw,col).Value =
myarray(y, x)
 
B

bwreath

thanks for the suggestion.

I don't think it has to do with the bounds of the array.

I tried the suggested code and I got a type mismatch.

If I do

msgbox myarray(y, x) & " "
I get my original automation error.

If I do msgbox myarray(y, x) without the concatentation of the
string I don't get the error.

I also get the error if I try to something like
mystring = myarray(y, x)

thanks
 
T

Tom Ogilvy

You are correct - I misread your code and missed that you were only
assigning a single element of the array.

I can't image what would cause type mismatch.

for example:

Sub Tester9()
Dim myArray(1 To 5, 1 To 5)
For i = 1 To 5
For j = 1 To 5
myArray(i, j) = IIf(Rnd < 0.3, CVErr(xlErrNA), _
IIf(Rnd < 0.5, i * i, Chr(Int(Rnd * 26 + 65))))
Next
Next
For i = 1 To 5
For j = 1 To 5
sStr = sStr & "myArray(" & i & _
", " & j & ")=" & CStr(myArray(i, j)) & vbTab
Next
sStr = sStr & vbNewLine
Next
Debug.Print sStr


End Sub

worked fine for me.

Maybe your code is doing something that corrupts memory where the array is
stored.
 
B

bwreath

Hi,

I thought it would help if I posted my code so you could see the
context of the problem.
Maybe it has something to do with types and recordsets?

thanks very much

Sub main()

Dim genarray As Variant
Dim introws As Integer

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

conn.CursorLocation = adUseClient
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=123.456.78.90;" _
& "DATABASE=mydatabase;" _
& "UID=myuser;" _
& "PWD=mypasswd;" _
'& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384

conn.Open

rs.Open "SELECT table1.an_id, table1.name, table1.definition FRO
mydatabase.table1 ORDER BY table1.name", conn, adOpenStatic
adLockOptimistic


introws = rs.RecordCount
MsgBox "this is the number of rows in the record count: "
introws

genarray = rs.GetRows(introws)

Dim x As Integer 'row of the recordset
Dim y As Integer 'index into the row

For x = 0 To introws - 1
For y = 0 To 1

'ERROR occurs on this line
ActiveWorkbook.Worksheets("Sheet1").Cells(2, 2).Value =
genarray(y, x)

'Error does not occur on this line.
MsgBox genarray(y, x)
'ERROR OCCURS on this line
'MsgBox genarray(0, 0) & " "

Next y

Next x


rs.Close
Set rs = Nothing

conn.Close
Set conn = Nothing
MsgBox done


End Su
 
T

Tom Ogilvy

I don't see anything obvious. I think it would depend on what is stored in
the array when the error occurs. I would guess that whatever is stored in
the array element when the error occurs is something that causes VBA to
choke on the assignment.

If it isn't something that causes a problem, you might just handle the error

on error resume next
' code that might error
on error goto 0
 
J

Jamie Collins

Tom Ogilvy said:
I don't see anything obvious. I think it would depend on what is stored in
the array when the error occurs. I would guess that whatever is stored in
the array element when the error occurs is something that causes VBA to
choke on the assignment.
Click to expand...

I'm intrigued. What *is* in this array? e.g.

MsgBox TypeName(genarray(0, 0))
MsgBox rs.Fields(0).Type

Jamie.

--
 
Top