CODEWITHSUNDEEP
vbaexcelexcel-formula
Jaylen
Jaylen

Reputation: 40311

How to permanently store custom function in Excel?

I have a custom VBA function in Excel called Combine. It simply combine multiple cells in a comma separated sting.

I am able to add the new function by doing the following

  1. I clicked ALT + F11
  2. Then I clicked on "Insert" then "Module"
  3. I inserted the following VBA code into it

.

Function Combine(WorkRng As Range, Optional Sign As String = ",") As String
Dim Rng As Range
Dim OutStr As String
For Each Rng In WorkRng
    If Rng.Text <> Sign Then
        OutStr = OutStr & "'" & Rng.Text & "'" & Sign
    End If
Next
Combine = Left(OutStr, Len(OutStr) - 1)
End Function

Then I can easily execute this function like so Combine(A1:A25) to combine all cells A1 to A25 by wrapping them with a single quota and separating the values by a comma.

Then problem is that when I close down excel and open up a new worksheet the function goes away and I can't reuse it.

How can I permanently save this function so I am able to reuse it when new sheet is created?

Upvotes: 1

Views: 5570

Answers (3)

sami
sami

Reputation: 1

write function in Module and when u click save, a dialogue Box will appear, in file Type Locate Excel Add in , this will be registered in excel

Upvotes: 0

Vova
Vova

Reputation: 1

I have used this trick to use custom function (mfdate) in workbook added from VBA:

Workbooks.Add
sheetArr = Range("AM2:AM10").Value
For i = 1 To UBound(sheetArr, 1)
    For j = 1 To UBound(sheetArr, 2)
        If Len(CStr(sheetArr(i, j))) Then sheetArr(i, j) = MfDate(CStr(sheetArr(i, j)))
    Next j
Next i
Range("AM2:AM10").Value = sheetArr

Upvotes: 0

EoinS
EoinS

Reputation: 5482

After you write your function in an Excel file, save the file as an .xlam file. This is an Excel addin file.

Then you need to go to options and addins and load your file.

Now going forward this 'file' will always be open.

You can hit alt-f11 to access it anytime.

link with details from the famous ozgrid

Upvotes: 2

Related Questions