none
Selected range while creating formula RRS feed

  • Question

  • Hi.

    I need to get the selected range while I am creating a formula. For example, I create the formula =SUM(A1:A10). How can I get the A1:A10 range? There is any Excel interop property that provides me the A1:A10 range?

    TIA

    Monday, April 16, 2018 8:50 AM

All replies

  • In Excel VBA, Selection.Address will return the string "$A$1:$A$10". To get "A1:A10", it would be Selection.Address(False, False)

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, April 16, 2018 11:44 AM
  • Hi.

    I need to get this range before accept the formula editing. I need to know the range selected while i am creating the formula.

    TIA.

    Monday, April 16, 2018 11:48 AM
  • You could assemble a formula like this:

    strFormula = "=SUM(" & Selection.Address & ")"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, April 16, 2018 12:24 PM
  • Hi Hans.

    Thanks for you attention. I need to know the cells selected while entering a formula. Selection.Address only is populated when accept the modification. In other words, I need to know the range selected in edit mode.

    Regards.

    Monday, April 16, 2018 1:15 PM
  • Ah - I cannot help you with that, sorry. I only do VBA, and what the user selects during edit mode is not exposed to the VBA object model.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Ashidacchi Monday, April 16, 2018 5:03 PM
    Monday, April 16, 2018 1:31 PM
  • Hi Hans.

    Thanks you for your time.

    Monday, April 16, 2018 1:42 PM
  • Hi Juan Antonio Riquelme Cutillas,

    From your description, It looks like you want to access the range from formula bar.

    As Hans Vogelaar MVP already informed you that It is not possible to access the range from formula bar.

    If you think that you got the answer for your questions then I suggest you to mark the suggestion given by Hans Vogelaar MVP as an answer.

    If you can inform us your overall goal then we can try to find a work around for you to solve this issue.

    If you have any further questions then let us know about it.

    We will try to provide you further suggestions.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 17, 2018 7:25 AM
    Moderator
  • Hi.

    This is no an answer for my problem if COM object model can provides this inforamtion.

    If no, the answer provided by Hans is correct.

    Thanks.

    Tuesday, April 17, 2018 10:05 AM
  • Hi Juan Antonio Riquelme Cutillas,

    You had asked,"This is no an answer for my problem if COM object model can provides this information. "

    VSTO Com Add in also share the same object model which is used in VBA.

    So all the methods, Properties , Events , everything will remain same.

    So the situation will remain same. In that also you are not able to access the range from formula bar.

    I suggest you to mark the suggestion given by Hans Vogelaar MVP as an answer, so that we can close this thread.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 18, 2018 2:42 AM
    Moderator
  • Hi Juan Antonio Riquelme Cutillas,

    Is your issue is solved now?

    I find that you did not done any follow up for a long time.

    If your issue is fixed by you then I suggest you to post your solution and mark it as an answer.

    If your issue is still exist then try to refer the suggestions given by the community members.

    If you think that the suggestions given by the community member can solve your issue then mark the helpful suggestion as an answer.

    It will help us to close this thread and it also can be helpful to other community members who will meet with same kind of issues in future.

    If you have any further questions then you can let us know about it, We will try to provide you further suggestions to solve it.

    I suggest you to update the status of this thread and take appropriate actions to close it.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 2, 2018 9:38 AM
    Moderator