format$ function

[pradeep_pvnm]

what has to be referenced so that the format$ function works fine.

i ahve a code which uses format$ function and when run errrors out saying cannot find library

code

here is the how it is being called

x=format$(variable,"mm/dd/yyyy hh:mm AMPM")

[Edgemeal]

You don' need to reference anything, are you defining your variables correctly?


example,

Dim x As String
Dim d As Date

d = Now
x = Format$(d, "mm/dd/yyyy hh:mm AMPM")
MsgBox d & vbCrLf & x

[pradeep_pvnm]

this is a production code hence all declarations would be fine(thats the assumption)

let me tell you something else

-when the compiling stops
-i check the refrences and could see a " mssing xyz.vbp" as checked
-when i uncheck it , the project runs fine

any thoughts????

btw i will also look at the variables.

and one more question,
what is this format$ function, i have used the format function previously but not the format$

[westconn1]

i have had this happen before, on a downloaded project, as far as i can remember it was due to some reference that was not available on my machine, i unchecked the reference then the format function worked again

no difference the $ shows it returns a string

[pradeep_pvnm]

pete,

thats exactly whats happening here
i uncheck the mssing one and the project runs.

so what has to be done to fix it

any thoughts ??

[gavio]

Just a wild guess. Someone who wrote it must have named his Function (the one that should be in the library that is missing) the same way so VB is confused

[westconn1]

either find the missing reference library or totally remove it from the project, making sure there are no calls to it anywhere in the project

[RhinoBull]

The actual problem is in the VB's core references - VBA. This a a known problem (in my opinion it's a bug).
If you look at the refernces list then VBA will be there however somehow it gets messed up.
There are a couple of ways to fix it:

- one is to type VBA name space (like VBA.Format) in front of "" function (it may happen to Left, Format, Right, etc functions from the entire VBA library);
- another way is to recreate your entire project by creating new and adding all objects from existing and then save new by overwriting existing;
- perhaps there is another way but I am not aware of it.

First method could be justified if project is small and second when you are dealing with large projects.

btw, the difference between Format and Format$ is data type returned:
one returns Variant and another String so it could be faster.

[gavio]

You can even declare a variable with $, so it becomes a String:

Dim x$

Debug.Print TypeName(x) '<<< String

[si_the_geek]

Correcting the Missing reference is usually by far the easiest solution in the long run - see here for an explanation of how to do it (and the other methods that RhinoBull described).

As the project runs when you simply remove it, the problem is gone - unless you actually need that Reference. To check start your project with a full compile (press Ctrl F5); if it starts you don't need that Reference.

The actual problem is in the VB's core references - VBA. This a a known problem (in my opinion it's a bug).

The problem is not actually related to VBA at all, that is just the most likely library to show an issue (as it is used very regularly, and unlike most libraries provides functions which do not need a parent object).

There is an explanation of a very probable cause for the errors occurring on VBA based lines in the "Side note:" section of my link, and it can easily be reproduced with a different library (such as "ODBC Driver and Data Source Name Functions").

[dilettante]

Yes, it is possible to overload functions and even classes in the global namespace. Most classic VB programmers were never led to think in these terms, but I'm sure it makes more sense to VB.Netters since they live with lots of explicit talk about namespaces.

If you define a Format() function somewhere, any code that tries to use it will pick up the local reference if it is in scope (local to the current module or Public in a static BAS module, etc.). You can still get to the VBA function by qualifying the name as in VBA.Format$(...).


In time-critical code this has some drawbacks. The results are different for code compiled to p-code versus native code.

In some cases using things like VBA.Format$() will be slower than Format$(). I suspect the compiler uses a shortcut to reach the usual form and has to go "the long way" to reach it as an object method.


Most of the VB and VBA library String functions are overloaded themselves, with both Variant and String ($) versions. In general one might expect the $ versions to be faster. Many of them are - but it can also vary between p-code and native code.

In the case of Format, the $ version seems to be somewhat slower than the Variant version in native code. I don't know why. Other functions like Join() seem to produce identical timings with and without $. Things like Left(), Mid(), Right() seem to do much better in the $ versions.

Maybe somebody has a "map" of these that shows when to use $ and when to avoid it? But often the performance difference doesn't matter unless you are using lots of them within a loop doing a ton of work.

I've read No variants please but I don't think the answer is quite as simple as stated there.

[westconn1]

i tested format$ and format to an undeclared variable, both returned a variant/string

[RhinoBull]

The problem is not actually related to VBA at all..

Of course it does - VB "looses" (or however you like to call it) reference to VBA library so how isn't it "related" remains a mystery to me.

[si_the_geek]

It is not just VBA based code where the error occurs (I mentioned another library that gives the error too), and fixing the "Missing:" library always removes the error - you do not need to do anything related to VBA to fix the problem.

[RhinoBull]

What if there isn't anything "missing" at all? It had happened to me 1000 and 1 times. Then what you do?
It's mainly because of VB--->>VBA link that's getting messed up.
The only "fixes" that I was able to find was to recreate project or always use VBA name space for any VBA based function.

[si_the_geek]

Most of the times it comes up on the forums (like in this case), the OP admits there is a Missing Reference. In the majority of the others they don't check (or at least, don't tell us), as somebody has insisted that adding "VBA." is the only way to solve it.

Solving a Missing Reference is often the simplest solution by far, and even with the "VBA." method is still mandatory if you want to compile the project anyway.


If there isn't a Missing Reference then one of the other methods is obviously needed, which is why I put all of the ones I am aware of into the FAQ article - in order of the likely workload involved (the "VBA." last, as even in a relatively small project it is likely to take the most amount of effort).