Getting cells referenced while creating formula

jariquelme · #1 04-16-2018, 01:42 AM

Hi.

I need to get the range selected when I create a formula for use in Interop services.

For example, if i am creating this formula =SUM(A1:A10), I select the range by mouse.

There is any Excel interop property that provides me the A1:A10 range?

TIA

p45cal · #2 04-17-2018, 01:16 AM

msgbox Selection.address(0,0)
?

jariquelme · #3 04-17-2018, 01:28 AM

Hi p45cal.

I need to know the range selected while creating the formula. Selection.Address(0,0) is only set when I accept the edition.

TIA

p45cal · #4 04-17-2018, 01:47 AM

Quote:

Originally Posted by jariquelme

Hi p45cal.

I need to know the range selected while creating the formula. Selection.Address(0,0) is only set when I accept the edition.

TIA

I haven't the foggiest what you mean but:

Code:

range("H5").Formula = "=SUM(" & Selection.address(0,0) & ")"

Perhaps if you described th background behind what you're trying to do…

jariquelme · #5 04-17-2018, 03:12 AM

Hi p45cal.

The background of the process is as following:

1.- Click in one cell.
2.- Put "=SUM(" to create a SUM.
3.- Select a range to sum with mouse.
4.- Getting the selected range in an Interop property to send to another application.
5.- Hit ENTER to save the data

In other forum, Hans Vogelaar tell me that object model can not expose data in edit mode. This could be the problem.

TIA.

p45cal · #6 04-17-2018, 03:33 AM

It is, but do you have to do it before the formula is committed to the sheet?
You could examine the formula directly after committing it to the sheet using a Worksheet_Change event. This in the sheet concerned's code-module:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
myString = Target.Formula
MsgBox myString
If UCase(Left(myString, 5)) = "=SUM(" Then
  strRng = Split(Split(myString, "(")(1), ")")(0)
  MsgBox strRng
End If
End Sub

Not very robust but with a few more IFs perhaps could be made to do what you want.

macropod · #7 04-17-2018, 03:12 PM

Cross-posted at:
https://www.excelguru.ca/forums/show...eating-formula
https://social.msdn.microsoft.com/Fo...forum=exceldev

For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Filling a formula down a column where only one cell value referenced changes?	takamaz	Excel	2	03-11-2018 08:56 AM
Creating a Text Entry That Holds a Property and Can Be Referenced Elsewhere	mike_302	Word	2	01-17-2018 06:03 AM
Shorter VBA for creating borders for cells	mbesspiata	Excel	2	07-28-2014 12:37 PM
Formula to hide '0' from blank referenced cells	formuladummy	Excel	3	05-08-2014 02:33 AM
Help with creating a Formula, please	websiteaog	Excel	9	11-25-2010 04:09 AM