Using RangeName instead of hard coded time in TimeValue Vba Macro

  • Hi guys. the spreadsheet i am currently using pulls in data from bloomberg. in order to pull in the data from bloomberg, and run my calculations off said data, i have had to use the Ontime Now +TimeValue funciton in vba in order for the macro to wait for the bloomberg data to be pulled in before making any calculations. here is the doe that i am using:



    i would like to convert where it says TimeValue("00:00:05") to something more dynamic and linked to a range in the spreadsheet. so instead of TimeValue("00:00:05"), something like TimeValue(range("timevalue").select). BTW i tried to enter a range name and it didnt work.


    Any help would be greatly appreciated. Thank you.

  • Re: Using RangeName instead of hard coded time in TimeValue Vba Macro


    Try playing wit the follwoing


    range(activeworkbook.names("timevalue")).Value to retrieve the value of the cell referred to by the name "timevalue" on the active sheet

  • Re: Using RangeName instead of hard coded time in TimeValue Vba Macro


    thanks for the suggestion. i tried playing around with it but was unable to get it to work. Do you have any other suggestions?

  • Re: Using RangeName instead of hard coded time in TimeValue Vba Macro


    in the immediate window, this worked for me


    print range(activeworkbook.names("thetime")).Value


    it returned a value of 5.78703703703704E-05


    it did fail however if "00:00:05" was entered as a text string, ensure it is entered as a time value without the quotes.

  • Re: Using RangeName instead of hard coded time in TimeValue Vba Macro


    thanks for all your help and working through this problem with me.


    when i tried:
    .OnTime Now + TimeValue(Range(ActiveWorkbook.Names("thetime")).Value), "wti_pnl_calc"
    the macro would error out and the error message was "type mismatch".


    when i tried:
    .OnTime Now + TimeValue(print range(activeworkbook.names("thetime")).Value), "wti_pnl_calc"
    print would get highlighted and i would get compile error message, Expected: expression, with print being highlighted.

  • Re: Using RangeName instead of hard coded time in TimeValue Vba Macro


    MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    *

  • Re: Using RangeName instead of hard coded time in TimeValue Vba Macro


    sorry about that. i have gone ahead and posted the ozgrid link to the other post.

  • Re: Using RangeName instead of hard coded time in TimeValue Vba Macro


    Quote

    All we ask is that you provide the links in your original post to your question in the other forum(s)...


    It's too late to edit your original post - please reply with the link.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!