Common functions location.

[welshgasman]

Hi all,
I use various self created functions in my Excel workbooks.
One such function might be GetLastRow() where I pass in a sheet and column name and get back the last used row.

This save me trying to remember the syntax, especially with my memory these days.
I save these in my personal.xlsb workbook.

Works fine, but I have just realised that if I have to give a workbook to someone else, they will not have my Personal.xlsb, but their own, if they even have one.

Other than having to copy the functions into multiple workbooks, is there any other method I could use please?

TIA

 

[RoryA]

It depends on how you are using them, and your setup. If you are calling them from cells, then you'll probably want to put them in an add-in which ideally you would store in a network location, otherwise the formula path (which you won't see normally) won't be the same on the other user's machine if the add-in is stored locally.

 

[welshgasman]

No, I call them from other vba.
Eg, I asked a question in another thread

Formulae jumping references?

ABCDEFGHI1DateTimeSystolicDystolicHeartWith TimeAVG SysAVG DisAVG Pulse226/091648626/09/2024 14:151417871326/091206326/09/2024 23:15427/091558227/09/2024 09:25527/09134767327/09/2024 14:15627/09143895827/09/2024 22:58728/09156925928/09/2024 08:15829/09147788029/09/2024...

www.mrexcel.com

and ended up, just copyìg the formula to the rows. In that scenario, I used my GetLastRow function.
I run this from workshop open.
However if I give that workbook to.my diabetic nurse, it is not going to find that function.

 

[RoryA]

I call them from other vba

How exactly? If the called code is in a different workbook, are you using Run, or setting a reference, or do you copy the code over?

 

[welshgasman]

BTW I am not getting any email notifications, despite having them set in preferences?

An example.
In my Blood Pressure Workbook I have

Option Explicit

Private Sub Workbook_Open()
Dim lngLastRow As Long
'Refresh sheet to get lastet data.
ActiveWorkbook.RefreshAll
'Get last row in A in sheet qryPresure
Sheets("qryPressure").Select
lngLastRow = getlastrow(ActiveSheet.Name) '.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
'Copy formula in F2 down.
Range("F2").Copy
Range("F2:F" & lngLastRow).PasteSpecial xlPasteFormulas
Range("F" & lngLastRow).Select
Sheets("Chart").Select
ActiveWorkbook.Save
End Sub

and in my Personal.xlsb I have

Public Function GetLastRow(pstrSheet As String, Optional pstrColumn As String) As Long
' Return last used row for sheet and column passed in
Dim lngLastRow As Long
Dim sht As Worksheet

Set sht = Sheets(pstrSheet)
If pstrColumn = "" Then pstrColumn = "A"

lngLastRow = sht.Cells(ActiveSheet.Rows.Count, pstrColumn).End(xlUp).Row
GetLastRow = lngLastRow
Set sht = Nothing

End Function

Now of course, it is only a snippet and I could copy it into the BP workbook.
However if I decide to enhance that is some way (same would apply to other functions) then I have to recopy.

I have not used any addins in Excel.
How would I go about that, and presumably that addin moves with the workbook?

 

[RoryA]

Unless you have set a reference to your personal macro workbook, that code will only work if the getlastrow function is also in the BP workbook already.

 

[welshgasman]

Sorry, my memory is not what it was.

Yes, I can see I set a reference to the Personal.xlsb and can now remember doing it after searching on how.

However, now that is there, if I was to give you the workbook, would it still work?
I know it would not find the data, as that comes from an Access DB on this laptop. So I suppose in this case there is no real need as the Refresh is not going to increase the rows of data. It is still going to run however as it is in the Workbook.Open event?
However if this was another function that *could* produce another result, would that work, if I sent it to you, for example.?

 

[RoryA]

No, because I don’t have the code, so the reference would be broken.
Unless you have a lot of workbooks you need to share, I’d just copy the relevant code across.

 

[welshgasman]

OK. Thank you.