Trouble selecting values to feed into custom formula-CodePudding

I have been given some code to add into an existing VBA module that calculates spline distributions. I can get this code to run in Excel by manually selecting the variables, however even though I have existing code to select the values, I cannot get this specific one to pick up the values.

The formula is called Spline and is used for calculating Spline distributions. Its requirements are coded as such:

Function Spline(form As String, Splines As Integer, params As Variant, knots As Variant, coef As Variant, tstar As Range)

In this, there are either 3 or 4 parameters that should be picked up by Params and 4 knots that should be picked up in knots. Tstar is a long list of timepoints.

I have formatted the values to be picked up by the formulae in the order that the function is asking for them, i.e. Form, then splines, then params, then knots etc.

The code I have written to select this values and the wider context for the selects are as follows:

Sub CurveParamCalcs1()
Dim CompOS As Integer
Dim Wsh As Worksheet: Set Wsh = Worksheets("CGP")
Dim CntH As Integer, CntV As Integer
Dim Arr_1 As Variant 'there are 8 arrays, cutting the rest for this example
Dim StrtCol As Long
ReDim Arr_1(0 To 937, 0 To 15)

CompOS = Sheets("Curve parameters").Range("AC16").Value
StrtCol = Col_Number("AV")
For CntH = 0 To 15
    For CntV = 0 To 783
            Select Case CompOS
'removing the other cases as they run correctly when selected
Case 7
Arr_1(CntV, CntH) = Spline(Wsh.Range(Col_Letter(StrtCol   CntH) & "87"), Wsh.Range(Col_Letter(StrtCol   CntH) & "88"), Wsh.Range(Col_Letter(StrtCol   CntH) & "89:92"), Wsh.Range(Col_Letter(StrtCol   CntH) & "93:93"), 1, Wsh.Range("AU151:AU934"))
End Select
    Next CntV
Next CntH

I am getting the error: RunTime Error 1004: Method 'Ranges' of object '_Worksheet' failed, which after much googling seems to be referring to the current worksheet being wrong. However, this is all taking place on the Worksheet CGP, and I can't see where the problem is. I've tried just using the usual Range rather than the Wsh.Range and I'm getting the same error. I am not experienced with VBA (my background is R), and so haven't been having much success troubleshooting this myself.

Just for an example, here is a case that is running correctly and picking up the right values:

Case 5
Arr_1(CntV, CntH) = SClognormal(Wsh.Range("AU" & CntV   151), Wsh.Range(Col_Letter(StrtCol   CntH) & "81"), Wsh.Range(Col_Letter(StrtCol   CntH) & "82"))

which feeds into this survival lognormal calculation

Function SClognormal(day, param1, param2)

SClognormal = 1
If day > 0 Then
SClognormal = 1 - WorksheetFunction.LogNormDist(day, param1, 1 / Exp(-1 * param2))
End If


End Function

Thanks for having a look.

CodePudding user response：

You have invalid range references. If the column letter is for example R:

Col_Letter(StrtCol CntH) & "89:92": evaluates to R89:92, but should be R89:R92.
Col_Letter(StrtCol CntH) & "93:93"): evaluates to R93:93, but should be R93.

This is easier to debug using some Range variables:

Dim arg1 As Range
Set arg1 = Wsh.Range(Col_Letter(StrtCol   CntH) & "87") ' fine

Dim arg3 As Range
Set arg3 = Wsh.Range(Col_Letter(StrtCol   CntH) & "89:92") ' blows up

Also note that using the column letter is a bit clunky. Generally prefer Cells over Range if you're working with the column index.

Wsh.Cells(87, StrtCol   CntH)
...
Wsh.Cells(89, StrtCol   CntH).Resize(4) ' rows 89:92
...
Wsh.Cells(93, StrtCol   CntH)