Compile error on VBA Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

All - I have written a .xla program. Program works great on my machine.
However, when installed another computer, I receive a compile error on the
VBA functions. See below for an example...in example subroutine, the other
computer has a compile error on the LEFT function. On my computer, no issues.

Additional info: Tools | Options | Require Variable Declaration is not
checked on either computer. All variables are defined and option explicit is
set on every module in the .xla.

--------------------------------------------------
Example
--------------------------------------------------
Option Explicit

Sub Test

Dim AnyString as string
Dim MyStr as string
AnyString = "Hello World"
MyStr = Left(AnyString, 7) '<<< Compile error on LEFT function

End Sub

--------------------------------------------------

What do I need to do to the .xla or to the computer in question to get past
this compile issue?

Thank you for any assistance.

MSweetG222
 
I think for older versions of Excel you need to use Left$. That will work in newer ones as well.
I don't know what subtleties are behind this change.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| All - I have written a .xla program. Program works great on my machine.
| However, when installed another computer, I receive a compile error on the
| VBA functions. See below for an example...in example subroutine, the other
| computer has a compile error on the LEFT function. On my computer, no issues.
|
| Additional info: Tools | Options | Require Variable Declaration is not
| checked on either computer. All variables are defined and option explicit is
| set on every module in the .xla.
|
| --------------------------------------------------
| Example
| --------------------------------------------------
| Option Explicit
|
| Sub Test
|
| Dim AnyString as string
| Dim MyStr as string
| AnyString = "Hello World"
| MyStr = Left(AnyString, 7) '<<< Compile error on LEFT function
|
| End Sub
|
| --------------------------------------------------
|
| What do I need to do to the .xla or to the computer in question to get past
| this compile issue?
|
| Thank you for any assistance.
|
| MSweetG222
|
 
Thanks for the reply. Both computers are using 2003. Do you think it still
makes a difference?
 
On the other machine, get into the VBE.
Check Tools>References for a checked reference that stats with 'MISSING'.
Fix the reference.
--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.
 
This is a common problem. I'd look once more for that missing reference. (It
can't hurt!)
 
Just to be clear, please check this when the problem add-in is the active
project in the VBE. Click on it in the Project window in the VBE. This is
a classic 'missing reference' problem.

--
Jim
| No missing DLL references on either machine.
| Thanks for the suggestion.
| --
| Thx
| MSweetG222
|
|
|
| "Gary Brown" wrote:
|
| > On the other machine, get into the VBE.
| > Check Tools>References for a checked reference that stats with
'MISSING'.
| > Fix the reference.
| > --
| > HTH,
| > Gary Brown
| > (e-mail address removed)
| > If this post was helpful to you, please select ''YES'' at the bottom of
the
| > post.
| >
| >
| >
| > "MSweetG222" wrote:
| >
| > > All - I have written a .xla program. Program works great on my
machine.
| > > However, when installed another computer, I receive a compile error on
the
| > > VBA functions. See below for an example...in example subroutine, the
other
| > > computer has a compile error on the LEFT function. On my computer, no
issues.
| > >
| > > Additional info: Tools | Options | Require Variable Declaration is not
| > > checked on either computer. All variables are defined and option
explicit is
| > > set on every module in the .xla.
| > >
| > > --------------------------------------------------
| > > Example
| > > --------------------------------------------------
| > > Option Explicit
| > >
| > > Sub Test
| > >
| > > Dim AnyString as string
| > > Dim MyStr as string
| > > AnyString = "Hello World"
| > > MyStr = Left(AnyString, 7) '<<< Compile error on LEFT function
| > >
| > > End Sub
| > >
| > > --------------------------------------------------
| > >
| > > What do I need to do to the .xla or to the computer in question to get
past
| > > this compile issue?
| > >
| > > Thank you for any assistance.
| > >
| > > MSweetG222
| > >
 
Hi Dave,

Is this why I sometimes can't use the Format function? I've been struggling with that one for years! Of course I found
workarounds, but they use worksheet functions which is always less efficient.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| This is a common problem. I'd look once more for that missing reference. (It
| can't hurt!)
|
| MSweetG222 wrote:
| >
| > No missing DLL references on either machine.
| > Thanks for the suggestion.
| > --
| > Thx
| > MSweetG222
| >
| > "Gary Brown" wrote:
| >
| > > On the other machine, get into the VBE.
| > > Check Tools>References for a checked reference that stats with 'MISSING'.
| > > Fix the reference.
| > > --
| > > HTH,
| > > Gary Brown
| > > (e-mail address removed)
| > > If this post was helpful to you, please select ''YES'' at the bottom of the
| > > post.
| > >
| > >
| > >
| > > "MSweetG222" wrote:
| > >
| > > > All - I have written a .xla program. Program works great on my machine.
| > > > However, when installed another computer, I receive a compile error on the
| > > > VBA functions. See below for an example...in example subroutine, the other
| > > > computer has a compile error on the LEFT function. On my computer, no issues.
| > > >
| > > > Additional info: Tools | Options | Require Variable Declaration is not
| > > > checked on either computer. All variables are defined and option explicit is
| > > > set on every module in the .xla.
| > > >
| > > > --------------------------------------------------
| > > > Example
| > > > --------------------------------------------------
| > > > Option Explicit
| > > >
| > > > Sub Test
| > > >
| > > > Dim AnyString as string
| > > > Dim MyStr as string
| > > > AnyString = "Hello World"
| > > > MyStr = Left(AnyString, 7) '<<< Compile error on LEFT function
| > > >
| > > > End Sub
| > > >
| > > > --------------------------------------------------
| > > >
| > > > What do I need to do to the .xla or to the computer in question to get past
| > > > this compile issue?
| > > >
| > > > Thank you for any assistance.
| > > >
| > > > MSweetG222
| > > >
|
| --
|
| Dave Peterson
 
