+ Reply to Thread
Results 1 to 16 of 16

Event Sub from run Function Event

  1. #1
    Registered User
    Join Date
    07-27-2014
    Location
    London
    MS-Off Ver
    2007
    Posts
    16

    Question Event Sub from run Function Event

    Hi All,

    Looking for the syntax that will allow me to code an event sub routine, based off the event of a specific function e.g. findnum being run.

    Haven't been able to find a thread on this.

    Many thanks!

  2. #2
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Event Sub from run Function Event

    I'm not sure what you are after.
    Do you want to run some event code as part of a UDF (is findnum a UDF)?

    Or do you want this UDF to modify some event code.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Registered User
    Join Date
    07-27-2014
    Location
    London
    MS-Off Ver
    2007
    Posts
    16

    Re: Event Sub from run Function Event

    Not sure what your referring to by "UDF" its a standard function e.g.

    Function Findnum(variable)
    End Function

    looking for event sub like e.g

    Sub Workbook_Open()
    End sub

    So something to the effect of

    Sub Findnum_run()
    End Sub

    Findnum is just an arbitrary allocation. Is that possible? I've seen a the effects of a code that does something to this effect, the sheet is locked though so unfortunately can't see how it was done.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Event Sub from run Function Event

    A UDF is a User Defined function.

    You would normally use one on a worksheet, but unfortunately there are no events directly associated with one.

    What would you want the event 'sub' do?
    If posting code please use code tags, see here.

  5. #5
    Registered User
    Join Date
    07-27-2014
    Location
    London
    MS-Off Ver
    2007
    Posts
    16

    Re: Event Sub from run Function Event

    Oh of course! yeah a UDF, effectively I need to populate multiple cells with my array called from a function. I am trying to replicate a function that does this but from everything I've seen on the threads everyone advises this cannot be done. From messing around with the function I thought that I'd identified it was getting around it with some sort of on function run event procedure.

    The code that does it is locked, and the programming team responsible for it don't seem to wish to disclose how they did it frustratingly. I'm not looking to use it in the exact same way as they have applied it, but the example of this set of functions means I know its possible just can't figure out how the managed it. (just to confirm its not ctrl+shift+enter with function=array, its far more elegant)

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Event Sub from run Function Event

    Like I said there's no event associated with a UDF and a UDF will not directly trigger any other event.

    The usual way to return multiple values to multiple cells is to use an array (CSE) function.

    Did the code for that you were given in the other thread not work?

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Event Sub from run Function Event

    Looks like the same issue being discussed here: http://www.excelforum.com/excel-prog...-function.html Mikerickson has provided an array UDF that, I think, should be what the OP is asking for, once he/she understands how to enter and use an array UDF.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  8. #8
    Registered User
    Join Date
    07-27-2014
    Location
    London
    MS-Off Ver
    2007
    Posts
    16

    Re: Event Sub from run Function Event

    No as mentioned in both threads, I am aware of the CSE option, but this is somewhat elegant as it requires you to ctrl+shift+enter, the range. Particularly frustrating if you are not sure how many columns will be populated and pulling perhaps the top 50 ranked items. The formula I have described above populates a grid the the formula in the top left cell. The goal is a tool that can be used by less knowledgeable users. Therefore any function involving a instruction manual is not ideal.

    It's quite frustrating as I have an example of a function that does this, but cannot see the code, and nobody seems to have heard of a method to do this.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Event Sub from run Function Event

    Where exactly are you using the function?

    PS What's wrong with an elegant solution?

  10. #10
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Event Sub from run Function Event

    Another work-around to let a UDF modify cells is to have it pass information to a Collection. The Calculate event will then change the cells as indicated.

    Here's an example. Put =ColorACell(A1:A3,D4) in a cell and watch A1:A3 change color as you put different numbers in D4.

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    07-27-2014
    Location
    London
    MS-Off Ver
    2007
    Posts
    16

    Re: Event Sub from run Function Event

    meant inelegant :-) The function that I'm trying to copy is based off pulling proprietary data from our internal system so can't upload it to the internet unfortunately, which would be the best way to express what I mean.

    Goal is fundamentally is a function, in which you reference the data you want in the function, the function then populates a cell range of the correct size with the array of data automatically. Which is what the other function manages to do. The big benefit of this is its far easier as all the user needs to do is put the function in a single cell.

  12. #12
    Registered User
    Join Date
    07-27-2014
    Location
    London
    MS-Off Ver
    2007
    Posts
    16

    Re: Event Sub from run Function Event

    Hi Mike, thanks let me test that!

  13. #13
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Event Sub from run Function Event

    Is this mysterious function pulling data from a database, or some other data source?

  14. #14
    Registered User
    Join Date
    07-27-2014
    Location
    London
    MS-Off Ver
    2007
    Posts
    16

    Re: Event Sub from run Function Event

    Hmmm passing it through a collection does seem to work, thanks! So that requires feeding in of the range, not very familiar with collections, so a few follow up questions!

    I'm thinking the best option would then to have the array as a public variable and the populating via the above loop through collection, would this be best?

    That formula requires the range be be specified, is their any easy method of selecting the correct relative range based from the cell the function is in a ubound of columns and rows of the array?

    That formula runs every time the sheet calculates, pretty sure that wouldn't work for multiple instances of the same function in a sheet, for example you wouldn't be able to recalc all the cells through shift+F9 correct?

  15. #15
    Registered User
    Join Date
    07-27-2014
    Location
    London
    MS-Off Ver
    2007
    Posts
    16

    Re: Event Sub from run Function Event

    Norrie with regards to your question it pulls from an internal database!

  16. #16
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Event Sub from run Function Event

    The way that I would do it is

    'in the UDF
    test Application.Caller against the result array,
    if it (the range Application.Caller) is the right size, return the array: DONE
    if it is not
    pass the current range (to be blanked)
    pass the range of the correct size
    pass the formula
    End UDF

    ' in the Calculate event
    Blank the current (wrong sized range)
    put the formula in the correct sized range. This triggers the UDF again, which will go out the OK branch.

    Here is an example. Note that this heavily assumes that the result is wanted in a column.
    put =myArrayFtn(A1) in a cell (not in column A) and change the value of A1.
    Then put =myArrayFtn(B1) in a different cell.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Last edited by mikerickson; 08-06-2014 at 01:28 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Userform multipage control - exit event not firing or event order
    By jane serky in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2013, 10:23 AM
  2. Deactivate event appears to be overriding next activate event
    By ezrizer in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-01-2013, 10:15 AM
  3. Replies: 4
    Last Post: 11-07-2012, 04:02 PM
  4. How to prevent SelectionChange event firing before Change event?
    By franklyn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-29-2012, 05:17 AM
  5. MsgBox in Enter event causes combobox not to run Change event
    By Richard in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-06-2006, 10:55 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1