none
Adding dynamic named ranges to Excel using VBA from inside MS Project

    Question

  • Hello,

    I created a macro in VBA that runs from within MS Project. That macro exports some task related data to Excel. I want to define a dynamic named range in the newly created Excel book.

    A simple range works, but when I want to add an OFFSET function it doesn't work.

    See the following simplified macro to illustrate the problem. Don't forget to go to Tools, References and enable the Excel Office library.

    Sub Macro1()
        Dim XlApp As Excel.Application
        Dim XlBook As Excel.Workbook
        Dim XlSheet As Excel.Worksheet
        Dim r As Long, n As Long
        n = 10
        
        Set XlApp = New Excel.Application
        XlApp.Visible = True
        Set XlBook = XlApp.Workbooks.Add
        Set XlSheet = XlBook.Worksheets.Add
    
        XlSheet.Name = "Project Data"
        XlSheet.Cells(1, 1).Value = "Task Data"
        XlSheet.Cells(1, 2).Value = n
        For r = 2 To n
            XlSheet.Cells(r, 1) = Rnd()
        Next r
        XlBook.Names.Add Name:="FirstTask", RefersTo:=XlSheet.Cells(2, 1)
        XlBook.Names.Add Name:="NumberOfTasks", RefersTo:=XlSheet.Cells(1, 2)
        XlBook.Names.Add Name:="AllTasks", RefersTo:="=OFFSET(FirstTask,0,0,NumberOfTasks,1)"
    End Sub
    

    It fails on the last line of the macro with a runtime error 1004 "The formula you typed contains an error"

    When I execute the macro from within Excel the dynamic ranges work. But from MS Project with VBA it doesn't work.

    Does anyone know why? Is there some way to define the named range from Project?


     

    Sunday, October 16, 2011 4:08 PM

Answers

  • Yeah, I saw where the setting resided. I was searching for an elegant solution to my problem regardless of what separator the user has configured on his system.

    Sub Macro1()
        Dim xlApp As Excel.Application
        Dim XlBook As Excel.Workbook
        Dim XlSheet As Excel.Worksheet
        Dim XlSep As String
        Dim namedArray() As Variant
        
        Dim r As Long, n As Long
        n = 10
        
        Set xlApp = New Excel.Application
        XlSep = xlApp.International(XlListSeparator)
        xlApp.Visible = True
        Set XlBook = xlApp.Workbooks.Add
        Set XlSheet = XlBook.Worksheets.Add
    
        XlSheet.Name = "Project Data"
        XlSheet.Cells(1, 1).Value = "Task Data"
        XlSheet.Cells(1, 2).Value = n
        For r = 2 To n + 1
            XlSheet.Cells(r, 1) = Rnd()
        Next r
        
        XlBook.Names.Add Name:="FirstTask", RefersTo:=XlSheet.Cells(2, 1)
        XlBook.Names.Add Name:="NumberOfTasks", RefersTo:=XlSheet.Cells(1, 2)
        namedArray = Array("=OFFSET(FirstTask", "0", "0", "NumberOfTasks", "1)")
        XlBook.Names.Add Name:="AllTasks", RefersTo:=Join(namedArray, XlSep)
    End Sub
    
    

    I am now using an array to hold the named formula and using join with the configured separator to localize the named range.

    Should work on all systems now.

    • Marked as answer by oohlaf Sunday, October 16, 2011 8:41 PM
    Sunday, October 16, 2011 8:33 PM

