Type Mismatch when Dim for multi-dimensional array is Variant

[polarisking]

but it works, if I change to "as String".

I have no idea why. The array is 5 x 2834. The first vector is always a number, the remaining 4 are often filled with text, sometimes blanks (not nulls, blanks.)

Any ideas would be very much appreciated.

 

[Smallman]

Polarisking

This is possibly the most cryptic message I have read in a while. You are not new to Chandoo and must see that posting Code and file but preferably both results in swift and specific responses.

Please upload a file with the code for the best results at Chandoo forum.

Take care

Smallman

 

[SirJB7]

Hi, polarisking!

Just as an example, give a look at the uploaded file.

Worksheet Hoja1 has range A1: D20 filled, column A as number, B as string, C as date, D as boolean . This is the code which works for me:

Option Explicit

Sub ThereAreALotOfBadTennisPlayers()
    ' constants
    ' declarations
    Dim ThatArray() As Variant
    Dim I As Integer, J As Integer
    ' start
    ' process
    With Worksheets("Hoja1")
        ThatArray = Range(.[A1], .[A1].End(xlToRight).End(xlDown))
    End With
    Debug.Print "Row", "Column", "Value"
    For I = 1 To UBound(ThatArray, 1)
        For J = 1 To UBound(ThatArray, 2)
            Debug.Print I, J, ThatArray(I, J)
        Next J
    Next I
    ' end
End Sub

Does it help? Otherwise you yet know the routine: prepare file, upload & so on.

Regards!

 

[polarisking]

Thanks to all for responding. After pounding my head against the wall, here's what I discovered:

What I presumed to be the problem, actually wasn't. I was attempting to Transpose roughly 2,400 rows and 5 columns. Easy, right? Well, the native "move" of the array into the spreadsheet worked fine, but the Transpose was breaking. I lopped off all but 10 rows, and the Transpose worked just fine. So, I figured I'd find the record that was creating the issue.

It has to with a cell having non-English characters mixed in with regular English-language ones. The conventional wisdom is that Excel returns a code=63 for characters above the 256 range. Makes sense because when I parsed out the offending characters, they yielded 63, but when I did char(63) I get ?. The cell contained email addresses from an Asian (Japanese) subsidiary. Once I "cleaned" the cell of the unprintable characters, the Transform worked perfectly. Here's a nice stub of code (citation http://windowssecrets.com/forums/sh...acro-to-Delete-Foreign-Characters-(Excel-2002)) that parses through a string and gets rid of all ASCII <127. Not sure why he chose 127, but it saved my bacon.

Sub RemoveOddChars()
    Dim oCell  As Range
    Dim strOld  As String
    Dim strNew  As String
    Dim i      As Integer
   
    For Each oCell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).Cells
        strOld = oCell
        strNew = ""
        For i = 1 To Len(strOld)
            If AscW(Mid(strOld, i, 1)) < 128 Then
                strNew = strNew & Mid(strOld, i, 1)
            End If
            oCell = strNew
        Next i
    Next oCell
 
End Sub

I cannot share the cell value causing the problem, as it contains actual email addresses. Any change I make to it, renders it harmless. The question I still have is why the Transform chokes on data, but not the regular move of the array into a range.

 

[SirJB7]

Hi, polarisking!

A full Unicode to UTF-8 table conversion can be found here:
http://www.utf8-chartable.de/

Building the whole range of equivalences may help to build functions that handle double-byte characters.

And regarding your question, I'm sorry but as not having dealt with DB alphabets apart from here:
http://chandoo.org/forum/threads/excel-multilanguage-formula-translator-and-function-reference.4789/
I'm unable to analyze the problem.

Regards!