VBA function call from excel

Question

I am creating a function to concatenate all cells in a range in a single cell. Everything works ok with the function.

Function transposerange(Rg As Range)
    Dim xCell As Range
    Dim xStr As String
    For Each xCell In Rg
        If Not IsEmpty(xCell.Value) Then
            xStr = xStr & xCell.Value & " "
        End If
    Next
    transposerange = Left(xStr, Len(xStr) - 1)
End Function

If I save it in the opened workbook, I can call it from excel. If I save this function to my personal workbook, it doesn't appear in excel when I want to call it.

What am I doing wrong?

Have you saved your personal workbook as a macro enabled workbook? — Pankaj Jaju
– Pankaj Jaju, Commented Nov 6, 2017 at 10:41
i don't think so, i see that it's called personal.xlsb. How do I change the file from .xlsb to .xlsm because I can't find it in %appdata%? — Ciprian Banisor
– Ciprian Banisor, Commented Nov 6, 2017 at 10:48

MarcinSzaleniec · Accepted Answer · 2017-11-06 11:46:11Z

1

If you want use this function in your spreadsheets, type Personal.xlsb!transposerange(somerange). If you want just transposerange you must make add-id comprising this UDF.

answered Nov 6, 2017 at 11:46

MarcinSzaleniec

2,2661 gold badge12 silver badges23 bronze badges

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

Collectives™ on Stack Overflow

VBA function call from excel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related