VBA function doesn't compile

Question

I have got a very simple function in VBA in which I work on a Range with multiple columns and rows. First I wanted to sort that Range over first column (filled with dates):

Public Function getCOF(data_zawarcia_tf As Date, waluta_tf As String, czas_trwania_tf As Double, _
                        table_to_search As Range, output As Range) As Double
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    table_to_search = table_to_search.Sort(Key1:=Range("A1"), _
                     Order1:=xlAscending)
    MsgBox "h"
    MsgBox table_to_search(1, 1)
    czas_trwania_tf = -Int(-czas_trwania_tf / 12) * 12
    For i = lastRow To 1 Step -1
        If table_to_search(i, 1) < data_zawarcia_tf And table_to_search(i, 2) = "S" And _
            waluta_tf = table_to_search(i, 3) And czas_trwania_tf = table_to_search(i, 4) Then
            getCOF = output(i)
            Exit For
        End If
    Next i
    

End Function

but when I do this this way, the function doesn't even call MsgBox (can you tell me why I don't even get an error?). The function doesn't compile and i get #ARG! in my cell. I also tried to do:

Public Function getCOF(data_zawarcia_tf As Date, waluta_tf As String, czas_trwania_tf As Double, _
                        table_to_search As Range, output As Range) As Double
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    table_to_search.Sort Key1:=Range("A1"), _
                     Order1:=xlAscending
    MsgBox "h"
    MsgBox table_to_search(1, 1)
    czas_trwania_tf = -Int(-czas_trwania_tf / 12) * 12
    For i = lastRow To 1 Step -1
        If table_to_search(i, 1) < data_zawarcia_tf And table_to_search(i, 2) = "S" And _
            waluta_tf = table_to_search(i, 3) And czas_trwania_tf = table_to_search(i, 4) Then
            getCOF = output(i)
            Exit For
        End If
    Next i
    

End Function

and this time the function do call MsgBox, but MsgBox table_to_search(1, 1) with ascending sort is same as with descending.

How can I sort my Range object?

Also is there a way to work on Range as on python's/R's dataframe? That is something like

get_value = my_range(my_range(all_rows, 1) = "12.02.2005", my_range(all_rows, 2) = "Los Angeles")(3) that means get_value would be value from 3rd column where 1st and 2nd columns are "12.02.2005" and "Los Angeles"

Pᴇʜ · Accepted Answer · 2022-07-08 08:59:12Z

2

User Defined Functions (UDF) – functions that can be called from cells like the built in Excel formulas – are not allowed to change other Excel cells. So you cannot Sort in them nor can you change the value of other cells. The only thing a UDF can do, is return a value to the cell where it is used as formula.

The issue is: You are not allowed to use Sort in this function. Therefore the code throws an exception at this line and errors.

answered Jul 8, 2022 at 8:59

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Saguro Over a year ago

And how can I sort a range to work on within the function? I mean I load the values as a "table" (dataframe) table_to_search and I want to sort it within the function, do stuff, extract one value that my function will return? In python i would write: def func(table): table = table.sorted() return table[1] or smth like that so the function would get table, sort it only in it, but do not change the data, and return 2nd value of already solved table

Pᴇʜ Over a year ago

@Saguro the only thing you can do is to read all the values into a multidimensional array and then write a procedure to sort that array (do some research there are tutorials on how to sort an array). Then use that array to find your desired data. • The issue is Sort on a sheet will change the cells in the sheet and that is not allowed to be done by UDFs

Saguro Over a year ago

and for real there is no implemented function to sort an array within a function?

Ron Rosenfeld Over a year ago

There is no VBA built-in sort function. There are a number of VBA sorting functions you can find with an internet search. Or, for a 1D array, you could use the ArrayList object (part of System.Collections).

Pᴇʜ Over a year ago

@Saguro there is for real no implemented function. But have a look here on Stack Overflow there is a lot of solutions: stackoverflow.com/search?q=vba+sort+array

Collectives™ on Stack Overflow

VBA function doesn't compile

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related