• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

Passing an Array Dimension to Sub

I want to pass an array dimension via a parameter.  I want a single sub to work with calls in which the array dimension will change.  I get a "constant expression required" error when I try to call this:
rivate Sub SingleRcrd(ByRef iArrayBound As Integer, ByVal ThisSql As String)
    'On Error GoTo ErrorHandler
    
    Dim rs As ADODB.Recordset
    Dim i As Integer
    Dim UploadArr(iArrayBound)
    
    Set rs = New ADODB.Recordset
    rs.Open ThisSql, conn, 1, 2
    For i = 0 To iArrayBound
        UploadArr(i) = rs(i).Value
    Next i
    rs.Close
    Set rs = Nothing
    
    Set rs = New ADODB.Recordset
    rs.Open ThisSql, srvr_conn, 1, 2
    For i = 0 To iArrayBound
        rs(i).Value = UploadArr(i)
    Next i
    rs.Update
    rs.Close
    Set rs = Nothing
    
    Exit Sub
ErrorHandler:
    Beep
    ErrorHandler
End Sub

Open in new window


with this:
    sql = "SELECT EventName, EventDate, Club, Website, Weather, Comments, FeeIncrDate, TimingMethod, EventDirID, ShowOnline, "
    sql = sql & "OnlineReg, WhenShutdown, FeeIncrDate FROM Events WHERE EventID = " & lEventID
    Call SingleRcrd(12, sql)

Open in new window


What am I doing wrong?
0
Bob Schneider
Asked:
Bob Schneider
3 Solutions
 
Paul JacksonCommented:
I suspect it is because you are passing the parameter byref and then using it to try and define an array which I suspect is where the error is occurring. Can't see why you need to pass byref so do you get the same error if you pass the parameter byval.
0
 
BanthorCommented:
use REDIM instead of DIM for the array, this tells the compiler that the dimension can be altered.

Another option is to use an xml object
0
 
advfinanceCommented:
Like Banthor says, use Dim, then ReDim. Change line 6 from

Dim UploadArr(iArrayBound)

Open in new window


to

Dim UploadArr()
ReDim UploadArr(iArrayBound)

Open in new window


--
Chris
0
 
Bob SchneiderAuthor Commented:
Thanks a bunch!
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now