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

Error passing Arrays to Function

I'm trying to write a generic stored procedure in vba and pass in parameters as variables

Code snippet:
Public Function RunSP(StoredProcedureName As String, ParamString() As String, DataType() As DataTypeEnum, ParamValue() As Variant)


In the immediate window I have:
? runsp "ElementInRange",("Element","Amount","Material"),(18,3,18),("Cr",22,"2205")

I put the breakpoint on the Function declaration line

It compiles correctly but when I attempt to run it I get
Compile Error  Expected: )  (closing parenthesis)

no clue as to where the missing parenthesis belongs
0
Kurt Bergman
Asked:
Kurt Bergman
  • 3
  • 3
  • 2
2 Solutions
 
NorieData ProcessorCommented:
Try this.
? runsp "ElementInRange",Array("Element","Amount","Material"),Array(18,3,18),Array("Cr",22,"2205")

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
or this

?runsp("ElementInRange",Array("Element","Amount","Material"),Array(18,3,18),Array("Cr",22,"2205"))
0
 
Kurt BergmanAuthor Commented:
Okay... tried that
Now I'm getting 'Argument Not Optional'
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Kurt BergmanAuthor Commented:
Hy Rey,
?runsp("ElementInRange",Array("Element","Amount","Material"),Array(18,3,18),Array("Cr",22,"2205"))

returns: Type mismatch: array or user-defined type expected
0
 
NorieData ProcessorCommented:
Try changing the function declaration to this.
Public Function RunSP(StoredProcedureName As String, ParamString As Variant, DataType As Variant, ParamValue As Variant)

Open in new window

Also, to test use a small sub - somethings you can't do in the Immediate Wndow.

Actually just realised that enclosing the arguments in brackets, as suggested by Rey, will work.
0
 
Rey Obrero (Capricorn1)Commented:
Public Function RunSP(StoredProcedureName As String, ParamString As Variant, DataType As Variant, ParamValue As Variant)


then use

?runsp("ElementInRange",Array("Element","Amount","Material"),Array(18,3,18),Array("Cr,22,2205"))
0
 
Rey Obrero (Capricorn1)Commented:
or

?runsp("ElementInRange",Array("Element","Amount","Material"),Array(18,3,18),Array("Cr",22,"2205"))
0
 
Kurt BergmanAuthor Commented:
?runsp("ElementInRange",Array("Element","Amount","Material"),Array(18,3,18),Array("Cr",22,"2205")) 

Open in new window

and
?runsp("ElementInRange",Array("Element","Amount","Material"),Array(18,3,18),Array("Cr,22,2205"))

Open in new window



Both work

It's now hanging up here:
ADOCmd.Parameters(ParamString(0)).Value = ParamValue(0)

Open in new window



Error 3421 (Application uses a value of the wrong type for the current operation.)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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