Calling a UDF in VBA, how to pass array from one UDF to another?

    Good morning,


    I'm having a bit of a problem getting this last piece to work. Basically I have a UDF called Route_Module which can take locations, and passes the data to a mapping program which can then return the driving distance, or driving time of the points you give it. I am using a Paramarray in this UDF to allow the user to enter a variable amount of locations. This function is working great as a UDF entered into a cell, no issue here (I posted the code below for your reference).


    Now, what I would like to do is be able to use this UDF within VBA for calculation purposes. I have created a test function to try and achieve this. Instead of the user having to pass each location in as a separate array item, the list of locations is going to be concatenated in one cell, separated by commas. What I have tried to do is use "Split" to create an array from the comma delimited list, however if I pass this array into my UDF, I can't get it to work.


    What I am struggling with is passing the array created in the "TEST" function, into the Route_Module function.

    Re: Calling a UDF in VBA, how to pass array from one UDF to another?


    You don't need param array if you are already passing an array, just have locations as a variant

    Re: Calling a UDF in VBA, how to pass array from one UDF to another?


    Hi Kyle123,


    Is it possible to accomplish while leaving Locations as a Paramarray?


    Reason being is I would like to maintain the functionality of the Route_Module as a useable UDF with the ability to take in multiple values, but also be able to call it in a separate function if need be.


    I guess worst case scenario I could keep two versions of Route_Module, one as a useable UDF, one as a "call-able" UDF, but hoping there is a cleaner alternative?


    Thanks!

    Re: Calling a UDF in VBA, how to pass array from one UDF to another?


    Yes, you can test if the param array value has been passed by a split function:


    Code
    If VarType(locations(LBound(locations))) = 8200 Then 'Passed by split


    This works as the first element in the locations array will be a string array, any other type will not have a vartype of 8200


    The paramarray will be an array of arrays, so will take the form of locations(0) (0),locations(0) (1),locations(0) (2) etc

    Re: Calling a UDF in VBA, how to pass array from one UDF to another?


    I see what you mean, but I'm not sure how I would implement that into my code/what would need to be done after the "then".


    And wouldn't I need to test it with:

    Code
    If VarType(locations) = 8200


    If I test the individual element (Lbound(Locations)) won't this just return the vartype of the element i.e. if Locations(0) is a String, it will return vartype = 8

    Re: Calling a UDF in VBA, how to pass array from one UDF to another?


    No, when you pass something via split to param array, you are creating an array within the locations array, so at locations(0) you don't have a string, you have an array containing the split data.


    If you pass values manually, location(0) will be a string so will return 8, it will not be an array.

    Re: Calling a UDF in VBA, how to pass array from one UDF to another?


    Gotcha,


    Ok...silly question, but when I am writing my lbound ubound statement, how do I return for the second dimension?


    If I write lbound(locations) it is only checking the first dimension. So I tried lbound(locations,2) but that doesn't seem to be working either. As you can tell, I'm fairly new to arrays

    Re: Calling a UDF in VBA, how to pass array from one UDF to another?


    It appears the correct syntax would be

    Code
    Lbound(Locations(0), 1)


    If I'm off track let me know! Thanks :)

    Re: Calling a UDF in VBA, how to pass array from one UDF to another?


    locations doesn't have a second dimension, I think you're getting a bit mixed up between multi-dimensional arrays and jagged arrays.


    A multi-dimensional array is a single array, a jagged array is an array of multiple arrays. Their syntax differs:


    2d array:

    Code
    arr(0,0)
arr(0,1)


    Jagged array

    Code
    arr(0)(1)
arr(0)(2)


    The easiest thing to do may be something like this:

    Code
    If VarType(locations(LBound(locations))) = 8200 Then
    newarray = locations(LBound(locations)) 'Assign the array at postion 0 of the locations array to the new array
Else
    newarray = locations
End If


    Then just use newArray, it will always be a single array

    Re: Calling a UDF in VBA, how to pass array from one UDF to another?


    Got it!


    Thanks so much Kyle123 for taking the time to walk me through this. I have the code functioning as desired now.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login