All replies

  • I just tried it and it doesn't give me the error...
    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.
    Sunday, October 16, 2011 6:47 PM
  • Really?

    Which version of Office are you using?

    I use MS Project 2007 with Excel 2007.

    Did you also start MS Project, opened up the macro editor with alt+F11, pasted the above code, and ran it.

    For me it starts Excel and loads the 10 random numbers. When I switch back to the macro editor of MS Project it shows an error and didn't execute the last statement.

    Sunday, October 16, 2011 7:03 PM
  • 1) I tried Project Professional 2010 with Excel 2003

    2) I don't have Project Professional 2007 but I can test Project Professional 2010 with Excel 2007 and 2010 if you want me to?

    >>>>Did you also start MS Project, opened up the macro editor with alt+F11, pasted the above code, and ran it.

    3) Yes. I pasted the code in a module and ran it :)


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.
    Sunday, October 16, 2011 7:11 PM
  • 2) I don't have Project Professional 2007 but I can test Project Professional 2010 with Excel 2007 and 2010 if you want me to?

    Oh yes, please test with Excel 2007. I can't get my head around this :(

    I also tried to run the macro as is from inside Excel 2007 and it also throws an error on the last line with 1004: "Application-defined or object-defined error".

    Thanks for testing. 

    Sunday, October 16, 2011 7:20 PM
  • >>>I also tried to run the macro as is from inside Excel 2007 and it also throws an error on the last line with 1004: "Application-defined or object-defined error".

    I just tried it from within Excel 2007 and it works just fine.

    Installing Project 2010 to test it with Excel 2007. Gimme 15 mins


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.
    Sunday, October 16, 2011 7:23 PM
  • Ok Tested Project Professional 2010 With Excel 2007 and the above code just works fine :)
    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.
    Sunday, October 16, 2011 7:37 PM
  • I found it. When I change the last line to "=OFFSET(FirstTask;0;0;NumberOfTasks;1)" so using semi-colons instead of comma's it works without error.

    Can you test if a semi-colon works in your version. Otherwise it's a locale related issue. Hopefully that also works for you otherwise I need to detect what kind of separator to use.

    Sunday, October 16, 2011 7:41 PM
  • Yes it is a locale related issue :)


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.
    Sunday, October 16, 2011 7:43 PM
  • Jup, in control panel regional settings my list separator is a semicolon.

    Great, now I need to detect what the separator is. Hmmm, no idea where to look.

    Thanks for testing and going through the effort to install different versions even!

    Sunday, October 16, 2011 7:49 PM
  • Found it

    XlApp.International(xlListSeparator)
    

     

    Sunday, October 16, 2011 7:54 PM
  • >>>Great, now I need to detect what the separator is. Hmmm, no idea where to look.

    It's under "Cutomize this format" see snapshot below.

     


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.
    Sunday, October 16, 2011 7:54 PM
  • Yeah, I saw where the setting resided. I was searching for an elegant solution to my problem regardless of what separator the user has configured on his system.

    Sub Macro1()
        Dim xlApp As Excel.Application
        Dim XlBook As Excel.Workbook
        Dim XlSheet As Excel.Worksheet
        Dim XlSep As String
        Dim namedArray() As Variant
        
        Dim r As Long, n As Long
        n = 10
        
        Set xlApp = New Excel.Application
        XlSep = xlApp.International(XlListSeparator)
        xlApp.Visible = True
        Set XlBook = xlApp.Workbooks.Add
        Set XlSheet = XlBook.Worksheets.Add
    
        XlSheet.Name = "Project Data"
        XlSheet.Cells(1, 1).Value = "Task Data"
        XlSheet.Cells(1, 2).Value = n
        For r = 2 To n + 1
            XlSheet.Cells(r, 1) = Rnd()
        Next r
        
        XlBook.Names.Add Name:="FirstTask", RefersTo:=XlSheet.Cells(2, 1)
        XlBook.Names.Add Name:="NumberOfTasks", RefersTo:=XlSheet.Cells(1, 2)
        namedArray = Array("=OFFSET(FirstTask", "0", "0", "NumberOfTasks", "1)")
        XlBook.Names.Add Name:="AllTasks", RefersTo:=Join(namedArray, XlSep)
    End Sub
    
    

    I am now using an array to hold the named formula and using join with the configured separator to localize the named range.

    Should work on all systems now.

    • Marked as answer by oohlaf Sunday, October 16, 2011 8:41 PM
    Sunday, October 16, 2011 8:33 PM
  • >>>Should work on all systems now.

    Yes it will :)


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.
    Sunday, October 16, 2011 8:39 PM
  • Another approach :)

    Option Explicit
    
    Sub Macro1()
        Dim XlApp As Excel.Application
        Dim XlBook As Excel.Workbook
        Dim XlSheet As Excel.Worksheet
        Dim r As Long, n As Long
        Dim xlFormula As String, NewFormula As String
        
        n = 10
        
        Set XlApp = New Excel.Application
        XlApp.Visible = True
        Set XlBook = XlApp.Workbooks.Add
        Set XlSheet = XlBook.Worksheets.Add
    
        XlSheet.Name = "Project Data"
        XlSheet.Cells(1, 1).Value = "Task Data"
        XlSheet.Cells(1, 2).Value = n
        For r = 2 To n
            XlSheet.Cells(r, 1) = Rnd()
        Next r
        XlBook.Names.Add Name:="FirstTask", RefersTo:=XlSheet.Cells(2, 1)
        XlBook.Names.Add Name:="NumberOfTasks", RefersTo:=XlSheet.Cells(1, 2)
        
        xlFormula = "=OFFSET(FirstTask,0,0,NumberOfTasks,1)"
        NewFormula = Replace(xlFormula, ",", XlApp.International(xlListSeparator))
        XlBook.Names.Add Name:="AllTasks", RefersTo:=NewFormula
    End Sub
    



    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.
    Sunday, October 16, 2011 8:42 PM
  • Try it and let me know if it worked for you :)
    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.
    Sunday, October 16, 2011 8:46 PM
  • That also works.

    Not sure which variant I'll use in my project though. The replace might change commas inside quotes which aren't a separator. Then again, in a named range formula that is unlikely.

    Sunday, October 16, 2011 9:00 PM
  • >>>Then again, in a named range formula that is unlikely.

    Yup :)

    Also breaking the formula and storing in an array could prove a tedious job ;) Replace in such scenarios will be faster. But then it is just my opinion.


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.
    Sunday, October 16, 2011 9:07 PM
  • What I find surprising though is that Formula and FormulaR1C1 members of a cell do not have this problem (otherwise I would have found out sooner). They automatically translate the separator to the localized one. It's only the RefersTo in a named range that doesn't.

    Sunday, October 16, 2011 9:18 PM