Passing Array to VBA

[Bazman76]

Hi there,

I have the following code:

Option Explicit

Function BSplineCoefficients(u As Double, n As Integer, p As Integer, m As Integer, ByRef clamped_knots As Range) As Double()

Dim y As Double

Dim Coeff() As Double
ReDim Coeff(n) As Double
Dim k As Integer
Dim d As Integer

Dim x As Integer
Dim i As Integer



x = 0
x = clamped_knots(0).Value

If u = clamped_knots(0).Value Then

Coeff(0) = 1
BSplineCoefficients = Coeff()
End If

If u = clamped_knots(m).Value Then
Coeff(n) = 1
BSplineCoefficients = Coeff()
End If

Do Until clamped_knots(k).Value >= u

k = k + 1

Loop

k = k - 1

Coeff(k + 1) = 1

For d = 1 To p

    Coeff(k - d + 1) = ((clamped_knots(k + 1).Value - u) / (clamped_knots(k + 1).Value - clamped_knots(k - d + 1).Value)) * Coeff(k - d + 2)
     
    x = clamped_knots(k + 1).Value
       
    If d > 1 Then
    
    For i = k - d + 1 To k - 1
 
    Coeff(i + 1) = ((u - clamped_knots(i).Value) / (clamped_knots(i + d).Value - clamped_knots(i).Value)) * Coeff(i + 1) + _
    ((clamped_knots(i + d + 1).Value - u) / (clamped_knots(i + d + 1).Value - clamped_knots(i + 1).Value)) * Coeff(i + 2)

    Next i
    
    End If

    Coeff(k + 1) = ((u - clamped_knots(k).Value) / (clamped_knots(k + d).Value - clamped_knots(k).Value)) * Coeff(k + 1)

Next d

BSplineCoefficients = Coeff()

End Function

As you can see I am successully calling the values from the range on the spreadsheet into the function as a range object. However there is a small logic error and it is hard for me to solve because I don't know how to see the value of each element in the Range object from within VBA.

All I want to do is use the information from a Range as an input and then be able to manipulate it like an array once it is input. If there is a way to do this with the range object please let me know

I want to be able to see each of the individual values in the range like you can with an array object. What is the best way to do this? At the moment it just gives all the attributes and properties of the range which means nothing to me.

If there is a different way not using the Range object I'd be happy to look at this too.

Thanks in advance

Baz

[si_the_geek]

Welcome to VBForums

You can put the values from a group of cells into an array like this:

Dim vArray As Variant
  vArray = Sheet1.Range("D7:E9").Value

I presume you can do similar with your variable:

Dim vArray As Variant
  vArray = clamped_knots.Value

[Bazman76]

wow thanks for the prompt response and the welcome.

The only thing is ideally I would like to pass the range to the array via the function call.

This is because the number of elements in the range being called and even the location of that range can and in most cases will change between calls.

Is this possible?

[si_the_geek]

You would still pass the range to the array as before, then use the code I posted (which will hopefully work as-is) to convert it to an array for the rest of your code to work with.

You can then use code like this to read each 'cell' in the array:

Dim lngCol as Long, lngRow as Long
  For lngRow = 1 To UBound(vArray,1)
    For lngCol = 1 To UBound(vArray,2)
      MsgBox vArray(lngRow, lngCol)
    Next lngCol
  Next lngRow

If you want a one-dimensional array, you will need to do the conversion (from the range or the array above), using a loop to put it into that array.

[Bazman76]

ah understood fully now!