VBA: More questions about custom Type and Function with arrays

Question

I am getting stuck on trying to pass array variable (custom type) to a function. The error comes up with the calling the function with D1, What am I getting wrong here please?

I have tried declaring D1() etc which did not seem correct but did not work anyway.

Public Type CusType
    One_ As Integer
    Two_ As Single
    Thr_ As Single
    Fou_ As Single
End Type

Public Sub Test1()
    Dim D1 As CusType
    Dim D2 As CusType
    Dim D3 As CusType
    Dim Marker As Integer

    Marker = 1

    With D1
        .One_ = 11
        .Two_ = 12
        .Thr_ = 13
        .Fou_ = 14
    End With

    With D2
        .One_ = 21
        .Two_ = 22
        .Thr_ = 23
        .Fou_ = 24
    End With

    With D3
        .One_ = 31
        .Two_ = 32
        .Thr_ = 33
        .Fou_ = 34
    End With

    Dim TestResult As CusType
    TestResult = Test(Marker, **D1, D2, D3**)

    Debug.Print TestResult.One_ & ","; TestResult.Two_ & ","; TestResult.Thr_ & ","; TestResult.Fou_
End Sub

Public Function Test(R, A, B, C) As CusType
    Dim First, Second, Third, Fourth As Single

    If R = 0 Then
        Test = A
    ElseIf R = 1 Then
        Test = B
    Else
        Test = C
    End If
End Function

score 2 · Accepted Answer · answered Sep 15 '16 at 08:09

2

The error message clearly tells you:

Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions

(It is helpful to include exact error messages in the question.)

You did not declare parameter and return types for Test, so your structures are going to be coerced to and from a Variant.

Declare the types:

Public Function Test(ByVal R As Long, A As CusType, B As CusType, C As CusType) As CusType

answered Sep 15 '16 at 08:09

GSerg

76,472
17
159
346

The `R` parameter needs to be an `Integer`: `Public Function Test(R As Integer, A As CusType, B As CusType, C As CusType) As CusType` – ThunderFrame Sep 15 '16 at 09:32
Thank you GSerg. Sorry about neglecting to include error message. – user110084 Sep 15 '16 at 09:34
@ThunderFrame [No, it does not](http://stackoverflow.com/a/26409520/11683). – GSerg Sep 15 '16 at 09:37
@GSerg - It *does* if you want to pass it `ByRef`. – ThunderFrame Sep 15 '16 at 09:40
@ThunderFrame The OP does not want to pass it byref, they omitted byval because they didn't know about it. In case it actually needed to be passed byref, the original variable should have been declared as Long too. Apart from interop scenarios, there is [no reason](http://stackoverflow.com/q/26717148/11683) to declare things as `Integer`. – GSerg Sep 15 '16 at 09:45
@GSerg I saw your link. I know the logic. There *are* reasons to use Integer. The OP's code is clearly incomplete/abridged. The value of `Marker` is set to an `Integer` literal of 1, so either it's incomplete code and we don't know how Marker will be used, or the value can be passed as a literal valued argument. Your answer, in isolation, just swaps one error for another. Either go all-in on `Long` and show the necessary code changes, including an explicit Long-assignment of `Marker = 1&`, or just stick to fixing the OP's immediate problem by leaving the implicit ByRef. – ThunderFrame Sep 15 '16 at 09:53
1

Actually, I chose integer because it will most likely << 100 and never exceed 10000, but now I learn that Integer is an illusion! I am still trying to figure out reasons for using ByRef and ByVal. Why would ByRef necessitate Long rather than Integer? – user110084 Sep 15 '16 at 15:33
@user110084 `ByRef` requires that you pass to the function variable of exactly the type declared for the function parameter. `ByVal` is more relaxed and allows you to pass things that are implicitly convertible to the declared parameter type. So `ByRef` would make you pass exactly an `Integer` or a `Long` depending on what is declared in the function. See e.g. http://stackoverflow.com/q/4908692/11683. – GSerg Sep 15 '16 at 17:29
@GSerg, just straining your patience with one more question about ByVal: So, if I have var as DOUBLE in the Sub main(), can I temporarily do a ByVal var as SINGLE when passing it to a function? – user110084 Sep 16 '16 at 17:06
@user110084 With byval you can pass a `double` to a function that wants a `single`, but it will result in an error if the value stored in the `double` is outside the range of a `single`. – GSerg Sep 16 '16 at 17:10
Understood @GSerg. Have to confess that it was for curiosity as I cannot think of why I might actually want or need to do that. – user110084 Sep 16 '16 at 17:23

VBA: More questions about custom Type and Function with arrays

1 Answers1