+ Reply to Thread
Results 1 to 5 of 5

VBA, User-defined formula argument referring to cell value calculated by Excel formula

  1. #1
    Registered User
    Join Date
    10-17-2017
    Location
    Haiti
    MS-Off Ver
    MS Office 2010
    Posts
    11

    VBA, User-defined formula argument referring to cell value calculated by Excel formula

    Hi all,

    I'm using this VBA code to show some QR codes in my spreadsheet.

    Please Login or Register  to view this content.
    The problem I have is that the argument of the user-defined formula "name" has to refer to a cell "G8" that contains a formula in it (please refer to the picture below). If in "G8" I write "8816" everything is working perfectly but if I keep the formula in "G8", the user-defined formula "getImage4" is not working. Any hint?

    BfBMtw7.png

    Thanks,
    Stefano
    Last edited by ARAGORN II; 10-25-2017 at 07:56 AM.

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,244

    Re: VBA, User-defined formula argument referring to cell value calculated by Excel formula

    The problem is this line:
    Please Login or Register  to view this content.
    Try replacing that line with:
    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    10-17-2017
    Location
    Haiti
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: VBA, User-defined formula argument referring to cell value calculated by Excel formula

    Hi Olly, thank you very much for your reply. It works perfectly!

    I just noticed another issues with this code, not depending on the change you just suggested me.

    Basically I have a "REPORT_general_data" worksheet where the cells are fed by values from the "MULTI-FIELD_search_form" worksheet.

    IQn7oN3.png

    The "MULTI-FIELD_search_form" worksheet contains a table updated through a MS Query based on the parameters given in a form I created (picture below).

    UCBk7b5.png

    Through the check-boxes (first picture) I can select the item for which I want to generate the report and, thanks a V.LOOK function, the data, related to the item selected, will fill the row right below the orange header (second picture).

    The "REPORT_general_data" worksheet takes then these data to populate the report. Among these values there is also the value (cell "G8" in my first message), used in the user-defined function, object of the thread. I paid attention to write a formula in "G8" that gives 0 if the V.LOOK search returns an error.

    The issue is that when I try to deselect a check-box (first picture) OR to select another one, Microsoft Excel closes saying that "it stopped working". Any idea why?

    I hope my description was clear enough, if not, please let me know and I'll try to be more specific.

    P.S. I'm also sure that the issue depends on the code above-mentioned, since it's enough I delete the formula referring to this code and everything is working fine when selecting/deselecting the check-boxes.

    Thanks,
    Stefano

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,244

    Re: VBA, User-defined formula argument referring to cell value calculated by Excel formula

    No idea. Really hard to diagnose, with no error code.

  5. #5
    Registered User
    Join Date
    10-17-2017
    Location
    Haiti
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: VBA, User-defined formula argument referring to cell value calculated by Excel formula

    Yes, exactly, I can imagine. Unfortunately Excel simply closes with no additional info.

    Thanks anyway,
    Stefano

+ 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. Replies: 2
    Last Post: 12-28-2015, 10:08 PM
  2. Scripting Creation of Defined Names referring to a formula
    By Feicstur in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-06-2015, 06:08 PM
  3. Replies: 4
    Last Post: 03-15-2014, 11:01 PM
  4. [SOLVED] User defined function takes array as argument but won't work with range
    By BuffaloFan32 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-20-2013, 12:50 PM
  5. diasable calling of user defined functions in excel formula
    By maulika in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-17-2012, 02:48 AM
  6. Excel 2010 Not Calculating Cells With User-defined Formula
    By ld_pvl in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-08-2011, 03:15 PM
  7. getting the column address of an argument to a user defined functi
    By Salman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-11-2006, 11:00 AM

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