Trying to evaluate cell referenes in a string

[JeffC]

I have a formula that takes a few parameters. For one of the string
parameters I want to concatenate values from cells in the spreadsheet
directly in the formula.

For example cell A1 = Bob cell B1 = Smith

In another cell I call my formula =My_formula("Name", A1 & " " & B1).
When I press enter on this, my formula is called and is passed two strings:
param1 = Name and param2 = Bob Jones

This is what I would expect, through Excel, but here is my problem:

I have a button, that when pressed, finds cells that contain My_formula and
gets the pertinent data out of them without executing each individually. It
gathers all of the data to make a batch call to a data store. I am parsing
the string
My_formula("Name", A1 & " " & B1). I can parse it and get the two parameters:
param1 = Name and param2 = A1 & " " B1
Excel did not process this, it is being read as a string, therefore the cell
references are not evaluated. Without parsing this even more, is there a way
to get Excel VBA to take something like A1 & " " B1 and evaluate the cell
references so that it returns Bob Smith ?

 

[Mike H]

Jeff,

I'm struggling to understand where

My_formula

comes into it but if you want to build a string from 2 cells in VB try this

mystring = "Name " & Range("A1") & " " & Range("B1")

Mike

 

[JeffC]

My_Formula is a user defined function that can be typed into an Excel cell.
I know that I can append and call the Range function, but I am trying to
figure out how to parse the string passed into the function.

For example

Private Function My_Formula(ByVal param1 As String, ByVal param2) As String
..
..
..
End Function

If I have cells A1 = Bob cell B1 = Smith and then in cell A2 I enter in,

=My_Formula("Name", A1 & " " & B1)

when the function My_Formula gets called, param2 will be the value "Bob
Smith" because Excel is handling the cell references and concatenation.

But now let's say that I'm not going to call My_Formula explicitly, rather
on a button click, I search each cell to see if it contains the string
"=My_Formula(". If it does, I need to parse out the two pieces based on the
"," in the string. When I do this, I have two strings: "Name" and
"A1 & " " & B1" .
I want to be able to turn that literal string into its cell references just
like Excel does.

I hope this makes sense.

 

[Rick Rothstein]

I'm not 100% sure I understand your question. Does this do what you want
(assuming the formula is in A3)?

Frmla = Range("A3").Formula
Arg = Application.Evaluate(Trim(Split(Split(Frmla, ",")(1), ")")(0)))

How you get to the actual argument may change depending on the actual
formula that is being parsed.