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

"MSweetG222" <(E-Mail Removed)> wrote in message news8486600-B6D5-4B8A-B2D4-(E-Mail Removed)...
| 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?
--
Thx
MSweetG222



"Niek Otten" wrote:

> 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
>
> "MSweetG222" <(E-Mail Removed)> wrote in message news8486600-B6D5-4B8A-B2D4-(E-Mail Removed)...
> | 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
> |
>
>
>

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 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
>

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 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
> >

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 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

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
"MSweetG222" <(E-Mail Removed)> wrote in message
news:A9D61A33-41A7-40E2-B78A-(E-Mail Removed)...
| 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 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


"Dave Peterson" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
| 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 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" <(E-Mail Removed)> wrote in message
news:uxXggfs$(E-Mail Removed)...
> 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
>
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> | 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 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
>
>

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
"Peter T" <peter_t@discussions> wrote in message news:e3brHqs$(E-Mail Removed)...
|> 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" <(E-Mail Removed)> wrote in message
| news:uxXggfs$(E-Mail Removed)...
| > 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
| >
| >
| > "Dave Peterson" <(E-Mail Removed)> wrote in message
| news:(E-Mail Removed)...
| > | 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 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
| >
| >
|
|