Run time error 13 on End Function (1 Viewer)

Thicko

Registered User.
Local time
Today, 06:01
Joined
Oct 21, 2011
Messages
46
Hi All,

I have a function that I want to export as an array. I've limited the code to the Type Mismatch error

Code:
Public Function BatchProductionVial1(DrugName As String, DrugRoute As String, DrugDose As Double, DrugBatchSize As Integer) As Variant


    Dim returnVal(3) As Variant
        
        returnVal(0) = DrugName
        returnVal(1) = DrugRoute
        returnVal(2) = DrugDose
        returnVal(3) = DrugBatchSize
        
        BatchProductionVial1 = returnVal

End Function
The error comes at the end function, the locals window shows the results as intend so I can't work out the issue

Many Thanks for any help.
 

vba_php

Forum Troll
Local time
Today, 00:01
Joined
Oct 6, 2019
Messages
2,884
I have a function that I want to export as an array.
i don't think i've ever seen a question like this asked before. I guess the thing I would ask is:

=> why are you doing this? what's the purpose? what do you mean by "export"? the general purpose of a function is to *return* a value to something else, not assign it to an actual array. have you looked up whether a function's return can actually accept a full array of values? VBA is not a complex language. you can do very weird things with arrays in other web languages, but not sure about simple basic.
 

Thicko

Registered User.
Local time
Today, 06:01
Joined
Oct 21, 2011
Messages
46
The function runs a lot more code and the end result is the quantities of 4 (or less) vial sizes needed.

I need to return those four values to a query so it can be used.
 

vba_php

Forum Troll
Local time
Today, 00:01
Joined
Oct 6, 2019
Messages
2,884
got the query's sql to post? my questions might be a little off, but there are quite a lot of people looking at this.
 

cheekybuddha

AWF VIP
Local time
Today, 06:01
Joined
Jul 21, 2014
Messages
516
Hi,

How are you calling the function?

If you are just trying to call it in the Immediate Window you will have to give an index, since you can't output an array directly.

The following will work:
Code:
?BatchProductionVial1("test", "Route 1", 1.5, 9)(0)
test
?BatchProductionVial1("test", "Route 1", 1.5, 9)(1)
Route 1
?BatchProductionVial1("test", "Route 1", 1.5, 9)(2)
 1.5 
?BatchProductionVial1("test", "Route 1", 1.5, 9)(3)
 9
 

cheekybuddha

AWF VIP
Local time
Today, 06:01
Joined
Jul 21, 2014
Messages
516
A query won't be able to handle an array without passing it through some sort of function, but you can use the indices to access the individual values.

Eg:
Code:
UPDATE SomeTable
SET
  DrugName = BatchProductionVial1("test", "Route 1", 1.5, 9)(0),
  Route = BatchProductionVial1("test", "Route 1", 1.5, 9)(1),
  Dose = BatchProductionVial1("test", "Route 1", 1.5, 9)(2),
  BatchSize = BatchProductionVial1("test", "Route 1", 1.5, 9)(3)
WHERE VialID = 123;
However, I think that the function will be called for each value (ie 4 times) which isn't very efficient.
 

Thicko

Registered User.
Local time
Today, 06:01
Joined
Oct 21, 2011
Messages
46
What I was missing was the extra brackets (0), (1) at the end. Thank you buddha

I also get the efficiency point I had originally thought that running the function and exporting 4 results would be more efficient as only need to run once but it's just dawned on me that I have to call the function 4 times. Woops

Will go with plan B and output using a recordset
 

cheekybuddha

AWF VIP
Local time
Today, 06:01
Joined
Jul 21, 2014
Messages
516
You're welcome!

It's not totally clear where you are pulling your values from, and also where you are trying to push the values to.

If you give some more details we might be able to suggest a more efficient method. Recordsets are often just as inefficient.

hth,

d
 

Thicko

Registered User.
Local time
Today, 06:01
Joined
Oct 21, 2011
Messages
46
In short and appreciate my explanation might be lacking:

There's the 4 variables in the function that come from a form.

These create a number of tables based on the different sizes of drug vials available, the function now populates one of those tables using a recodset with the number of the different vial sizes needed and the amount of stock available. Once done it can select the product batch numbers required and assigns to a table from where the picking list is created.
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 00:01
Joined
Feb 28, 2001
Messages
17,034
If you take the approach you mentioned of outputting via recordset:

Consider building at least a temporary table where you have your four results plus something useful as a key. Then rather than running the function in the query, use that key to JOIN the recordset to the place you needed it. Divide this problem into a separate output phase of the four values followed by the association of the values to whatever else was involved. Or is that what you intended in post #9?
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom