I realize there are thousands of comments and hundreds of questions related to UDF tooltips on this website, external forums and miscellaneous discuss groups. I am new to VBA and Excel really, but have learned a lot recently. With that time I've put in to learn, I've also seen those forums, so please don't send me links to third party workarounds like Excel-DNA etc. This is NOT the same question. I don't want to populate a tooltip, I want to autocomplete the UDF parameters into the cell.
Short and to the point, title says everything, is there a way to autonomously populate a UDFs parameters into the string.
I've searched the Documentation site for anything related to this with no success. So the next step was to find a solution that is relatively easy on the user,extremely friendly on the users computer memory, and not to mention dev friendly that doesn't cost a lot of time, has built in cross-platform compatibility and easy to customize.
As I am new to Excel, I'm not sure how add-ins work in terms of the Workbook object. I can find that out just with trial and error, but the closest thing I've found on the documentation site that resembles my desired outcome is by forcing an autocorrect:
With Application.AutoCorrect
.AddReplacement "=EPP(", "=EPP(payrate,hrs)"
End With
This is quick and gets the job done!!! ........ but that's about it.
This works well for 2 reasons:
- Formulas/UDFs are uniquely initiated, with an
=symbol, so I don't have to do too much coding to figure out what the users are typing among other reasons - Reduces code and error potential. Especially in regards to Win 64bit versions vs 32bit versions needing different syntax, and possible for Mac users when using APIs
For every good reason there is, there are 10 bad ones. I'll highlight a few:
- It allows you to press enter, tab etc to go to the next cell, lower cell etc.
- The cursor goes to the end of the string, which is normal for autocorrect, but not desirable for mandatory parameters to be filled in.
- I'm going to have to manually curate an 'AddReplacement' array
- This is useless in formulas with nested functions
I was able to fix some of those issues, by changing it to the following:
With Application.AutoCorrect
.AddReplacement "=EPP(", "=EPP(<payrate>,<hrs>"
End With
What happens here is if the user presses enter, Excel will autonomously include the closing parenthesis, however, an error will throw in this circumstance, the 'Excel thinks this is a formula error: 1+1 = 2', because Excel thinks it's not a formula due to the <> symbols. So the user now is 100% certain they need to review.
Butttttt..... with all that said and done, one huge obstacle remains, the user MUST press space for this to populate. Ouch. As long as I've used excel, I've never pressed space after selecting a function, I can't imagine that will be a natural thing for anyone to do.
So I've tried a few avenues to circumvent this behavior. Nothing worked so far. I tried using Application.SendKeys within the Autocorrect object:
With Application.AutoCorrect
.AddReplacement "=EPP(", "=EPP("
.Application.SendKeys "^+A" 'CTRL + SHIFT + A also tried " " for space
End With
Next I tried using Application.OnKey event to handle when the Tab key is pressed, so I could decipher if it was after a function/UDF or not, I keep getting an Error stating the Macro may not be available for some reason. I think that's good for now anyways, thinking about that option might be a nightmare to maintain (what's the user currently doing? where is the user? is the user pressing tab on a userform control? etc etc) Don't think that's viable, unless there is something already built in that I couldn't find in that documentation site, then I'm stumped.
Anybody have a solid workaround for the core issue? I don't care for the technicalities right now like setting cursor position etc, just the fundamental autocomplete to occur without pressing space. Basically, I would like to emulate what CTRL + SHIFT + A does after a UDF is entered in a cell
UPDATE 1
Okay, so after playing around with this some more, I have more info to share that may help.
I did play around with Application.OnKey events related to tab, and came to the conclusion that Microsoft has a special event handler for when a formula option is selected in the list of formulas. If someone can help me figure out how to access that, I think we could move forward with this. My Debug.Print string "Tab was pressed" didn't execute on the first Tab press when selecting the formula from the suggested list of formulas. The Application object doesn't recognize it as a tab press because it's selecting something from that list which leads me to believe they have their own event handler for it. Just gotta grab that I think we'll be good to go.
Meanwhile, I found an ugly way to get this to work, without having to press space, by setting Formula Autocomplete to false in Excel > Options > Formulas. The list doesn't populate and if a user just types `=EPP(" it will fill out the rest just as desired! But that's a user specific setting, so this is not optimal for scaling.
