+ Reply to Thread
Results 1 to 9 of 9

UDF gives compile error: expected:expression

  1. #1
    Registered User
    Join Date
    07-12-2015
    Location
    Uppsala, Sweden
    MS-Off Ver
    2013
    Posts
    28

    UDF gives compile error: expected:expression

    Hello!


    I have a column A containing either "a"s or "b"s. In column B i want to count the number of a:s between each b. For this I'm trying to create a UDF, but I get an error:
    Compile error:
    Expected:expression.

    I would be most grateful if anyone coulp help me with this. The UDF looks like this:

    Please Login or Register  to view this content.
    The idea is that I enter "=counter(A1)" in cell B1, and the UDF then does this:
    - Redefines rng to A2:A15
    - Loop: checks is value in A2 "a". If it is; increase counter by 1, and check A3, A4, ..., until a value isn't "a"; then exit.

    I'm very hesitant about the I use Offset to define the range, but it seems that there is a problem earlier?

    Attached is a workbook showing the intended result.


    Best,
    Alfred
    Attached Files Attached Files

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: UDF gives compile error: expected:expression

    How about just a formula?

    Row\Col
    A
    B
    C
    1
    b
    7
    B1: {=IF(A1<>"b", "", MATCH(1, (A2:A27="b")+(A2:A27=""), 0) - 1)}
    2
    a
    3
    a
    4
    a
    5
    a
    6
    a
    7
    a
    8
    a
    9
    b
    4
    10
    a
    11
    a
    12
    a
    13
    a
    14
    b
    2
    15
    a
    16
    a
    17
    b
    7
    18
    a
    19
    a
    20
    a
    21
    a
    22
    a
    23
    a
    24
    a
    25
    b
    1
    26
    a
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: UDF gives compile error: expected:expression

    Hi adh2

    Check the spelling

    Please Login or Register  to view this content.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  4. #4
    Registered User
    Join Date
    07-12-2015
    Location
    Uppsala, Sweden
    MS-Off Ver
    2013
    Posts
    28

    Re: UDF gives compile error: expected:expression

    shg and jaslake, thank you for your quick replies!

    shg: A formula? Yes why not, it works great! However, I don't fully understand it. Could you please explain how your formula works?
    MATCH(1, (A2:A27="b")+(A2:A27=""), 0) - 1)
    The syntax is MATCH(LookUpValue, LookupArray, MatchType). "0" is exakt match, that I understand. But how is "1" the lookup value, and, especially, what type of array is "(A2:A27="b")+(A2:A27="") "?



    jaslake: How embarrassing! Thank you for pointing it out to me.


    My question is definitely solved with your answers, but I would still want to get my UDF to work, just for the fun of it.

    I removed the "Set" before "Counter = 0", as this seemed to be wrong, but now the UDF gives me a "#VALUE" error instead. If anyone sees why this happens, then I would greatly appreciate that!

    Please Login or Register  to view this content.

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: UDF gives compile error: expected:expression

    Please Login or Register  to view this content.
    You were passing variables as literal text.
    Remember what the dormouse said
    Feed your head

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: UDF gives compile error: expected:expression

    Could you please explain how your formula works?
    Watch the formula evaluate and post back if you still don't understand.

    Excel doesn't see any dependency in your UDF on the rest of column A, so it won't automatically recalculate when a value changes. You can paper over it with Application.Volatile, but it's very bad design practice.

  7. #7
    Valued Forum Contributor
    Join Date
    10-05-2014
    Location
    Regina, SK Canada
    MS-Off Ver
    Excel for Mac 11
    Posts
    330

    Re: UDF gives compile error: expected:expression

    Hi, just 3 points: (Besides the ones mentioned by others)

    1. I'd recommend defining each variable in the function; for example variable c
    Related, use a local variable to do the counting and assign that as the function's result at its end.
    And use another variable to be defined as the range to be checked.

    2. Your description says you want to count cells in the range antil one of them is not value "a".
    As written, it will count any cell in the range that has value "a".

    3. Your range offsets do not refer to the cells as described
    The idea is that I enter "=counter(A1)" in cell B1, and the UDF then does this:
    - Redefines rng to A2:A15
    - Loop: checks is value in A2 "a". If it is; increase counter by 1, and check A3, A4, ..., until a value isn't "a"; then exit.

    Here's my suggested revision . . .

    Please Login or Register  to view this content.
    Hope this helps.
    - Stu
    If this has been helpful, please click on the star at the left.

  8. #8
    Registered User
    Join Date
    07-12-2015
    Location
    Uppsala, Sweden
    MS-Off Ver
    2013
    Posts
    28

    Re: UDF gives compile error: expected:expression

    Thank you all for your input!

    I figured out how the formula works, had no idea that True+False = 1.

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: UDF gives compile error: expected:expression

    You're welcome.

    When Boolean values appear in arithmetic expressions, True is coerced to 1 and False to 0, so False + False = 0, True + False = 1, and True + True = 2.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Compiile error: Expected: expression
    By soldgold in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-01-2015, 04:46 AM
  2. VBA error question (Expected expression) Application.Worksheetfunction
    By Gijsbenjezelf in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-04-2014, 12:40 PM
  3. [SOLVED] Compile Error: Expected: expression
    By Wheelie686 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-30-2013, 08:51 AM
  4. [SOLVED] Compile error: Constant expression required error when merging two Codes
    By Kezwick in forum Outlook Programming / VBA / Macros
    Replies: 8
    Last Post: 06-26-2013, 09:32 AM
  5. [SOLVED] Compile Error: Expected Expression, Syntax Error
    By gjohn282 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-20-2012, 11:28 PM
  6. VBA compilation error : expected expression error
    By raknahs in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-21-2010, 12:57 PM
  7. [SOLVED] if isna vlookup expected expression error
    By harishankarin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-20-2010, 04:16 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1