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
0
Utf
1/21/2010 8:41:02 PM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
1101 Views

Similar Articles

[PageSpeed] 25

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
0
Utf
1/21/2010 9:31:02 PM
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
0
Dave
1/21/2010 9:31:58 PM
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
0
Utf
1/21/2010 10:23:01 PM
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
0
Utf
1/21/2010 11:34:01 PM
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
0
Utf
1/22/2010 12:50:01 PM
Reply:

Similar Artilces:

Help with Error
I got this error recently, and just want to make sure I'm reading it right. Logon Failure on database "First Storage Group\Mailbox Store (EXCHANGE)" - Windows 2000 account PARADIGM\user1; mailbox /o=Paradigm/ou=First Administrative Group/cn=Recipients/cn=user2. Error: -2147221231 Does this mean the user1 tried to access the user2 mailbox and failed? Thanks in advance hello albert, well, this looks familiar to: http://support.microsoft.com/default.aspx?scid=kb;en-us;260782 and http://support.microsoft.com/default.aspx?scid=kb;en-us;309113 regards, Alex "AlbertP&quo...

Compiling automated report from stocklist
Hi all, I'd like to make a big list with items, one item per row, multiple properties and one column that states howmany I ordered. On the next tab I'd like an automated report, displaying each line if and only if I ordered something. I somewhat managed to do that with an advanced filter but that's not exactly what I want. It's not updated when I order some other items. How should I approach this? Data > Filter > AutoFilter, and select "non-blanks" in the column giving your order quantity. Vaya con Dios, Chuck, CABGx3 "webhead" wrote: > Hi...

Windows Mail Error 03-06-10
Randomly but alot lately, I keep getting this error message that the server has an error and it may be due to things like memory and other things. I have checked my memory and it is good. I really like windows mail but I always get random errors. I only use it for AOL mailboxes as that is all I have. Does anyone have any suggestions? Is there a comparable product out there? Next time you get an error message, right-click on your error message, copy, then paste it into a reply here. We can't do much troubleshooting without the complete error message.=20 --=20 Gary VanderMole...

Odd Email Recipt error
Hi. We have a number of users who have external mailboxes, and some who have internal mailboxes. Everyone uses the same domain username@ourcompany.com. When anyone of us send an email to an account externally with a copy to an account internally, the external receipient gets the message, but the sender gets an error which states there is an error delivering to username@companyname.LOCAL, because on receipt to the internal receipient, Exchange is trying to send that email to the external receipient, but locally... Has anyone experienced this, and if so, does anyone know a solution to th...

Checklinks errors
Yesterday we had someone run checklinks over the sales tables. Many errors came up, such as "A record for invoice xxx was removed from the Sales Master Number Setup table; no corresponding record was found in the Sales Transaction History table." After being run a second time, these errors were still there. Is there something we're missing to remove these? Also, we receive the message "The ship-to address record for this customer is missing." It looks correct when we look at it and haven't noticed a problem with it before. Is there a way to correct that ...

Warning 913: Cash Drawer error?
Hi, On one of the cash registers in my client's store is giving an error message everytime you start the POS software. This is a remote cash register connecting to a server running the database (this register has an offline database that sync's back). The error it shows is: "Warning 913: The cash drawer may be locked by another process." Even with no other software running, this error comes up. Anyone have any suggestions as to how to fix this error? Thanks, Matt Matt, Are you using a OPOS driver for the drawer? Try Manager | File | Configuration | Options tab | ...

Re: cannot add public folders, c1030af0 error
No one seems to know why this is happening. I cannot create any new public folders, please help! - Matt A friend of mine had the same problem, so he rang me. It took me 30 minutes to figure out the solution on his server, perhaps it works for you as well: His default web site in IIS (the site where exchange has its virtual directories) was running only with the host header "www.domain.com" (there are more sites running on his machine, since he uses only one IP address, host header names are used). When we added the host header name "servername", it worked. Probabl...

