Using paramarray in function call

Greetings,,
I have a problem with coding a VBA function.   I am using Excel 2003 and have not done any serious programming for years (Pascal, though I was using Basic and Fortran on a mainframe in 1970).  This project requires reading an array of 27 cells formatted as Text (Sheet4!F3:F29) into a function so that each can be compared with another string also read in via the same function call from Excel.  The array is read in as dataIN and the single string as triad.  Because I will be using the function in a large number of different situations, the information needs to come with the call, rather than collected as the function runs.  What seems to happen is that I have not been able to convince VBA that the loop is using two arrays because it fouls up when i = 1 (Option 1 is not being used because there is not any problem using the first index value as 0 within the VBA function).  Apparently dataIN is read in as a single item because Ubound was of no help.  I am sure the solution is trivial and it is only lack of experience (= ignorance on my part which is why Help, on and off Internet, has not been).  Could someone take pity on a retired academic and spare a minute or two to fix the coding please.



Option Explicit
Function Trial2(triad As String, ParamArray dataIN() As Variant) As Integer

Dim i As Integer, dataUSED() As Variant, Sheet4 As Worksheet
Worksheets("Sheet4").Activate
dataUSED = dataIN

For i = 0 To 26
dataUSED(i) = dataIn(i)
Next i

Remainder of coding in not a problem.  An integer is returned to calling cell.


End Function

Thanks,
Gordon.
Start Free Trial