Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error :: Trim, ...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error :: Trim, ...
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. "QB" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error :: Trim, ...
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. QB wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error :: Trim, ...
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? -- Cheers, Ryan "QB" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error :: Trim, ...
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" wrote: 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? -- Cheers, Ryan "QB" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error :: Trim, ...
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. -- Cheers, Ryan "QB" wrote: 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" wrote: 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? -- Cheers, Ryan "QB" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bubble sort Error (Compile Error: Type Mismtach) | Excel Programming | |||
VBAProject name compile error, not defined at compile time | Excel Programming | |||
error message: compile error, argument not optional | Excel Programming | |||
=IF(TRIM(RIGHT(D4,1))="X",MID(D4,1,LEN(D4)-1),D4) ? remove error | Excel Worksheet Functions |