error message when any office 2007 office app closes.
I have just installed 2007 Office Pro Plus on my win xp (svc pack 2) computer. So far the apps have all worked fine except: after I close the program, (word or powerpoint, etc.) I get a error message and do I want to report this to Microsoft. I looked into the "what does this error report contain" and found that the error code is 0x0000005. That's it. My work is saved like normal I don't find anything missing. Anyone have any clues? -- Damzish ----- On 3/10/08 6:38 PM, in article ACE1805D-69BA-4E3D-83CA-3C7D77CC2260@microsoft.com, "Damzish" <Damzis...

run time errors when posting transaction
hey everyone, i restored my database today from a backup and started to recieve some weired run time errors whe posting a transaction. It shows some Microsoft Business Rules error plus when i wanted to add a new item it wouldnt allow me to put more than 15 characters in item description field. if i put more than that it give me an error "Maximum Stored Procedure, function, trigger, or view nesting level exceeded (limit 32)" Please did anybody else had this problem or am i doing something really wrong. one more thing i just changed my OPOS printer to windows printer. t...

error SHSQL.DLL
what's the problem SHSQL.DLL when i compile my program in vc++? ...

Cannot access outlook.pst error
I run Win 98 and Outlook 2002 on my computer. All the sudden all the mail sent for the last 7 days starts showing up again in my INBOX. After that I cannot delete or move anything without the error message "The file c:\windows\local settings\application data\microsoft\outlook\outlook.pst could not be accessed. Access is denied" How can I fix this problem? ...

Error installing outlook sales
Hi i am trying to install sales for outlook and every machine i try it on gets the following message Setup was unable to find an instance of the Microsoft CRM Server Components. The web interface works fine is just the sales for outlook module we are having problems with. I have tried machines with and without xp sp2. We are running office 2003 professional. This is the second install i have done. The test one on our test network worked fine but this one just won't work. Please Help Hi Leon. This happens because the Microsoft CRM Sales for Outlook installation cannot see th...

Join problem
Hello, Usually when I join querys to get a new one, If in the child query the record is missing, i get missing field in the new query. but somtime's, for reason's i don't know, i get in the new query field the value " #Error " , wich i dont know how to handel. Why is it happened and how to avoid this ? thank you -- תודה רבה Can you give us an example of a query that yields #Error? There can be lots of reasons why this could appear. A common example is if you use one of the VBA functions that cannot handle the Null value, such as CDbl() or Val(). -- ...

Check box on unbound form error
I have two check boxes that update a yes/no field in the rst field (using DAO). If left unchecked on save they return a data conversion error 3421. Any help would be greatly appreciated. hi Bob, Bob wrote: > I have two check boxes that update a yes/no field in the rst field (using > DAO). If left unchecked on save they return a data conversion error 3421. > Any help would be greatly appreciated. Checkboxes have three states: True, False, Null. I assume you are leaving it Null. You may set the default value in the property editor or use the Nz() function when updating your...

Drop Down Lists and Error Alerts
I have created a drop down list and want to put in an error alert, so that people cannot enter text into the fields (and can therefore only use the data in the drop down boxes). I have gone to the error alert tab in data validation and ticked the show error alert after invalid data is entered and the selected stop from the drop down box (and entered a short message). However, when I have tested the fields it is still allowing me to enter text and no warning message is coming up. Any ideas on what else I might need to do to make it work. Thanks. What data validation criteria have you...

mail merge error
Trying to do a mail merge and the system seems to hang when she is using the wizard to look for her data (Access or Excel). She can access each file outside the mailmerge fine. uising Office Pro 2000 thanks Antz, This sounds like a question for a Word newsgroup. Did she give it plenty of time? -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Antz" <Antz@discussions.microsoft.com> wrote in message news:443EC1D1-B6EA-45C7-9878-ADF1BAF09AF4@microsoft.com... > Trying to do a mail merge and the system seems to...

MSExchangeTransport Error 929 due to Calendar Connector
I have an exchange 2003 server which regularly shows the following error caused by a calendar connector: Event Type: Error Event Source: MSExchangeTransport Event Category: Routing Engine/Service Event ID: 929 Date: 29/12/2004 Time: 15:31:11 User: N/A Computer: QSI-EXCH2 Description: Failed in reading Connector's DS Info Process Id: 1620 Process location: C:\WINDOWS\system32\inetsrv\inetinfo.exe ConnectorDN: CN=Calendar Connector (QSI-UK-EXCH2),CN=Connections,CN=First Routing Group,CN=Routing Groups,CN=First Administrative Group,CN=Administrative Groups,CN=QSI UK,CN=Microsoft Exc...

