CODEWITHSUNDEEP
vbaexcelcompiler-errors
Edward G-Jones
Edward G-Jones

Reputation: 585

VBA Compile error

I have very little experience with VBA and the last time I used it was years ago. Heres my VBA:

Function MacIDGen(total, current)
If total - current = 0 Then
    current -1
Else
    current 1
End If
    Macro1 (current)
End Function

Sub Macro1(cur)
    Sheets("Donations").Cells(I2).Value = cur & "Test"
End Sub

It's its own module, no other code in with it at all.

So I have a cell which is calling MacIDGen(). It's passing MacID two other cell values. The error comes whenever it's executed, "Compile Error: Expected Sub, Function, or Property" and highlights the functions method signature.

I have no idea why its kicking this up here, I'm guessing its because I've either missed a major step or something about how you can't have a function in that context or some other such issue.

EDIT As a little extra info, I'm creating these spreadsheets in Excel to serve as random data generation sheets to be imported into a drupal site. I've encountered a problem that cannot be resolved without the use of loops (I also can't hard code the number of iterations either), which formulas don't do.

Upvotes: 4

Views: 5300

Answers (2)

Be'eri Gurtler Har-Tuv
Be'eri Gurtler Har-Tuv

Reputation: 1

If you want to change a cell you can use a sub procedure instead of a UDF, in that case you will need a way to execute it from your spreadsheet such as a commandButton, a ribbon button or a key combination shortcut. If so, your first line should be:

Sub MacIDGen(total, current)

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149295

Regarding the Compile Error, you are getting that because you forgot to use the = sign in current -1 and current 1

Like I mentioned, there are many errors but the main error is that you cannot use a UDF to write to another cell. It really doesn't matter if you break that function into functions or subs. You simply can't write to any other cell. You will get a #Value in the cell where you are using that function.

A Function is used when you want to return something. Now since you are using this as a UDF (user defined function), it should return something and to that particular cell.

If you are writing to the same cell where the function is called from then change your code to 

Function MacIDGen(total, current)     
    If total - current = 0 Then
        current = -1
    Else
        current = 1
    End If

    MacIDGen = current & "Test"
End Function

Upvotes: 2

Related Questions