Is this why I sometimes can't use the Format function?

Really ?

does this work for you -

MsgBox VBA.Strings.Format(Date, "ddd dd mmm yy")

Regards,
Peter T

Niek Otten said:
Hi Dave,

Is this why I sometimes can't use the Format function? I've been
Click to expand...
struggling with that one for years! Of course I found
 
I can't check right now, because the version I use now does recognize Format.
I'll keep your suggestion for when I encounter this again.

Thanks,

Niek
|> Is this why I sometimes can't use the Format function?
|
| Really ?
|
| does this work for you -
|
| MsgBox VBA.Strings.Format(Date, "ddd dd mmm yy")
|
| Regards,
| Peter T
|
| | > Hi Dave,
| >
| > Is this why I sometimes can't use the Format function? I've been
| struggling with that one for years! Of course I found
| > workarounds, but they use worksheet functions which is always less
| efficient.
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| >
| | > | This is a common problem. I'd look once more for that missing
| reference. (It
| > | can't hurt!)
| > |
| > | MSweetG222 wrote:
| > | >
| > | > No missing DLL references on either machine.
| > | > Thanks for the suggestion.
| > | > --
| > | > Thx
| > | > MSweetG222
| > | >
| > | > "Gary Brown" wrote:
| > | >
| > | > > On the other machine, get into the VBE.
| > | > > Check Tools>References for a checked reference that stats with
| 'MISSING'.
| > | > > Fix the reference.
| > | > > --
| > | > > HTH,
| > | > > Gary Brown
| > | > > (e-mail address removed)
| > | > > If this post was helpful to you, please select ''YES'' at the bottom
| of the
| > | > > post.
| > | > >
| > | > >
| > | > >
| > | > > "MSweetG222" wrote:
| > | > >
| > | > > > All - I have written a .xla program. Program works great on my
| machine.
| > | > > > However, when installed another computer, I receive a compile
| error on the
| > | > > > VBA functions. See below for an example...in example subroutine,
| the other
| > | > > > computer has a compile error on the LEFT function. On my
| computer, no issues.
| > | > > >
| > | > > > Additional info: Tools | Options | Require Variable Declaration is
| not
| > | > > > checked on either computer. All variables are defined and option
| explicit is
| > | > > > set on every module in the .xla.
| > | > > >
| > | > > > --------------------------------------------------
| > | > > > Example
| > | > > > --------------------------------------------------
| > | > > > Option Explicit
| > | > > >
| > | > > > Sub Test
| > | > > >
| > | > > > Dim AnyString as string
| > | > > > Dim MyStr as string
| > | > > > AnyString = "Hello World"
| > | > > > MyStr = Left(AnyString, 7) '<<< Compile error on LEFT function
| > | > > >
| > | > > > End Sub
| > | > > >
| > | > > > --------------------------------------------------
| > | > > >
| > | > > > What do I need to do to the .xla or to the computer in question to
| get past
| > | > > > this compile issue?
| > | > > >
| > | > > > Thank you for any assistance.
| > | > > >
| > | > > > MSweetG222
| > | > > >
| > |
| > | --
| > |
| > | Dave Peterson
| >
| >
|
|
 
Okay all...She finally got back to me. I made sure she was focused on the
correct VBA project (per Jim's suggestion) and there WAS a missing DLL. :(

Thank you all for your help. :)

Thx
MSweetG222
 
If you mean that you get a complile error, then I'd guess so.

If you mean that not all the number formats match what you can use with =text(),
then no.

(I'm guessing the first.)
 
Can a VBA program be written that tests for missing dlls?
That way I can attach to an Auto_Open and would not have to rely on user
looking into the VBE and reading it to me over the phone.

Thx
MSweetG222
 
AFAIK, not in straight forward manner, because as you have found, that
causes code not to run.
If you specify the library of all functions
e.g. VBA.Strings.Left(...)
you don't get the errors that you see, but obviously any calls to missing
libraries will error when they are compiled.

Use of late-binding and CreateObject would be able to tell you if some
instance could be created and deal with the fact if not possible.

NickHK
 
Back
Top