invoicing contract
We are getting the following error when invoicing contracts in CRM 3.0. Can anyone help point us in the right direction? We have just finished customising the contract form (added a couple of fields). We don't use all of the locked fields nor do we use contract lines. We hadn't used the contracts before the customisation so I don't know if the customisation is the problem or whether this occurred before. Our site was a 1.2 site which was upgraded to 3.0 the error below occurs after the contract has been created (draft status) and we then then use the "invoice" acti...

Weird System Undeliverable Messages (Outlook 2003/Exchange 2003) and some Exchange Server errors? Advice?
I'm getting this weird undeliverable error message with about 2 or 3 people now (Cant find much on the way on this online though): Your message did not reach some or all of the intended recipients. This message could not be sent. Try sending the message again later, or contact your network administrator. Error is [0x80004005-00000000-00000000]. I had both users turn off "cached" mode.. and so far so good, but this isnt an adequate solution... xxxxxxxxxxxxxxxxxxx On the Server (Exchange 2003 SP2) I am getting a bunch of errors in the event viewer: EventID 623: Sou...

User define message error!
Hi! I created a SDI app and I want to show a dialog after creating SDI, therefore I defined an user define message #define WMU_STARTUP (WM_APP+10) and call it from CSDIView::OnInitUpdate() with PostMessage(WM_STARTUP). In debug version it work correctly but in release version it generates an Application Error after close the program. what's the problem? Thankx ...

Unable to Edit Lookup Tables (Error: 'ExcelBtn' is undefined)
Project Server 2007 SP2 A week or so ago, we were happily adding/modifying Lookup Tables...but today when I click on an existing lookup table to modify (or select the 'New Lookup Table' button) I received a mostly blank screen. Strange thing is, when I'm trying to edit an existing lookup table the resulting screen says "New Lookup Table", some empty frames are barely visible. Clicking on the details of the accompanying error message: Line: 1366 Char: 8 Error: 'ExcelBtn' is undefined Code: 0 I can use the copy lookup table feature successful...

Anyone know when there might be a fix for the "corrupt font error" issues??????
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3233316366_1747827 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit Its such a major pain. I have tried everything on the boards, and they are temp fixes at best. -ww- --B_3233316366_1747827 Content-type: text/html; charset="US-ASCII" Content-transfer-encoding: quoted-printable <HTML> <HEAD> <TITLE>Anyone know when there might be a fix for the &quot;corrupt font err= or&q...

KDC event log error 11
I have a client that has installed MS CRM 1.2 on SBS 2003 PE. It looks like a bad install. It appears they may have even installed it twice because there are 2 x ‘CompanyX CRM version 1.2’ websites in IIS (one installed over the top of companyweb), and their RRW doesn’t work. They didn’t set up a new user to run the CRM service - they installed as Administrator and CRM uses that. They are getting KDC error's in the event log (event ID 11) and have complained about authentication errors. There are several different SPN's listed in the event log as being duplicated. The errors look ...

Outlook97 error message when in Calendar
I posted this under Calendaring but thought it might fit here also. I have WinME. When trying to create appointment in Calendar, I get this message after clicking the Save & Close button. "message interface returned unknown error" It won't let me create an appointment. I had installed me_oe_client.reg because OE wasn't listed in the options of Internet Options. Now it is. That part worked. Now I have this other problem. What can I do? ...

Error in CRMQuery.RetrieveByType Method?
I want to retreive two types of savedqueries. For accounts and for contacts. In my code I use an array to specify the ObjectTypes. ObjectType[] Types = new ObjectType[2]; Types[0] = ObjectType.otContact; Types[1] = ObjectType.otAccount; the call: string XML = oQuery.RetreiveByType(oUserAuth, Types, savedQType, 0, strColumns); This will result in the savedqueries of the Contact object being returned but not from the accounts. Changing the sequence to: Types[0] = ObjectType.otAccount; Types[1] = ObjectType.otContact; Will result in savedqueries of accounts being returned and not of the con...