Evaluate Formula From Another Sheet as Current Sheet

Question

I'm trying to use Eval() to take some functions stored on Main and evaluate them with values from Sheet2. So far static references to Main and pure text work, but I'm having trouble referencing relative cells.

Ideally, the Main worksheet combines static and relative references and applies the function down the column to rows in a table (such that it's A7, A8, ... in this example). The single quotes are for forming SQL. Here is one of the functions I'm trying to implement.

=CONCATENATE(Main!$B$30, "'", A7, "', '", B7, "', '", C7, "', '", D7, "', '", E7, "', '",F7, "', '",G7, "', '",H7, "', '",I7, "', '",J7, "', '", K7, "', ",$B$4,Main!$B$35)

I think the problem is in my Eval function, it's not evaluating A7 as a cell value in the active sheet. I was looking into a way to change the function to something like CurrentSheet!A7, but couldn't find the proper syntax.

Function Eval(Ref As Range)
   Dim shCurrent As Worksheet: Set shCurrent = ActiveSheet
   Application.Volatile
   Application.ThisCell.Parent.Activate
   Eval = Application.ThisCell.Parent.Evaluate(Ref.Formula)
   shCurrent.Activate
End Function

I'm referencing this Eval function in Sheet2 like this, copied down my table column where Main!$E$5 is a cell that has the above excel function in it.

=EVAL(Main!$E$5)

ANSWER

I took bwyn's example and modified it for my use. This is what the above excel function ended up looking like as a VB function. Sending in the row number as a parameter makes the calling function the most generic, allowing the spreadsheet master to extend the "A" and "K" values as needed in one location.

Function EvalInsert(rowNum As Integer)
  Dim shCurrent As Worksheet: Set shCurrent = ActiveSheet
  Dim shMain As Worksheet: Set shMain = Sheets("Main")
  Dim insertClose As String: insertClose = "'));"
  Dim openParen As String: openParen = "'"
  Dim closeParen As String: closeParen = "', "
  Application.Volatile

  Set insertOpen = shMain.Range("B30")
  Set tableId = shMain.Range("B33")
  Set TableName = shCurrent.Range("B1")
  Set insertValues = shCurrent.Range(Cells(rowNum, "A"), Cells(rowNum, "K"))

  EvalInsert = insertOpen.Value

  For Each c In insertValues.Cells
     EvalInsert = EvalInsert & openParen & c.Value & closeParen
  Next

  EvalInsert = EvalInsert & tableId & TableName & insertClose

End Function

I called this using

=EvalInsert(ROW())

bwyn bwyn · Accepted Answer · 2016-06-16T16:41:08

In your concatenate formula, it seems that only A7 changes. If this is true, then you could rewrite the Eval function as follows:

Function Eval(currShRelRefStr As Range)
   Dim shCurrent As Worksheet, shMain As Worksheet
   Dim mainRng As Range, currShRelRef As Range, currShStaticRef As Range
   Dim aStr As String

   Set shCurrent = ActiveSheet
   Set shMain = Sheets("Main")

   Set mainRng = shMain.Range("B30")
   Set currShRelRef = shCurrent.Range(currShRelRefStr.Value)
   Set currShStaticRef = shCurrent.Range("B3")
   aStr = "a string"

   Eval = mainRng.Value & aStr & currShRelRef.Value & currShStaticRef.Value

End Function

Evaluate Formula From Another Sheet as Current Sheet

1 Answers