Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Compile error Expected array 1

Status
Not open for further replies.
vba317

vba317

Programmer
Mar 5, 2009
708
US
When I compile I am getting an expected array error. I have declared the variables so I don't know why I would be getting this error. The line causing the error is highlighted in blue. I would greatly appreciate any help.


Code: 
'Module

Public Function GetDataTypeTF(D As Field, ByRef lTbl As Integer, ByRef lRow As Integer, ByRef lFld As Integer, intCol As Integer) As String
' **********************************************************************************
' *** THIS FUNCTION READS THE FIELDS FROM THE TABLE AND CREATES A REPORT OF IT******
' **********************************************************************************
    Dim dBase As DAO.Database
    Dim xlApp As Object
    Dim wbExcel As Object
    Dim fRow As Long
    Set dBase = CurrentDb
    Dim IndexExists As Boolean
    Dim IndexText As String
    Dim idx As DAO.Index
    Dim prp As DAO.Property
    Dim strIdx As DAO.Index
    Dim strCol As String
    Dim strTbl As String
    Dim strFld As String
    Dim ConvColLet As String
    Dim intColCnt As Integer
    With goXL.ActiveSheet
        .Range("A" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).SourceTable
        For intColCnt = 2 To intCol
 [Blue]           strCol = ConvColLet(intColCnt) [/blue]
            .Range(strCol & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Name
        Next intCol
    End With
End Function


'Other Function

Public Function ConvColLet(intCol As Integer) As String
' ******************************************************************************
' *** THIS FUNCTION RETURNS A LETTER FOR A COLUMN NUMBER FOR CELL REFERENCES ***
' ******************************************************************************
'strLeftLetter = ConvColLet(intFirstColumn)
Select Case intCol
    Case 1
        ConvColLet = "A"
    Case 2
        ConvColLet = "B"
    Case 3
        ConvColLet = "C"
    Case 4
        ConvColLet = "D"
    Case 5
        ConvColLet = "E"
    Case 6
        ConvColLet = "F"
    Case 7
        ConvColLet = "G"
    Case 8
        ConvColLet = "H"
    Case 9
        ConvColLet = "I"
    Case 10
        ConvColLet = "J"
    Case 11
        ConvColLet = "K"
    Case 12
        ConvColLet = "L"
    Case 13
        ConvColLet = "M"
    Case 14
        ConvColLet = "N"
    Case 15
        ConvColLet = "O"
    Case 16
        ConvColLet = "P"
    Case 17
        ConvColLet = "Q"
    Case 18
        ConvColLet = "R"
    Case 19
        ConvColLet = "S"
    Case 20
        ConvColLet = "T"
    Case 21
        ConvColLet = "U"
    Case 22
        ConvColLet = "V"
    Case 23
        ConvColLet = "W"
    Case 24
        ConvColLet = "X"
    Case 25
        ConvColLet = "Y"
    Case 26
        ConvColLet = "Z"
    Case 27
        ConvColLet = "AA"
    Case 28
        ConvColLet = "AB"
    Case 29
        ConvColLet = "AC"
    Case 30
        ConvColLet = "AD"
    Case 31
        ConvColLet = "AE"
    Case 32
        ConvColLet = "AF"
    Case 33
        ConvColLet = "AG"
    Case 34
        ConvColLet = "AH"
    Case 35
        ConvColLet = "AI"
    Case 36
        ConvColLet = "AJ"
    Case 37
        ConvColLet = "AK"
    Case 38
        ConvColLet = "AL"
    Case 39
        ConvColLet = "AM"
    Case 40
        ConvColLet = "AN"
    Case 41
        ConvColLet = "AO"
    Case 42
        ConvColLet = "AP"
    Case 43
        ConvColLet = "AQ"
    Case 44
        ConvColLet = "AR"
    Case 45
        ConvColLet = "AS"
    Case 46
        ConvColLet = "AT"
    Case 47
        ConvColLet = "AU"
    Case 48
        ConvColLet = "AV"
    Case 49
        ConvColLet = "AW"
    Case 50
        ConvColLet = "AX"
    Case 51
        ConvColLet = "AY"
    Case 52
        ConvColLet = "AZ"
    Case 53
        ConvColLet = "BA"
    Case 54
        ConvColLet = "BB"
    Case 55
        ConvColLet = "BC"
    Case 56
        ConvColLet = "BD"
    Case 57
        ConvColLet = "BE"
    Case 58
        ConvColLet = "BF"
    Case 59
        ConvColLet = "BG"
    Case 60
        ConvColLet = "BH"
    Case 61
        ConvColLet = "BI"
    Case 62
        ConvColLet = "BJ"
    Case 63
        ConvColLet = "BK"
    Case 64
        ConvColLet = "BL"
    Case 65
        ConvColLet = "BM"
    Case 66
        ConvColLet = "BN"
    Case 67
        ConvColLet = "BO"
    Case 68
        ConvColLet = "BP"
    Case 69
        ConvColLet = "BQ"
    Case 70
        ConvColLet = "BR"
    Case 71
        ConvColLet = "BS"
    Case 72
        ConvColLet = "BT"
    Case 73
        ConvColLet = "BU"
    Case 74
        ConvColLet = "BV"
    Case 75
        ConvColLet = "BW"
    Case 76
        ConvColLet = "BX"
    Case 77
        ConvColLet = "BY"
    Case 78
        ConvColLet = "BZ"
    Case 79
        ConvColLet = "CA"
    Case 80
        ConvColLet = "CB"
    Case 81
        ConvColLet = "CC"
    Case 82
        ConvColLet = "CD"
    Case 83
        ConvColLet = "CE"
    Case 84
        ConvColLet = "CF"
    Case 85
        ConvColLet = "CG"
    Case 86
        ConvColLet = "CH"
    Case 87
        ConvColLet = "CI"
    Case 88
        ConvColLet = "CJ"
    Case 89
        ConvColLet = "CK"
    Case 90
        ConvColLet = "CL"
    Case 91
        ConvColLet = "CM"
    Case 92
        ConvColLet = "CN"
    Case 93
        ConvColLet = "CO"
    Case 94
        ConvColLet = "CP"
    Case 95
        ConvColLet = "CQ"
    Case 96
        ConvColLet = "CR"
    Case 97
        ConvColLet = "CS"
    Case 98
        ConvColLet = "CT"
    Case 99
        ConvColLet = "CU"
    Case 100
        ConvColLet = "CV"
    Case Else
        ConvColLet = "CZ"
End Select
End Function
 
Sort by date Sort by votes
Sorry for the post. I just figured out I removed the Dim ConvColLet As String
statemant and everything works fine.

Tom
 
Upvote 0 Downvote
BTW: You could shorten your ConvColLet to:
Code: 
Public Function ConvColLet(intCol As Integer) As String
    Dim intFirstLetter As Integer
    Dim intLastLetter As Integer
    intFirstLetter = intCol \ 26
    intLastLetter = intCol Mod 26
    If intFirstLetter = 0 Then
        ConvColToLet = Chr(intLastLetter + 64)
      Else
        ConvColToLet = Chr(intFirstLetter + 64) & Chr(intLastLetter + 64)
    End If
End Function
You also have a memory variable with the same name as a function. I expect that's why you got the "array" error.

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
I might be wrong but I think you need to add this code to account for "z", because 26 mod 26 is 0 and 26\26 = 1.
...
intLastLetter = intCol Mod 26
If IntLastLetter = 0 Then
intFirstLetter = intFirstLetter - 1
intLastLetter = 26
End If
...
 
Upvote 0 Downvote
Good catch MajP. I discovered this in later testing and was working on a solution [blush]
Code: 
Public Function ConvColToLet(intCol As Integer) As String
    Dim intFirstLetter As Integer
    Dim intLastLetter As Integer
    intFirstLetter = intCol \ 26
    intLastLetter = intCol Mod 26
    If intLastLetter = 0 Then
        intFirstLetter = intFirstLetter - 1
        intLastLetter = 26
    End If
    If intFirstLetter = 0 Then
        ConvColToLet = Chr(intLastLetter + 64)
      Else
        ConvColToLet = Chr(intFirstLetter + 64) & Chr(intLastLetter + 64)
    End If
End Function

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
WHY? No need for ANY kind of column num to text conversion!!!
Code: 
        For intColCnt = 2 To intCol
            .Cells(lRow, strCol).Value = dBase.TableDefs(lTbl).Fields(lFld).Name
        Next intCol

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
oops...
Code: 
        For intColCnt = 2 To intCol
            .Cells(lRow, intColCnt).Value = dBase.TableDefs(lTbl).Fields(lFld).Name
            lFld = lFld + 1
        Next intCol
or...
Code: 
   intColCnt = 2
   for each fld in dBase.TableDefs(lTbl).Fields
      .Cells(lRow, intColCnt).Value = fld.name
         intColCnt = intColCnt + 1
   next


Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
I did have to add one field in for the top row . Thanks for the help.

Code: 
  lRow = 2
    fRow = 1
    'Loop through all tables
    For lTbl = 0 To dBase.TableDefs.Count
        intCol = 1
        For lFld = 0 To dBase.TableDefs(lTbl).Fields.Count - 1
            With goXL.ActiveSheet
                If fRow = 1 And intCol = 1 Then
                    .Range("A" & fRow) = "Table Name"
                    .Range("A" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).SourceTable
                    'intCol = 2
                    intColCnt = 2
                End If
                For Each Fld In dBase.TableDefs(lTbl).Fields
                    If fRow = 1 Then .Cells(1, intColCnt).Value = "Field Name"
                    .Cells(lRow, intColCnt).Value = Fld.Name
                    intColCnt = intColCnt + 1
                Next
            End With
        Next lFld
        lRow = lRow + 1
        Call xlFmtAutoFit(1, intColCnt)
    Next lTbl
 
Upvote 0 Downvote
The reason I leaped to that assumption, is because I often use ADO Data Objects to query external data sources from Excel. When I return the resultset, I first populate the field headings in row 1 of my sheet. Hence my folly.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Why looping 2 times thru the Fiels collection ?
Code: 
lRow = 2
With goXL.ActiveSheet
    .Range("A1") = "Table Name"
    'Loop through all tables
    For lTbl = 0 To dBase.TableDefs.Count
        For lFld = 0 To dBase.TableDefs(lTbl).Fields.Count - 1
            If lFld = 0 Then
                .Range("A" & lRow) = dBase.TableDefs(lTbl).Name
            End If
            .Cells(1, lFld + 1).Value = "Field Name"
            .Cells(lRow, lFld + 1).Value = dBase.TableDefs(lTbl).Fields(lFld).Name
        Next lFld
        lRow = lRow + 1
        Call xlFmtAutoFit(1, dBase.TableDefs(lTbl).Fields.Count)
    Next lTbl
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
It might look that way but I am not. The first statement .Cells(1, lFld + 1).Value = "Field Name"
Puts Field name in the top cell of every column that has a field in it.
The .Cells(lRow, lFld + 1).Value = dBase.TableDefs(lTbl).Fields(lFld).Name code puts the value from the table in the next column.

Tom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Question
TransferSpreadsheet 3125 Error
Replies
5
Views
566
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question
Find error in currency feild 2
Replies
8
Views
482
Andrzejek
Andrzejek
Fino
  • Locked
  • Question
Compile error on Office 365 desktop in 64 bit
Replies
7
Views
259
Fino
Fino
Kim296
  • Locked
  • Question
Sending Gmail using vba Send error
Replies
6
Views
531
strongm
strongm
Moss100
  • Locked
  • Question
Dlast not functioning as expected 1
Replies
12
Views
230
Moss100
Moss100

Part and Inventory Search

Sponsor

Back
Top