Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
array values type mismatch?
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
array values type mismatch?
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) -- Regards, Tom Ogilvy "bwreath " wrote in message ... 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. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
array values type mismatch?
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
array values type mismatch?
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. -- Regards, Tom Ogilvy "bwreath " wrote in message ... 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. --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
array values type mismatch?
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 -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
array values type mismatch?
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 -- Regards, Tom Ogilvy "bwreath " wrote in message ... 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 FROM 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 Sub --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
array values type mismatch?
"Tom Ogilvy" wrote ...
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. I'm intrigued. What *is* in this array? e.g. MsgBox TypeName(genarray(0, 0)) MsgBox rs.Fields(0).Type Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Type mismatch" when I try to fill an Array variable with "+" | Excel Discussion (Misc queries) | |||
other type values in array. | Excel Programming | |||
Variant Array with String Values - Type Mismatch | Excel Programming | |||
setting ctl to array of checkboxes yields type mismatch error. | Excel Programming | |||
setting ctl to array of checkboxes yields type mismatch error. | Excel Programming |