My VBA function cannot be shared because of full-filespec prefix

  • Hello, first-time caller here. A lifetime ago I wrote assembler, C, and C++ but was not a VB developer. Being a small business owner now (and with Covid19 giving me downtime) I thought I'd try to automate a few things and found my way to VBA and macros.

    I've cobbled together a function that I will use literally hundreds of times - it simply sums numbers below itself while the background color is that same as it's own. It also draws borders around the column of cells as a visual confirmation of how it worked.

    I put my VBA function, =sltcSumColor(), into an otherwise empty spreadsheet, put it on a common drive, and then pointed to it in the File|Options|Add-ins section. That all worked, until I had another user test it... it turns out each usage of the function now has a file-spec in front of it.  My function correctly shows up in the Add-in list, and if I delete the long pathname prefix, the function does work, but when I opened it later I found the full pathnames were back, but this time with the name of the testing user...

    ='C:\Users\Gord\Dropbox\Seabbatical\IT Dept\Office\sltc VBA code.xlam'!sltcSumColor()


    I poked around and see information on Personal.xlsb and XLSTART, and compiling - but I feel like I'm going in circles. How do I properly make my function ready for big-time?


    I've attached a cut-back version of the spreadsheet (OzGrid sample.xlsm) and I believe the VBA code is included too.

    Note: I attempted many variations to put a double-underline on the cell that holds the sum, but never could get it to work and gave up (but pieces of that code are still in there).


    If anybody has any guidance for me it would be much appreciated.

    Thanks in advance,

    Dan.

  • Sorry, I thought that code would have been included .I've put the code into an otherwise empty spreadsheet called, "sltc VBA code.xlam". I've copy/pasted of my function in-line here (the .xlam filetype could not be attached). Unfortunately, the indents are lost.

    Again, this is my humble first attempt with VBA so please excuse what might look like dangerous techniques [flame shields up].

    BTW, "sltc" stands for Seabbatical Long Term Charters (my 2-person business).

    Thanks in advance for any guidance.


  • Hello RoyUK, I thought I'd check back with you on my post.

    I see it's been viewed 580+ times with no reply, so I'm not sure how to interpret this - did I post the code incorrectly and people can't access it? Did I describe it poorly? It seems more about managing code than writing code.


    I image all VBA coders would deal with this any time they aren't the only one using the VBA function. In my scenario, I have one other person who works on the spreadsheets, and when they open it up they see an error in the cell(s) because a fully-qualified file-spec prefix on the function name includes my name. If they remove all that prefix leaving just the function name itself, it works correctly, but later I open the spreadsheet and see an error in the cell because that prefix is back, but this time with their name in the file-spec.


    Unless you are the only person working on the spreadsheet, this would be a very common problem. Additionally, I email the spreadsheet (containing a hundred instances of this VBA function) out to customers, and it needs to work for them too.


    If I may... The MS design seems to work well by having the code in an empty .xlam and using the Add-in feature to resolve the call, but I put that .xlam in a sibling folder in Dropbox (where the actual spreadsheets are too) and that is the file-spec that needs to be used by the Add-in somehow. So then it defaults to the .xlam Dropbox file-spec (allowing all spreadsheets to look to the 'master version' of code), but when the spreadsheet gets sent to the customer, there would need to also be a mechanism that would see the Add-in resolve to an "embedded" version so the function continues to work (obviously an orphaned version at that point). Or, maybe a way to tell Add-in to break the link and use the embedded.


    I feel like VBA has a mountain of features, flags, and setting, but that finding that last nugget of information somewhere out there will determine if my cool little idea is time-saver or a big waste of time. Unfortunately, the more I look, the more I find - VBA, JavaScript, TypeScript, VSTO - climbing mountains is exhausting; my head is spinning. I don't need to talk to Sir Edmund Hillary, just somebody with a compass and some sound advice.


    Please let me know how I might structure my question to get it in front of the right people - many thanks!

  • Where is the code normally? It should have been in the workbook if you expected it to be uploaded with it.


    If it's in another workbook then you need to save the workbook as an addin

  • Hi Roy and thanks for taking a look at this.


    Yes, I saved my VBA function, =sltcSumColor(), into an otherwise empty spreadsheet, saved it in a Dropbox folder (close to where the spreadsheet resides), and then pointed to it in with the File|Options|Add-ins dialog. It shows up with alt-F11.


    That all worked until I had another user test it... it turns out each usage of the function now has a file-spec in front of it.  My function correctly shows up in the Add-in dialog, and if I delete the long pathname prefix, the function works correctly, but when I opened it later on my pc, I found the full pathnames were back, but this time with the name of the testing user (Gord)…


    ='C:\Users\Gord\Dropbox\Seabbatical\IT Dept\Office\sltc VBA code.xlam'!sltcSumColor()


    I would want both of us to be able to share the spreadsheet and then be able to email it to the customer. Obviously everybody would want the function to work seamlessly, there must be a way - I just don't know the trick.


    Thanks again, and I'll look forward to your reply.

  • Roy, thanks for the reply - not what I wanted to see, but at least I can stop chasing my tail.

    This hurts though, especially when sending to customers.


    I suppose I could move the code back into the spreadsheet itself (as opposed to using an Add-in), so it would always be with the spreadsheet for everybody. I saw an option to protect or hide it.


    The (steep) downside would be having the same code duplicated in many spreadsheets, so doing changes would be bad.


    It seems that there is a ton out there in the online help files. Is there a better way for me - JavaScript, TypeScript, VSTO, etc?


    Thanks again.

    Dan