Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 3 of 3

Thread: Excel VBA Compile error: Expected: List separator or )

  1. #1
    Join Date
    16th February 2012

    Excel VBA Compile error: Expected: List separator or )

    Dear All,

    My overall code is to, on click into a cell in a calendar made in excel, output some data into a cell named LiveOutput. To do this I am trying to convert a conditional formatting rule into a piece of VBA in order to formulate the elseif statement. The code (which is incomplete for the complete task) can be seen below.
    Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
        Dim LiveCalendar As Range 
        Dim LiveYear As String 
        Dim LiveBusArea As String 
        Dim LiveOutput As String 
        Dim LiveStart As Range 
        Dim LiveEnd As Range 
        Dim LiveLength As Range 
        LiveYear = ThisWorkbook.Names("LiveYear").RefersTo 
        LiveYear = Mid(LiveYear, 3, Len(LiveYear) - 3) 
        LiveBusArea = ThisWorkbook.Names("LiveBusArea").RefersTo 
        LiveBusArea = Mid(LiveBusArea, 3, Len(LiveBusArea) - 3) 
        LiveOutput = ThisWorkbook.Names("LiveOutput").RefersTo 
        LiveOutput = Mid(LiveOutput, 3, Len(LiveOutput) - 3) 
        Set LiveCalendar = ThisWorkbook.Names("LiveCalendar").RefersToRange 
        Set LiveStart = ThisWorkbook.Names("LiveStart").RefersToRange 
        Set LiveEnd = ThisWorkbook.Names("LiveEnd").RefersToRange 
        Set LiveLength = ThisWorkbook.Names("LiveLength").RefersToRange 
        With ActiveWindow 
            If ActiveCell.Value > 0 And IsNumeric(ActiveCell.Value) = True And InRange(ActiveCell, LiveCalendar) Then 
                 ' Yes, We are in the calendar and it is a number over 1
                Range("LiveOutput").Value = ActiveCell.Value 
            ElseIf Application.IsText(ActiveCell.Value) = True And InRange(ActiveCell, LiveCalendar) And (Application.WorksheetFunction.CountIfs(Indirect(LiveBusArea, ""=Y"" ,LiveStart,DATE(LiveYear,R(Row(Activecell)-4/2)C2,R4C((Column(ActiveCell)-1)/2))) + Application.WorksheetFunction.CountIfs(INDIRECT(LiveBusArea),""=Y"",LiveEnd,DATE(LiveYear,R(Row(Activecell)-4/2)C2,R4C((Column(ActiveCell)-1)/2)),LiveLength,"">1"") + COUNTIFS(INDIRECT(LiveBusArea,""=Y"",LiveEnd,"">"" & DATE(LiveYear,R(Row(Activecell)-4/2)C2,R4C((Column(ActiveCell)-1)/2)),LiveStart,""<"" & DATE(LiveYear,R(Row(Activecell)-4/2)C2,R4C((Column(ActiveCell)-1)/2))))>0" Then 
                 'Within range but day of the week
                Range("LiveOutput").Value = ActiveCell.Value 
                 'Blank so make the output box blank
                Range("LiveOutput").Value = "" 
            End If 
        End With 
    End Sub 
    Function InRange(Range1 As Range, Range2 As Range) As Boolean 
         ' returns True if Range1 is within Range2
        Dim InterSectRange As Range 
        Set InterSectRange = Application.Intersect(Range1, Range2) 
        InRange = Not InterSectRange Is Nothing 
        Set InterSectRange = Nothing 
    End Function 
    When trying to compile the elseif line, I am receiving the error message "Compile error: Expected: List separator or )" at the first ""=Y"", specifically highlighting the 2nd "". This line has been modified from writing a macro with the macro recorder to capture the formula. This part has not been adapted from this so I am at a bit of a loss as to why it is not working and how I can get past this point.

    Thank you for any help offered.


    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    16th June 2005

    Re: Excel VBA Compile error: Expected: List separator or )

    My head hurts just looking at it...

    You use "Indirect", but this is not a VBA method/property/function... it should application.indirect, if you mean to use the Excel function.

    If you ARE using Indirect, then the syntax is wrong, because indirect only takes two arguments... the first one being a reference to a cell and the second argument being the type of reference... so this piece is definitely incorrect VBA syntax and two sides... (the incorrect use of the function name AND the incorrect parameters for the function)....

    Application.WorksheetFunction.CountIfs(Indirect(LiveBusArea, ""=Y"" , 
    Maybe.... this?

    Application.WorksheetFunction.CountIfs(application.Indirect(LiveBusArea), ""=Y"" , 

    Thats just a guess though. Aside from that, I would strongly urge you in the strongest possible terms, to simplify whatever it is you are trying to do.


    In addition

    Check out our new reputation system. Click on the "star" under the post!

    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend


  3. #3
    Join Date
    16th February 2012

    Re: Excel VBA Compile error: Expected: List separator or )

    Thank you Ger. This has been a head hurter for me Today, and the worrying thing is this is likely the simpler bit! (The full code would copy items from a table based on certain rules). I was indeed trying to use the Excel function rather than the VBA function. I will have a look again tomorrow and either hopefully it will work with some "application." added or it will be ditched for something simpler.

    Thank you again.


    Excel Video Tutorials / Excel Dashboards Reports

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Compile Error Expected Sub, Function, or Property
    By vtflee in forum Excel General
    Replies: 11
    Last Post: February 9th, 2012, 23:54
  2. Replies: 1
    Last Post: June 3rd, 2011, 21:55
  3. Compile Error: = Expected After Function Call
    By R++ in forum Excel General
    Replies: 3
    Last Post: December 19th, 2006, 03:53
  4. Compile Error Expected Function or Variable
    By Niggle in forum Excel General
    Replies: 2
    Last Post: July 27th, 2006, 17:37
  5. Compile error expected =
    By Canuck in forum Excel General
    Replies: 1
    Last Post: November 17th, 2005, 10:30


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts