Develop a function in VBA that creates a simple regression

Question

I'm trying to create a function in VBA that when recives a colum of data and the title of the graphic, returns a graphic of a simple regression in Excel. Here's the code. I have tried changing the code but I still havong the same value error.

Function ModeloEstimacionUnicaColumna(ColumX As Range, TitleGraphic As String) As Boolean
    
    If ColumnaX Is Nothing Then
        MsgBox "La columna de datos está vacía."
        ModeloEstimacionUnicaColumna = False
        Exit Function
    End If
    
    
    Dim DatosX As Variant
    Dim Modelo As Object
    Set Modelo = CreateObject("Excel.Application")

    
    DatosX = ColumnaX.Value

    
    Modelo.Workbooks.Add
    Modelo.ActiveSheet.Cells(1, 1).Resize(UBound(DatosX), 1).Value = Application.Transpose(DatosX)
    Modelo.Charts.Add
    Modelo.ActiveChart.SetSourceData Source:=Modelo.ActiveSheet.Range("Sheet1!$A$1:$A$" & UBound(DatosX))
    Modelo.ActiveChart.ChartType = xlXYScatterLines
    Modelo.ActiveChart.Location Where:=xlLocationAsNewSheet
    Modelo.ActiveChart.HasTitle = True
    Modelo.ActiveChart.ChartTitle.Text = TitleGraphic

    
    ModeloEstimacionUnicaColumna = True
End Function

I am not sure if there is some excel library that I am not applying or if I have made some serious syntax error in the code. I would like that once I give the function the required variables, it will return the desired graph of a simple regression.

Answer 1

It seems to have a few issues;

1. "ColumX" and "ColumnaX." I used "ColumnX" to be consistent.
2. "Modelo" object, used existing Excel application instead of creating a new one.
3. Source data references "Sheet1!$A$1:$A$" & UBound(DatosX).

    Function ModeloEstimacionUnicaColumna(ColumnX As Range, TitleGraphic As String) As Boolean
    
        If ColumnX Is Nothing Then
            MsgBox "La columna de datos está vacía."
            ModeloEstimacionUnicaColumna = False
            Exit Function
        End If
        
       
        If ColumnX.Columns.Count > 1 Then
            MsgBox "La columna de datos no es válida. Debe ser una única columna."
            ModeloEstimacionUnicaColumna = False
            Exit Function
        End If
        
        Dim DatosX As Variant
        Dim ChartSheet As Worksheet
        Dim MyChart As ChartObject
        
        DatosX = ColumnX.Value
        Set ChartSheet = ThisWorkbook.Sheets.Add
        Set MyChart = ChartSheet.ChartObjects.Add(Left:=10, Width:=375, Top:=75, Height:=225)
        
        ' Set source data directly.
        MyChart.Chart.SetSourceData Source:=ColumnX
        
        
        MyChart.Chart.ChartType = xlXYScatterLines
        MyChart.Chart.HasTitle = True
        MyChart.Chart.ChartTitle.Text = TitleGraphic
        
        ModeloEstimacionUnicaColumna = True
    End Function