VBA Compile error

Question

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.

There are many errors but the main error is that you cannot use a UDF to write to another cell. — Siddharth Rout
– Siddharth Rout, Commented Jan 6, 2014 at 16:55
And so that's why I'm calling the Macro. Also the error occurs on the first line, it's no where near getting to writing yet :/ — Edward G-Jones
– Edward G-Jones, Commented Jan 6, 2014 at 16:59

Siddharth Rout · Accepted Answer · 2014-01-06 17:16:24Z

2

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

edited Jan 6, 2014 at 17:16

answered Jan 6, 2014 at 17:03

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Edward G-Jones Over a year ago

This has fixed the issue. However when you say that you cannot change a cells value with a UDF; can't I call Macro1, and let that change the values(as I stated above)?

Siddharth Rout Over a year ago

No You can't :) Also after your edit, you don't need the Dim statements so I have removed it from my code.

Be'eri Gurtler Har-Tuv · Accepted Answer · 2014-12-28 16:26:57Z

0

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)

answered Dec 28, 2014 at 16:26

Be'eri Gurtler Har-Tuv

11 bronze badge

Collectives™ on Stack Overflow

VBA Compile error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related