Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    ByRef Hell (VBA/Excel/2002)

    Is there a way to tell what a "ByRef" error is really about? I have been in DIM hell for a week!

    I have checked every line of code several times. Basically what I am doing is building a set of parameters for a stored procedure, then calling the proc to insert the data. Someone else in the group wrote and tested (with my data) the procedure.
    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ByRef Hell (VBA/Excel/2002)

    How is runSQLsp defined? What is SqlSPparams?

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ByRef Hell (VBA/Excel/2002)

    > Someone else in the group wrote and tested (with my data) the procedure.
    It works for them but not for you?

    What is the var type of the argument in the called procedure runSQLsp? Also an array of sqlSPparams? Same size?

  4. #4
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: ByRef Hell (VBA/Excel/2002)

    These were the "easy" things I tried first

    <pre>Public Function runSQLsp(thisSP As String, Action As String, Params() As SqlSPparams)</pre>

    -----------------------+--------------------------
    <pre> Public Type SqlSPparams
    SPname As String
    SPdataType As ADODB.DataTypeEnum
    SPdirection As ADODB.ParameterDirectionEnum
    SPlength As ADO_LONGPTR
    SPvalue As Variant
    End Type
    Public sqArray(5) As SqlSPparams</pre>

    -----------------------+--------------------------
    <pre>Private Function FillSParray(CLSparms() As SqlSPparams) As SqlSPparams()
    CLSparms(0).SPname = "@Amount"
    CLSparms(0).SPdataType = adDouble
    CLSparms(0).SPdirection = adParamInput
    CLSparms(0).SPvalue = TheAmount <font color=red> 'Dollar Amt</font color=red>
    ~~~~~ plus more parameters ~~~~~~~~~~
    </pre>

    Alan

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ByRef Hell (VBA/Excel/2002)

    A bit of a wild stab, not having intimate knowledge of the code, but I can't see the point of the function prototype:
    Private Function FillSParray(CLSparms() As SqlSPparams) As SqlSPparams()

    Since CLSparms (or sqArray) is being (default) passed ByRef, why return anything? It's return reference is not utilized in the calling code anyway. Would
    Private Sub FillSParray(CLSparms() As SqlSPparams)

    somehow "magically" compile better?

    Alan

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ByRef Hell (VBA/Excel/2002)

    It's hard to be sure, but if you have sqArray as both a local and global variable (of two different sizes), that could be a bit confusing. If you want to pass ByVal, that's easy enough to specify. If not, then perhaps you cannot pass the same-named variable from the local scope to another function that can see the global one.

  7. #7
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: ByRef Hell (VBA/Excel/2002)

    Well, it is no worse. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>
    Alan

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ByRef Hell (VBA/Excel/2002)

    I seem to remember a similar problem I had, passing an array of user-defined types. I think I solved it somehow by specifying the UDT as a Variant, in some capacity or other. Whatever it was, it was not a documented limitation, and a real PITA to finally hit on. Maybe in the function/sub declaration:

    Private Sub FillSParray(CLSparms() As Variant)

    might be worth a whirl?

    Alan

  9. #9
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: ByRef Hell (VBA/Excel/2002)

    No soap. Introduced a new error.

    I have been stuck on this one part for almost a month. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> The rest of the team have their own demons right now.
    Alan

  10. #10
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ByRef Hell (VBA/Excel/2002)

    I can't recreate the error. Is your code divided between multiple code/class modules?

    <pre>Type Tester
    first As String
    second As Long
    SPdataType As ADODB.DataTypeEnum 'Set Ref to ADO 2.8
    SPdirection As ADODB.ParameterDirectionEnum
    SPlength As ADO_LONGPTR
    SPvalue As Variant
    End Type
    Public testArray(5) As Tester

    Sub SimulateError()
    Dim testArray(4) As Tester ' Local duplicate name
    Call FillArray(testArray) ' ByRef, or is it?
    Stop ' View locals to confirm assignment (optional)
    MsgBox FlyIt("Hello", "How are you", testArray)
    End Sub

    Sub FillArray(refArray() As Tester)
    refArray(0).first = "This"
    refArray(0).second = 123
    refArray(0).SPdataType = adDouble
    refArray(0).SPdirection = adParamInput
    Dim TheAmount As Variant
    TheAmount = 123456.78
    refArray(0).SPvalue = TheAmount
    End Sub

    Function FlyIt(str1 As String, str2 As String, Params() As Tester) As String
    Stop ' View locals to confirm assignment (optional)
    FlyIt = Params(0).first
    End Function</pre>


  11. #11
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: ByRef Hell (VBA/Excel/2002)

    Very long code fragment (over 5K) moved to attachment by HansV

    Yes, unfortunately it is broken up. I had to cut out some things to prevent showing company-confidential. Here is the code except the Apply Stored Proc

    This is a tiny piece of a huge application. Sorry to bother you all.

    Suffice to say, the code dies before it builds the parameters array.
    Alan

  12. #12
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ByRef Hell (VBA/Excel/2002)

    > the code dies before it builds the parameters array

    In your initial screen shot, there was a compile error, so the code was dying before it even started doing anything. Is that still the problem? Can you Debug>Compile without errors?

    If you still have that particular error, try bypassing it using ByVal on the third parameter in runSQLsp (the array). Unless you need to further change the calling procedure's copy of the array, it should not need to be passed ByRef:

    Public Function runSQLsp(thisSP As String, Action As String, ByVal Params() As SqlSPparams)

    Hopefully this will get us past the initial error and into the meat of what is causing it. Or just get rid of it. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  13. #13
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: ByRef Hell (VBA/Excel/2002)

    Same problem. Never changed. I guess I did not know all the questions to ask at the beginning.
    Alan

  14. #14
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: ByRef Hell (VBA/Excel/2002)

    It would not allow ByVal, only ByRef. It does all the indiv macros up to DoTheCSAdd before we bomb. I am in the process of comparing line by line to the code I cloned (and it worked for months) from another module to see what changed.
    Alan

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ByRef Hell (VBA/Excel/2002)

    Jeff,

    Array arguments cannot be passed by value, only by reference. (Nice try, though - it could have been an elegant way out of the problem)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •