CODEWITHSUNDEEP
vbaexcel
Josh
Josh

Reputation: 878

Custom Function Works in VBA but not when called in a Excel Cell

I have written a custom VBA function that will work when I call it from within VBA. e.g.

Sub test()

    Debug.Print calculate_acr_bands_data("recommended_acr", "22/11/2014", 2.67, "B23:C27", 50, 2.14, 1, 0.107, 3.89, "22/02/2021")

End Sub

Outputs ...

0.01

However, when I call the exact same function from within a cell in my excel workbook. I get the error "A value used in this formula is of the wrong data type". Which makes no sense because I just proved the formula worked with the exact same inputs from within VBA.

enter image description here

I have uploaded my repo to my GitHub account if you would like to view the source code...

https://github.com/Joshua-W-Adams/vba-wall-loss-vs-time

Thanks for the help.

UPDATE

Function has been posted below...

'Description: Calculate database of wall loss vs time information so relevant parameters can be returned
Public Function calculate_acr_bands_data(return_parameter As String, last_inspection_date As Date, last_inspection_date_wall_loss As Double, _
    acr_bands_array_text As String, nominal_wall_thickness As Double, _
    minimum_allowable_wall_thickness As Double, current_acr As Double, actual_cr As Double, current_rl As Double, current_end_of_life As Date) As Variant

    'Create copy of data range so nominal wall thickness value is not overriden
    Dim acr_bands_array As Range:                   Set acr_bands_array = ThisWorkbook.Worksheets("Wall_Loss_Vs_Time_Graph").Range(acr_bands_array_text)
    Dim n As Integer:                               n = 0
    Dim acr_bands_array_size As Integer:            acr_bands_array_size = acr_bands_array.Rows.Count
    Dim return_data_array() As BAND_ARRAY_CLASS
    Dim current_band_position As Integer
    Dim recommended_acr As Double
    Dim recommended_rl As Double
    Dim forecast_wall_loss As Double
    Dim recommended_end_of_life As Date

    'Update band array with fail FFS wall thickness of current cml
    acr_bands_array(acr_bands_array.Rows.Count, 1) = nominal_wall_thickness - minimum_allowable_wall_thickness

    'Determine current band that the last inspection date wall loss falls into
    For n = 1 To acr_bands_array_size - 1

        If last_inspection_date_wall_loss < acr_bands_array(n + 1, 1) Then

            current_band_position = n
            Exit For

        End If

    Next n

    'Push first row to array (last inspection date details)
    return_data_array = push_to_array(return_data_array, "Band Data Points", Format(last_inspection_date, "Short Date"), last_inspection_date_wall_loss, acr_bands_array(current_band_position, 2))

    'Debug.Print return_data_array(0).graph_name
    'Debug.Print return_data_array(0).date_value
    'Debug.Print return_data_array(0).wall_loss
    'Debug.Print return_data_array(0).acr

    'Push all corrosion rate band data
    If n <> 0 Then 'If a hole out is not detected

        For n = current_band_position To acr_bands_array_size - 1

            return_data_array = push_to_array(return_data_array, _
                "Band Data Points", _
                Format(DateAdd("d", (acr_bands_array(n + 1, 1) - return_data_array(UBound(return_data_array)).wall_loss) / acr_bands_array(n, 2) * 365, return_data_array(UBound(return_data_array)).date_value), "Short Date"), _
                acr_bands_array(n + 1, 1), _
                acr_bands_array(n, 2))

        Next n

    End If

    'Push data for current wall loss as of today
    For n = 1 To UBound(return_data_array) - 1

        If Now() < return_data_array(n + 1).date_value Then

            return_data_array = push_to_array(return_data_array, _
                "Band Data Points", _
                Format(Now(), "Short Date"), _
                return_data_array(n).acr * DateDiff("d", return_data_array(n).date_value, Now()) / 365 + return_data_array(n).wall_loss, _
                return_data_array(n).acr)

                'Dim rsize As Integer
                'rsize = UBound(return_data_array)
                'Debug.Print return_data_array(rsize).graph_name
                'Debug.Print return_data_array(rsize).date_value
                'Debug.Print return_data_array(rsize).wall_loss
                'Debug.Print return_data_array(rsize).acr

            Exit For

        End If

    Next n

    recommended_acr = (return_data_array(UBound(return_data_array) - 1).wall_loss - return_data_array(UBound(return_data_array)).wall_loss) _
                        / (return_data_array(UBound(return_data_array) - 1).date_value - return_data_array(UBound(return_data_array)).date_value)
    forecast_wall_loss = return_data_array(UBound(return_data_array)).wall_loss
    recommended_end_of_life = return_data_array(UBound(return_data_array) - 1).date_value
    recommended_rl = DateDiff("d", return_data_array(UBound(return_data_array) - 1).date_value, Now()) / 365

    Debug.Print recommended_acr
    Debug.Print forecast_wall_loss
    Debug.Print recommended_end_of_life
    Debug.Print recommended_rl

    'Debug.Print recommended_rl

    'Add additional graph data for reference
    return_data_array = push_to_array(return_data_array, "Today", DateValue(Format(Now(), "Short Date")), 0, 0)
    return_data_array = push_to_array(return_data_array, "Today", DateValue(Format(Now(), "Short Date")), nominal_wall_thickness, 0)

    return_data_array = push_to_array(return_data_array, "Recommended RL", DateValue(Format(recommended_end_of_life, "Short Date")), 0, 0)
    return_data_array = push_to_array(return_data_array, "Recommended RL", DateValue(Format(recommended_end_of_life, "Short Date")), nominal_wall_thickness - minimum_allowable_wall_thickness, 0)

    return_data_array = push_to_array(return_data_array, "Recommended ACR", DateValue(Format(Now(), "Short Date")), forecast_wall_loss, recommended_acr)
    return_data_array = push_to_array(return_data_array, "Recommended ACR", DateValue(Format(recommended_end_of_life, "Short Date")), nominal_wall_thickness - minimum_allowable_wall_thickness, 0)

    return_data_array = push_to_array(return_data_array, "Current RL", current_end_of_life, 0, 0)
    return_data_array = push_to_array(return_data_array, "Current RL", current_end_of_life, nominal_wall_thickness, 0)

    return_data_array = push_to_array(return_data_array, "Current ACR", DateValue(Format(last_inspection_date, "Short Date")), last_inspection_date_wall_loss, current_acr)
    return_data_array = push_to_array(return_data_array, "Current ACR", DateValue(Format(current_end_of_life, "Short Date")), nominal_wall_thickness, current_acr)

    return_data_array = push_to_array(return_data_array, "Actual CR", DateValue(Format(last_inspection_date, "Short Date")), last_inspection_date_wall_loss, actual_cr)
    return_data_array = push_to_array(return_data_array, "Actual CR", DateAdd("d", (nominal_wall_thickness - last_inspection_date_wall_loss) / actual_cr * 365, last_inspection_date), nominal_wall_thickness, 0)

    return_data_array = push_to_array(return_data_array, "Actual RL", DateAdd("d", (nominal_wall_thickness - last_inspection_date_wall_loss) / actual_cr * 365, last_inspection_date), 0, 0)
    return_data_array = push_to_array(return_data_array, "Actual RL", DateAdd("d", (nominal_wall_thickness - last_inspection_date_wall_loss) / actual_cr * 365, last_inspection_date), nominal_wall_thickness, 0)

    return_data_array = push_to_array(return_data_array, "Fail FFS", DateValue(Format(last_inspection_date, "Short Date")), nominal_wall_thickness - minimum_allowable_wall_thickness, 0)
    return_data_array = push_to_array(return_data_array, "Fail FFS", IIf(recommended_end_of_life > current_end_of_life, recommended_end_of_life, current_end_of_life), nominal_wall_thickness - minimum_allowable_wall_thickness, 0)

    return_data_array = push_to_array(return_data_array, "Nominal Wt", last_inspection_date, nominal_wall_thickness, 0)
    return_data_array = push_to_array(return_data_array, "Nominal Wt", IIf(recommended_end_of_life > current_end_of_life, recommended_end_of_life, current_end_of_life), nominal_wall_thickness, 0)

    'Return requested parameter to user
    If return_parameter = "database" Then

        calculate_acr_bands_data = return_data_array

    ElseIf return_parameter = "recommended_acr" Then

        calculate_acr_bands_data = Round(recommended_acr, 2)

    ElseIf return_parameter = "forecast_wall_loss" Then

        calculate_acr_bands_data = Round(forecast_wall_loss, 2)

    ElseIf return_parameter = "recommended_rl" Then

        calculate_acr_bands_data = Round(recommended_rl, 2)

    Else

        calculate_acr_bands_data = Null

    End If

End Function

'Description: Pass array and values to push
Function push_to_array(bands_array As Variant, graph_name As String, date_value As Date, wall_loss As Double, acr As Double) As Variant

    Dim size As Integer: size = UBound(bands_array) + 1

    ReDim Preserve bands_array(size)

    Set bands_array(size) = New BAND_ARRAY_CLASS

    With bands_array(size)
        .graph_name = graph_name
        .wall_loss = wall_loss
        .date_value = date_value
        .acr = acr
    End With

    push_to_array = bands_array

End Function

Upvotes: 2

Views: 1111

Answers (1)

YowE3K
YowE3K

Reputation: 23974

Your code won't work as a UDF because a UDF can't assign a value to any cell other than the one that the UDF is called from (and its value is assigned by setting the return value of the function).

So, because your line 

acr_bands_array(acr_bands_array.Rows.Count, 1) = nominal_wall_thickness - minimum_allowable_wall_thickness

is attempting to modify the state of the Excel worksheet, it will crash and should return a #VALUE! error to the cell the function was called from.

I'm not sure why you are getting a "A value used in this formula is of the wrong data type" error - it is very unusual for a UDF to return any sort of error message, just the error value.

Upvotes: 1

Related Questions