+ Reply to Thread
Results 1 to 11 of 11

VBA processes before needed cells are calculated

  1. #1
    Registered User
    Join Date
    07-26-2012
    Location
    Waikanae, Wellington, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    12

    VBA processes before needed cells are calculated

    I'm using a script from here: http://www.excelforum.com/excel-form...final-and.html

    The script basically gets a collection of cells and comma separates them with a final "and" (as the last comma)

    My problem is, the VBA script seems to be running before cell values are calculated. If I change student % scores (sheet 1 "Grades") then the "Top Subjects" column is what it should be just prior to the change of data.

    How can I make sure the script executes only when the cells have been fully calculated, so that the results appear accurate when data is updated?

    Please examine the attachment: autograder_lags.xlsm
    Last edited by LancerNZ; 05-23-2014 at 06:47 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: VBA processes before needed cells are calculated

    Can you put the code in

    Please Login or Register  to view this content.
    b
    Last edited by alansidman; 05-23-2014 at 08:04 AM.
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Registered User
    Join Date
    07-26-2012
    Location
    Waikanae, Wellington, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: VBA processes before needed cells are calculated

    Thanks for the reply. I don't understand how to do what you are saying. Excel won't let me sandwich the function between Sub Worksheet_Calculate() and End Sub.

    Here's the function...

    Please Login or Register  to view this content.
    How do I apply what you've said to do?

  4. #4
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,709

    Re: VBA processes before needed cells are calculated

    Open the VBE. In the left window which lists your objects, click on the Worksheet you want to place the code in. Your window on the right will open blank. Click in the left address window and select worksheet. Now in the right address window, select calculate. Copy everything from your code except the First line and last line and paste in. SAVE
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Registered User
    Join Date
    07-26-2012
    Location
    Waikanae, Wellington, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: VBA processes before needed cells are calculated

    Quote Originally Posted by alansidman View Post
    ...Now in the right address window, select calculate...
    Sorry, I'm lost there; where is "calculate"? Here's a screenshot of my VBE.

    vbewherecalc.png

    I'm worried that you're asking me to post the function which relies on specified cells (e.g. rRange As Range) into a global function which might not know where to find them (when it references rRange) because you've asked me not to include the first line which defines rRange, but as I'm a noobie on anything Excel / VBE I'm following along with whatever you suggest.

  6. #6
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,709

    Re: VBA processes before needed cells are calculated

    See the attached .VBE.jpg

    Define your rRange as a Dimension statement

    Please Login or Register  to view this content.
    Last edited by alansidman; 05-23-2014 at 02:25 PM.

  7. #7
    Registered User
    Join Date
    07-26-2012
    Location
    Waikanae, Wellington, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: VBA processes before needed cells are calculated

    Thanks for your reply. I've tried what you suggest but am now getting:
    Compile Error: Function call on left-hand side of argument must retutrn a Variant or object
    See image: error_vbfunction01.png

    This happens with the line of the code:
    Please Login or Register  to view this content.
    My code after following your instructions (did I put Dim rRange in the right place?) is:

    Please Login or Register  to view this content.
    Also, the previous module still exists which is a double up (how do I delete a module? Do I just erase all its lines?)

  8. #8
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,709

    Re: VBA processes before needed cells are calculated

    To delete a module, single click on the module in the left window, click on file and remove module.

  9. #9
    Registered User
    Join Date
    07-26-2012
    Location
    Waikanae, Wellington, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: VBA processes before needed cells are calculated

    Quote Originally Posted by alansidman View Post
    ...click on file and remove module.
    I can't. The remove option is greyed out.

  10. #10
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,709

    Re: VBA processes before needed cells are calculated

    Not sure why that is happening. An alternative means. Click on Alt +F8 to open the Macro selection window. Highlight your macro and click on delete. Does that work?

  11. #11
    Registered User
    Join Date
    07-26-2012
    Location
    Waikanae, Wellington, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: VBA processes before needed cells are calculated

    ALT+F8 brings up the a message box with the previous error message (above) saying that my modified VB is broken. (Compile Error: Function call on left-hand side of argument must return a Variant or object) Looks like we need to fix the newer procedure you directed me in making before we can delete other parts.

    I feel like there are a lot of dead ends being introduced. Could you please look at the file I attached in my original post? It's actually working except that the VB function gets called too early (it's processing immediately, before the cells feeding to it have had their own formulae updated)

    What's meant to be happening (in the excel worbook) is that the right column reports the highest subject score for each student (e.g. English). If a student has equal top score, it will report all of them with commas and "and" like this: "English, Maths and Cooking". The actual formula etc are run from the sheet labelled "BestWorst".

    Edit: I managed to delete the module by cutting and repasting the main VB code (attempt to flush any errors). Deleting the original module might not have been the best move. Now I'm getting Run-time error 424: Object required. I take it the formula cells are trying to call the module I've just deleted. Am going back to previous backup.

    Still don't know how to get the VB modules to not run after (not before) other cells in the workbook are updated. This might be a chicken egg problem, because other cells will use the output.
    Last edited by LancerNZ; 05-23-2014 at 06:18 PM.

+ 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. Help needed Highlighting Cell in a grid from 2 calculated cells
    By steve77 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-03-2013, 06:25 PM
  2. Scheduling Data Needed and Bid Hours Calculated for a certain date
    By smirk100 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-17-2013, 06:52 PM
  3. Macro that processes subfolders
    By jimbofoxman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-17-2009, 02:36 PM
  4. How many excel processes can you open?
    By Stanley in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-17-2006, 07:25 AM
  5. [SOLVED] Display System Processes
    By Ashley in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-21-2005, 11:30 PM

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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.08769 seconds
  • Memory Usage 9,195KB
  • Queries Executed 16 (?)
More Information
Template Usage (35):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (5)bbcode_code
  • (3)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (11)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (11)postbit_legacy
  • (11)postbit_onlinestatus
  • (11)postbit_wrapper
  • (4)showthread_bookmarksite
  • (5)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (48):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1