Compile error :: Trim, ...

[QB]

I have an excel 2003 xls which works fine on my pc. When I send it to a
colleague, it spits out errors.

When he compiles it it highlight a Trim() command. When we delete the
Trim(), just to test further, it then highlights an integer variable 'i'
which is simply a counter. If a change that and compile further, it
highlight UCase$... and I stopped trying to fix it.

What is going on? Anyone ever seen this before. I check the references
libraries and they are all OK.

Thank you for your input,

QB

 

[Mike H]

Hi,

I don't understand why it would fail to compile TRIM() without seeing the
whole line and it would have helped had you given the error message but one
possibility is that this other machine has 'Option Explicit' in another Sub
which means you must declare all of your variables. i.e

Dim i as Long
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Dave Peterson]

Trim is built into excel's VBA. It should not cause an error.

So if you're getting an error message on this statement, it usually means that
you have an invalid reference in that workbook's project.

Open excel and your workbook
Open the VBE and select your workbook's project.
Then click on: Tools|References
Look for MISSING reference.

Uncheck that missing reference.

Then test your code. If it works ok, then go back to excel and save your
workbook.

 

[Ryan H]

Sounds like you have some incorrect code inside the Trim() and UCase()
methods. Plus, Option Explicit might be at the top of a module which will
throw an error if your variable i is undeclared. Declare i like this Dim i
as Long.

Post your code so we can see what you have. If you don't post cost how are
we suppose to fix it?

 

[QB]

Here is my sub in question, well the first one that gets flagged anyways...

Sub Pop_Click()
On Error GoTo PopQB_Click_Error

If Sheets("Contract variables").Range("B19").Value = "" Then
MsgBox "You must first specify the payment method used by the
client", _
vbCritical, "Unable to complete this operation"
Sheets("Contract variables").Range("B19").Select
Exit Sub
End If
If Sheets("Contract variables").cboServices1 = "" Then
MsgBox "You must first specify the 'Items' to which the sales
receipt item" & _
" should be billed against", vbCritical, "Unable to complete
this operation"
Exit Sub
End If
If Sheets("Contract variables").TaxCodes1 = "" Then
MsgBox "You must first specify the 'Tax Code' to which the sales
receipt item" & _
" should be billed against", vbCritical, "Unable to complete
this operation"
Exit Sub
End If

AddCustomer Trim(Sheets("Contract variables").Range("B6").Value), _
Trim(Sheets("Contract variables").Range("B7").Value), _
Trim(Sheets("Contract variables").Range("B8").Value), _
Trim(Sheets("Contract variables").Range("B9").Value), _
Trim(Sheets("Contract variables").Range("B10").Value), _
Trim(Sheets("Contract variables").Range("B11").Value), _
Trim(Sheets("Contract variables").Range("B12").Value), _
Trim(Sheets("Contract variables").Range("B14").Value), _
Trim(Sheets("Contract variables").Range("B5").Value), _
Trim(Sheets("Contract variables").Range("B15").Value)

Exist Sub

Pop_Click_Error:
MsgBox "MS Excel has generated the following error" & vbCrLf &
vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: Sheet1 / Pop_Click" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has
Occured!"
Exit Sub
End sub


The module does not have Option Explicit Set, well... it is currently
commented out. Beyond which, when it compiles the error are flagged in
different modules inconsequentially of the Option Explicit option.

Something else is going on here. My colleague assures me that all the
references are OK, but I will go and have a personal look because this does
appear to be the most likely cause, especially since it does run smoothly on
my PC!

QB

 

[Ryan H]

Ok, I have lots of questions.

What is cboServices1? I assume it is a combobox? I assume it is on your
worksheet? Is it an ActiveX or Forms control?

Same questions for TaxCodes1.

What is AddCustomer? Is this a Sub or another combobox? Please give us as
much details as you can so we don't have to assume stuff, so we can help you.