Too many local, nonstatic variables error in VBA 2003?

Question

Hi I'm getting compile error in VBA Excel 2003 for the insert query for MS access database.How to overcome this error?

Public Function InsertSheetCurrent()
    Static intK As Integer
    Call clsadodb.ConnecttoDatabase
    objRSet.CursorType = adOpenStatic

    For intK = 0 To WSCurrentHCV.Range("A10:HN223").Rows.Count - 1
        clsadodb.objCon.Execute "insert into tblCurrentHCV(fldVersion,fldCountry,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220)" & _
        " values('" & WSCurrentHCV.Cells(intK + 10, 1).Text & "','" & WSCurrentHCV.Cells(intK + 10, 2).Text & "','" & WSCurrentHCV.Cells(intK + 10, 3) & "','" & WSCurrentHCV.Cells(intK + 10, 4).Text & "','" & WSCurrentHCV.Cells(intK + 10, 5).Text & "','" & WSCurrentHCV.Cells(intK + 10, 6).Text & "','" & WSCurrentHCV.Cells(intK + 10, 7).Text & "','" & WSCurrentHCV.Cells(intK + 10, 8).Text & "','" & WSCurrentHCV.Cells(intK + 10, 9).Text & "','" & WSCurrentHCV.Cells(intK + 10, 10).Text & "','" & WSCurrentHCV.Cells(intK + 10, 11).Text & "','" & WSCurrentHCV.Cells(intK + 10, 12).Text & "','" & WSCurrentHCV.Cells(intK + 10, 13).Text & "','" & WSCurrentHCV.Cells(intK + 10, 14).Text & "'," & _
        " '" & WSCurrentHCV.Cells(intK + 10, 15).Text & "','" & WSCurrentHCV.Cells(intK + 10, 16).Text & "','" & WSCurrentHCV.Cells(intK + 10, 17).Text & "','" & WSCurrentHCV.Cells(intK + 10, 18).Text & "','" & WSCurrentHCV.Cells(intK + 10, 19).Text & "','" & WSCurrentHCV.Cells(intK + 10, 20).Text & "','" & WSCurrentHCV.Cells(intK + 10, 21).Text & "','" & WSCurrentHCV.Cells(intK + 10, 22).Text & "','" & WSCurrentHCV.Cells(intK + 10, 23).Text & "','" & WSCurrentHCV.Cells(intK + 10, 24).Text & "','" & WSCurrentHCV.Cells(intK + 10, 25).Text & "','" & WSCurrentHCV.Cells(intK + 10, 26).Text & "','" & WSCurrentHCV.Cells(intK + 10, 27).Text & "','" & WSCurrentHCV.Cells(intK + 10, 28).Text & "'," & _
        " '" & WSCurrentHCV.Cells(intK + 10, 29).Text & "','" & WSCurrentHCV.Cells(intK + 10, 30).Text & "','" & WSCurrentHCV.Cells(intK + 10, 31).Text & "','" & WSCurrentHCV.Cells(intK + 10, 32).Text & "','" & WSCurrentHCV.Cells(intK + 10, 33).Text & "','" & WSCurrentHCV.Cells(intK + 10, 34).Text & "','" & WSCurrentHCV.Cells(intK + 10, 35).Text & "','" & WSCurrentHCV.Cells(intK + 10, 36).Text & "','" & WSCurrentHCV.Cells(intK + 10, 37).Text & "','" & WSCurrentHCV.Cells(intK + 10, 38).Text & "','" & WSCurrentHCV.Cells(intK + 10, 39).Text & "','" & WSCurrentHCV.Cells(intK + 10, 40).Text & "','" & WSCurrentHCV.Cells(intK + 10, 41).Text & "','" & WSCurrentHCV.Cells(intK + 10, 42).Text & "'," & _
        " '" & WSCurrentHCV.Cells(intK + 10, 43).Text & "','" & WSCurrentHCV.Cells(intK + 10, 44).Text & "','" & WSCurrentHCV.Cells(intK + 10, 45).Text & "','" & WSCurrentHCV.Cells(intK + 10, 46).Text & "','" & WSCurrentHCV.Cells(intK + 10, 47).Text & "','" & WSCurrentHCV.Cells(intK + 10, 48).Text & "','" & WSCurrentHCV.Cells(intK + 10, 49).Text & "','" & WSCurrentHCV.Cells(intK + 10, 50).Text & "','" & WSCurrentHCV.Cells(intK + 10, 51).Text & "','" & WSCurrentHCV.Cells(intK + 10, 52).Text & "','" & WSCurrentHCV.Cells(intK + 10, 53).Text & "','" & WSCurrentHCV.Cells(intK + 10, 54).Text & "','" & WSCurrentHCV.Cells(intK + 10, 55).Text & "','" & WSCurrentHCV.Cells(intK + 10, 56).Text & "'," & _
        " '" & WSCurrentHCV.Cells(intK + 10, 57).Text & "','" & WSCurrentHCV.Cells(intK + 10, 58).Text & "','" & WSCurrentHCV.Cells(intK + 10, 59).Text & "','" & WSCurrentHCV.Cells(intK + 10, 60).Text & "','" & WSCurrentHCV.Cells(intK + 10, 61).Text & "','" & WSCurrentHCV.Cells(intK + 10, 62).Text & "','" & WSCurrentHCV.Cells(intK + 10, 63).Text & "','" & WSCurrentHCV.Cells(intK + 10, 64).Text & "','" & WSCurrentHCV.Cells(intK + 10, 65).Text & "','" & WSCurrentHCV.Cells(intK + 10, 66).Text & "','" & WSCurrentHCV.Cells(intK + 10, 67).Text & "','" & WSCurrentHCV.Cells(intK + 10, 68).Text & "','" & WSCurrentHCV.Cells(intK + 10, 69).Text & "','" & WSCurrentHCV.Cells(intK + 10, 70).Text & "'," & _
        " '" & WSCurrentHCV.Cells(intK + 10, 71).Text & "','" & WSCurrentHCV.Cells(intK + 10, 72).Text & "','" & WSCurrentHCV.Cells(intK + 10, 73).Text & "','" & WSCurrentHCV.Cells(intK + 10, 74).Text & "','" & WSCurrentHCV.Cells(intK + 10, 75).Text & "','" & WSCurrentHCV.Cells(intK + 10, 76).Text & "','" & WSCurrentHCV.Cells(intK + 10, 77).Text & "','" & WSCurrentHCV.Cells(intK + 10, 78).Text & "','" & WSCurrentHCV.Cells(intK + 10, 79).Text & "','" & WSCurrentHCV.Cells(intK + 10, 80).Text & "','" & WSCurrentHCV.Cells(intK + 10, 81).Text & "','" & WSCurrentHCV.Cells(intK + 10, 82).Text & "','" & WSCurrentHCV.Cells(intK + 10, 83).Text & "','" & WSCurrentHCV.Cells(intK + 10, 84).Text & "'," & _
        " '" & WSCurrentHCV.Cells(intK + 10, 85).Text & "','" & WSCurrentHCV.Cells(intK + 10, 86).Text & "','" & WSCurrentHCV.Cells(intK + 10, 87).Text & "','" & WSCurrentHCV.Cells(intK + 10, 88).Text & "','" & WSCurrentHCV.Cells(intK + 10, 89).Text & "','" & WSCurrentHCV.Cells(intK + 10, 90).Text & "','" & WSCurrentHCV.Cells(intK + 10, 91).Text & "','" & WSCurrentHCV.Cells(intK + 10, 92).Text & "','" & WSCurrentHCV.Cells(intK + 10, 93).Text & "','" & WSCurrentHCV.Cells(intK + 10, 94).Text & "','" & WSCurrentHCV.Cells(intK + 10, 95).Text & "','" & WSCurrentHCV.Cells(intK + 10, 96).Text & "','" & WSCurrentHCV.Cells(intK + 10, 97).Text & "','" & WSCurrentHCV.Cells(intK + 10, 98).Text & "'," & _
        " '" & WSCurrentHCV.Cells(intK + 10, 99).Text & "','" & WSCurrentHCV.Cells(intK + 10, 100).Text & "','" & WSCurrentHCV.Cells(intK + 10, 101).Text & "','" & WSCurrentHCV.Cells(intK + 10, 102).Text & "','" & WSCurrentHCV.Cells(intK + 10, 103).Text & "','" & WSCurrentHCV.Cells(intK + 10, 104).Text & "','" & WSCurrentHCV.Cells(intK + 10, 105).Text & "','" & WSCurrentHCV.Cells(intK + 10, 106).Text & "','" & WSCurrentHCV.Cells(intK + 10, 107).Text & "','" & WSCurrentHCV.Cells(intK + 10, 108).Text & "','" & WSCurrentHCV.Cells(intK + 10, 109).Text & "','" & WSCurrentHCV.Cells(intK + 10, 110).Text & "','" & WSCurrentHCV.Cells(intK + 10, 111).Text & "','" & WSCurrentHCV.Cells(intK + 10, 112).Text & "'," & _
        " '" & WSCurrentHCV.Cells(intK + 10, 113).Text & "','" & WSCurrentHCV.Cells(intK + 10, 114).Text & "','" & WSCurrentHCV.Cells(intK + 10, 115).Text & "','" & WSCurrentHCV.Cells(intK + 10, 116).Text & "','" & WSCurrentHCV.Cells(intK + 10, 117).Text & "','" & WSCurrentHCV.Cells(intK + 10, 118).Text & "','" & WSCurrentHCV.Cells(intK + 10, 119).Text & "','" & WSCurrentHCV.Cells(intK + 10, 120).Text & "','" & WSCurrentHCV.Cells(intK + 10, 121).Text & "','" & WSCurrentHCV.Cells(intK + 10, 122).Text & "','" & WSCurrentHCV.Cells(intK + 10, 123).Text & "','" & WSCurrentHCV.Cells(intK + 10, 124).Text & "','" & WSCurrentHCV.Cells(intK + 10, 125).Text & "','" & WSCurrentHCV.Cells(intK + 10, 126).Text & "'," & _
        " '" & WSCurrentHCV.Cells(intK + 10, 127).Text & "','" & WSCurrentHCV.Cells(intK + 10, 128).Text & "','" & WSCurrentHCV.Cells(intK + 10, 129).Text & "','" & WSCurrentHCV.Cells(intK + 10, 130).Text & "','" & WSCurrentHCV.Cells(intK + 10, 131).Text & "','" & WSCurrentHCV.Cells(intK + 10, 132).Text & "','" & WSCurrentHCV.Cells(intK + 10, 133).Text & "','" & WSCurrentHCV.Cells(intK + 10, 134).Text & "','" & WSCurrentHCV.Cells(intK + 10, 135).Text & "','" & WSCurrentHCV.Cells(intK + 10, 136).Text & "','" & WSCurrentHCV.Cells(intK + 10, 137).Text & "','" & WSCurrentHCV.Cells(intK + 10, 138).Text & "','" & WSCurrentHCV.Cells(intK + 10, 139).Text & "','" & WSCurrentHCV.Cells(intK + 10, 140).Text & "'," & _
        " '" & WSCurrentHCV.Cells(intK + 10, 141).Text & "','" & WSCurrentHCV.Cells(intK + 10, 142).Text & "','" & WSCurrentHCV.Cells(intK + 10, 143).Text & "','" & WSCurrentHCV.Cells(intK + 10, 144).Text & "','" & WSCurrentHCV.Cells(intK + 10, 145).Text & "','" & WSCurrentHCV.Cells(intK + 10, 146).Text & "','" & WSCurrentHCV.Cells(intK + 10, 147).Text & "','" & WSCurrentHCV.Cells(intK + 10, 148).Text & "','" & WSCurrentHCV.Cells(intK + 10, 149).Text & "','" & WSCurrentHCV.Cells(intK + 10, 150).Text & "','" & WSCurrentHCV.Cells(intK + 10, 151).Text & "','" & WSCurrentHCV.Cells(intK + 10, 152).Text & "','" & WSCurrentHCV.Cells(intK + 10, 153).Text & "','" & WSCurrentHCV.Cells(intK + 10, 154).Text & "'," & _
        " '" & WSCurrentHCV.Cells(intK + 10, 155).Text & "','" & WSCurrentHCV.Cells(intK + 10, 156).Text & "','" & WSCurrentHCV.Cells(intK + 10, 157).Text & "','" & WSCurrentHCV.Cells(intK + 10, 158).Text & "','" & WSCurrentHCV.Cells(intK + 10, 159).Text & "','" & WSCurrentHCV.Cells(intK + 10, 160).Text & "','" & WSCurrentHCV.Cells(intK + 10, 161).Text & "','" & WSCurrentHCV.Cells(intK + 10, 162).Text & "','" & WSCurrentHCV.Cells(intK + 10, 163).Text & "','" & WSCurrentHCV.Cells(intK + 10, 164).Text & "','" & WSCurrentHCV.Cells(intK + 10, 165).Text & "','" & WSCurrentHCV.Cells(intK + 10, 166).Text & "','" & WSCurrentHCV.Cells(intK + 10, 167).Text & "','" & WSCurrentHCV.Cells(intK + 10, 168).Text & "'," & _
        " '" & WSCurrentHCV.Cells(intK + 10, 169).Text & "','" & WSCurrentHCV.Cells(intK + 10, 170).Text & "','" & WSCurrentHCV.Cells(intK + 10, 171).Text & "','" & WSCurrentHCV.Cells(intK + 10, 172).Text & "','" & WSCurrentHCV.Cells(intK + 10, 173).Text & "','" & WSCurrentHCV.Cells(intK + 10, 174).Text & "','" & WSCurrentHCV.Cells(intK + 10, 175).Text & "','" & WSCurrentHCV.Cells(intK + 10, 176).Text & "','" & WSCurrentHCV.Cells(intK + 10, 177).Text & "','" & WSCurrentHCV.Cells(intK + 10, 178).Text & "','" & WSCurrentHCV.Cells(intK + 10, 179).Text & "','" & WSCurrentHCV.Cells(intK + 10, 180).Text & "','" & WSCurrentHCV.Cells(intK + 10, 181).Text & "'," & _
        " '" & WSCurrentHCV.Cells(intK + 10, 182).Text & "','" & WSCurrentHCV.Cells(intK + 10, 183).Text & "','" & WSCurrentHCV.Cells(intK + 10, 184).Text & "','" & WSCurrentHCV.Cells(intK + 10, 185).Text & "','" & WSCurrentHCV.Cells(intK + 10, 186).Text & "','" & WSCurrentHCV.Cells(intK + 10, 187).Text & "','" & WSCurrentHCV.Cells(intK + 10, 188).Text & "','" & WSCurrentHCV.Cells(intK + 10, 189).Text & "','" & WSCurrentHCV.Cells(intK + 10, 190).Text & "','" & WSCurrentHCV.Cells(intK + 10, 191).Text & "','" & WSCurrentHCV.Cells(intK + 10, 192).Text & "','" & WSCurrentHCV.Cells(intK + 10, 193).Text & "','" & WSCurrentHCV.Cells(intK + 10, 194).Text & "','" & WSCurrentHCV.Cells(intK + 10, 195).Text & "'," & _
        " '" & WSCurrentHCV.Cells(intK + 10, 196).Text & "','" & WSCurrentHCV.Cells(intK + 10, 197).Text & "','" & WSCurrentHCV.Cells(intK + 10, 198).Text & "','" & WSCurrentHCV.Cells(intK + 10, 199).Text & "','" & WSCurrentHCV.Cells(intK + 10, 200).Text & "','" & WSCurrentHCV.Cells(intK + 10, 201).Text & "','" & WSCurrentHCV.Cells(intK + 10, 202).Text & "','" & WSCurrentHCV.Cells(intK + 10, 203).Text & "','" & WSCurrentHCV.Cells(intK + 10, 204).Text & "','" & WSCurrentHCV.Cells(intK + 10, 205).Text & "','" & WSCurrentHCV.Cells(intK + 10, 206).Text & "','" & WSCurrentHCV.Cells(intK + 10, 207).Text & "','" & WSCurrentHCV.Cells(intK + 10, 208).Text & "','" & WSCurrentHCV.Cells(intK + 10, 209).Text & "'," & _
        " '" & WSCurrentHCV.Cells(intK + 10, 210).Text & "','" & WSCurrentHCV.Cells(intK + 10, 211).Text & "','" & WSCurrentHCV.Cells(intK + 10, 212).Text & "','" & WSCurrentHCV.Cells(intK + 10, 213).Text & "','" & WSCurrentHCV.Cells(intK + 10, 214).Text & "','" & WSCurrentHCV.Cells(intK + 10, 215).Text & "','" & WSCurrentHCV.Cells(intK + 10, 216).Text & "','" & WSCurrentHCV.Cells(intK + 10, 217).Text & "','" & WSCurrentHCV.Cells(intK + 10, 218).Text & "','" & WSCurrentHCV.Cells(intK + 10, 219).Text & "','" & WSCurrentHCV.Cells(intK + 10, 220).Text & "','" & WSCurrentHCV.Cells(intK + 10, 221).Text & "','" & WSCurrentHCV.Cells(intK + 10, 222).Text & "')"
    Next intK

    If clsadodb.objCon.State = adStateOpen Then clsadodb.objCon.Close
    If objRSet.State = adStateOpen Then objRSet.Close
End Function

Why do you have so many columns? It looks like it could be caused by referring to WSCurrentHCV.Cells(1,1).Text so many times. — Zaider

MattCrum MattCrum · Accepted Answer · 2013-03-28T14:40:28

Create a string variable and loop through the WSCurrentHCV.Cells(intK + 10, i).Text fields to populate it, rather than spelling them all out:

Dim strTemp as String, i as Integer
strTemp = ""
For i = 1 to 222
    strTemp = strTemp & WSCurrentHCV.Cells(intK + 10, i).Text & ","
Next
    'Remove trailing ","
    strTemp = Left(strTemp, len(strTemp)-1)

Once you've built the string, you can replace all of the repetitions: " values('" & strTemp & "')"

Too many local, nonstatic variables error in VBA 2003?

1 Answers