none
How to assign the result of a formula to a variable RRS feed

  • Question

  • Sub InsertTabName()

       'Capture name of current tab and display it in a MsgBox
       'I get the Type Mismatch error  

       Dim tabName As Integer
       'tabName = RIGHT(CELL(""filename"",R[3]C),LEN(CELL(""filename"",R[3]C))-FIND(""]"",CELL(""filename"",R[3]C)))
       tabName = Evaluate("RIGHT(CELL(""filename"",R[3]C),LEN(CELL(""filename"",R[3]C))-FIND(""]"",CELL(""filename"",R[3]C)))")
       MsgBox "The tab name is " & tabName

       'Insert tab name in current cell (Works)

       ActiveCell.FormulaR1C1 = "=RIGHT(CELL(""filename"",R[3]C),LEN(CELL(""filename"",R[3]C))-FIND(""]"",CELL(""filename"",R[3]C)))"

    End Sub



    • Edited by Joe1959 Tuesday, March 8, 2016 4:16 PM
    Tuesday, March 8, 2016 4:13 PM

Answers

  • tabName should be declared as String, not as Integer!

    Try

        Dim tabName As String
        tabName = ActiveSheet.Name


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

    • Proposed as answer by Soliddrew Wednesday, March 9, 2016 11:32 AM
    • Marked as answer by Joe1959 Friday, September 9, 2016 2:49 PM
    Tuesday, March 8, 2016 4:17 PM

All replies

  • tabName should be declared as String, not as Integer!

    Try

        Dim tabName As String
        tabName = ActiveSheet.Name


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

    • Proposed as answer by Soliddrew Wednesday, March 9, 2016 11:32 AM
    • Marked as answer by Joe1959 Friday, September 9, 2016 2:49 PM
    Tuesday, March 8, 2016 4:17 PM
  • Hans, 

    Thank you very much.  That made it work.  

    So why didn't the formula work?  It works in the cell.

    Your time and help is appreciated.  

    Joe

    Tuesday, March 8, 2016 4:25 PM
  • Problem 1: you declared tabName as an Integer instead of as a String.

    Problem 2: the Evaluate function works with cell references in A1 style, not RC style.

    This would have worked:

        Dim tabName As String
        tabName = Evaluate("RIGHT(CELL(""filename"",A1),LEN(CELL(""filename"",A1))-FIND(""]"",CELL(""filename"",A1)))")

    or since you can omit the cell reference if you want to refer to the active sheet,

        Dim tabName As String
        tabName = Evaluate("RIGHT(CELL(""filename""),LEN(CELL(""filename""))-FIND(""]"",CELL(""filename"")))")


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

    • Proposed as answer by Soliddrew Wednesday, March 9, 2016 11:32 AM
    Tuesday, March 8, 2016 8:00 PM
  • Hans,

    Thank you for taking the time to answer my questions.  I picked up a VBA book today and I'm trying to figure out how to fine tune macros and write functions.  So this newbie is filled with gratitude.  

    Sincerely, 

    Joe 

     


    Tuesday, March 8, 2016 8:50 PM