2

I'm trying to save an activeworkbook but when I use the following code, I keep getting the error "compile error: expected function or variable" with the word "format" highlighted.

It boggles my mind because I used the exact same function and format in another macro and it saved the file perfectly. I also made sure they had the same types of variables defined already...

Here's the one line code

ActiveWorkbook.SaveAs Filename:=SavedPath & format(Date, "mmddyyyy") & " 4512 GLUpload.xlsm"

The variable savedpath is fine because when I run this line without the format part, it saves the file, but not sure why this screw it up. Also noticed in my other code, format is capitalized but it's not here.

Improve this question
8
  • @Comintern I tried using 'Format$' but I got the error: "Compile Error" Type-declaration character does not match declared data type". I also checked and the word 'format' isn't used anywhere else in my module. In my colleague's modules, he used formatrow or format4512 as a variable but never format itself. Commented Aug 4, 2016 at 19:20
  • Actually, the only way I can replicate the exact compiler error you get is if I have a Sub named Sub SavedPath(). If it is supposed to return a value, change it to a Function. Commented Aug 4, 2016 at 19:20
  • Also check for a Sub Format with 2 arguments. That also gives the same compiler error. Commented Aug 4, 2016 at 19:27
  • @Comintern Thanks!! Turns out my colleague had a sub called format .... lol, so I just renamed that Question for you, I'm new to this site, why did you reply to my question as a comment rather than as an answer? Commented Aug 4, 2016 at 19:27
  • 1
    @Comintern, is it done yet!? S.Kat, comments are for clarification, queries, and ideas while people work through your problem isolating all possible causes. Answers are reserved for definitive answers, I.e. An answers should be 'this is the fix', not 'try this and see' these comments are a great example of this community coming together to help you Commented Aug 4, 2016 at 19:33

1 Answer 1

Reset to default
5

The compiler error you are getting indicates that VBA is expecting an assignable value (either a literal, a variable, or the return value of a function). This means that one of the identifiers in the statement to the right of the equals sign doesn't fall into those categories. So, either SavedPath is defined somewhere as Sub SavedPath(), or there is a Sub Format(arg1, arg2) defined somewhere (if it had a different number of arguments you would get a "Wrong number of arguments or invalid property assignment" error). The second clue (in the comments) is that changing format to the strongly typed Format$ gave a "Type-declaration character does not match declared data type" error. This points to the compiler not treating the symbol format as a function call (Format$() is the strongly typed version of Format()). The solution is to track down the errant use of the VBA function name and re-name it.

A perfect example of why avoiding VBA keywords and function names is good practice.

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

2 Comments

@SiddharthRout - Testing code for Rubberduck has me clued into all the weird compiler quirks in VBA... ;-)
I was aware of this quirk. In fact there is another stackoverflow post where i suggested the same thing but couldn't find it. I just remember rory was also trying to answer the same one. It is already 1 AM here and I need to head to bed else my wife will surely divorce me :D Let me see if I can find that thread in the morning... nite nite

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.