Compile error: Can't find project or library

[Melanie]

i get this error even after pasting in tried and true code. I am trying to
paste this code in a workbook I got from someone else. I am not sure why it
doesn't work (something disabled?)

seems like it doesn't like "Visual Basic for Applications" functions.

what do i need to do to make this work?

THANKS!!

 

[Gord Dibben]

Melanie

When in the VBE go to Tools>References and see if any references are marked
as missing.

You may want to post your code here for trouble-shooting.


Gord Dibben MS Excel MVP

 

[Melanie]

I think I got this from you! It works in one of my other workbooks. Just
not from a workbook I got from someone else. It gives me an error at the
"Right" function. If I delete this statement, it stops at the "environ"
function.

Private Sub Workbook_Open()
Dim sFile As String
Dim sText
Dim ff As Long

sFile = Application.DefaultFilePath
' or maybe
'sFile = ThisWorkbook.Path
If Right$(sFile, 1) <> "\" Then sFile = sFile & "\"

sFile = sFile & "logTest.txt"

sText = Environ("Username") & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss")
'Environ("Username") is the login name of user opening the workbook
ff = FreeFile
Open sFile For Append As #ff
Print #ff, sText
Close #ff
End Sub

 

[Gord Dibben]

Did you check for any missing references?

I tested with this line remmed out

If Right$(sFile, 1) <> "\" Then sFile = sFile & "\"

Code still works properly in my setup............from any workbook opened
from any folder.

You are saying that when you delete that line you get an error at

sText = Environ("Username") & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss")


Gord

 

[Gord Dibben]

I misspoke.

If I rem out the line

If Right$(sFile, 1) <> "\" Then sFile = sFile & "\"

The code does nothing.............no error, just nothing.


Gord

 

[Melanie]

I checked and yes there was a missing library. I unchecked it because I
don't think it is important for my purposes.
Now I get an error at the FORMAT statement saying that it has "wrong number
of arguments or invalid property assignment"

 

[Melanie]

I checked and yes there was a missing library. I unchecked it because I
don't think it is important for my purposes.
Now I get an error at the FORMAT statement saying that it has "wrong number
of arguments or invalid property assignment"

 

[Gord Dibben]

I assume you have copied the code directly from the posting and have no
typos.

sText = Environ("Username") & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss")

Try experimenting with either of these.................

sText = Application.UserName & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss")
sText = "hoohah" & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss")

If you wish, you could email the workbook to me.

gorddibbATshawDOTca

Change the AT and DOT


Gord

 

[Melanie]

i tried both and they still don't work with the same compile error.
i'm pretty sure it has something to do with the workbook... there are no
typos in my code.
unfortunately i can't send this workbook as it's work-confidential.
oooh well... thanks anyway.

 

[Melanie]

I should add that if I delete the Format command, it works (but no date)

 

[Gord Dibben]

I thought you said you got rid of the comile error by checking a missing
reference?


Gord

 

[Gord Dibben]

That I don't understand.

If no typos, Format(Now, "yyyy-mm-dd hh:mm:ss") should work properly.

You do get the logtest.txt file created and the appending of Username works
each time you open the workbook?

Something we're missing but I can't put my finger on it.

Maybe someone else can see the fault?


Gord

 

[Melanie]

maybe the Excel workbook is down rev?
even though the Visual Basic for Applications is checked off in the
'References' section, for some reason maybe it isn't there??
i don't know... it's strange because the code works in my other workbooks.
just not this one.

 

[Gord Dibben]

Which version of Excel are you running?

Make a copy of the workbook.

Delete the sheets with sensitive information.

Save the new workbook in any version and send to me.

gorddibbATshawDOTca


Gord

 

[Gord Dibben]

For the edification of anyone who was follwing this thread..............

Melanie sent me the workbook.....scaled down.

The problem with the Format statement was that she had a module with some
macros.

One of them was Sub Format()

Excel barfed on this until I changed the name to Format22

All is well.


Gord