CODEWITHSUNDEEP
vbams-access
mongoose00318
mongoose00318

Reputation: 131

Format - Expected Array

I keep getting an error when I try to format this number. I've done it in VBA before and I tried to change the SOPID to a variant, a string, and an integer.

Dim SOPID As Integer
SOPID = DMax("file_id", "tblSOP") + 1

'Output test data
MsgBox (format(SOPID, "000"))

I have no idea what I am doing wrong.

Upvotes: 0

Views: 315

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Assuming the code was pasted directly from your IDE, the casing of format is suspicious; that would be Format, unless there's a format variable or function that's in-scope, ...and that's being invoked here.

Look for a public (could be implicitly Public, or if it's in the same module then it could also be Private) format function procedure that expects an array argument: that's very likely what's being invoked here.

Rubberduck (free, open-source; I manage this project) can help you easily identify exactly what's being invoked and an inspection would tell you about any shadowed declarations, but to be sure you can fully-qualify the function call to avoid inadvertently invoking another function that's in scope:

MsgBox VBA.Strings.Format$(SOPID, "000")

Note that there are no parentheses around the argument list of a parameterized procedure call in VBA; the parentheses you have there are surrounding the first argument and making the expression be evaluated as a value that is then passed to the invoked function: this isn't necessary.

Also note the $: the VBA.Strings.Format function takes and returns a Variant; VBA.Strings.Format$ takes and returns a String. If you aren't dealing with any Null values (an Integer couldn't be Null), consider using the String-returning alias.

Upvotes: 3

Related Questions