| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error on VBA Function
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compile error on VBA Function
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" wrote in message ... | 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 | |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compile error on VBA Function
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" wrote in message ... | 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 | |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compile error on VBA Function
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 ToolsReferences for a checked reference that stats with 'MISSING'. Fix the reference. -- HTH, Gary Brown 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compile error on VBA Function
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 ToolsReferences for a checked reference that stats with 'MISSING'. Fix the reference. -- HTH, Gary Brown 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compile error on VBA Function
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" wrote in message ... | 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 ToolsReferences for a checked reference that stats with 'MISSING'. | Fix the reference. | -- | HTH, | Gary Brown | | 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 | |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compile error on VBA Function
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" wrote in message ... | 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 ToolsReferences for a checked reference that stats with 'MISSING'. | Fix the reference. | -- | HTH, | Gary Brown | | 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compile error on VBA Function
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" wrote in message ... 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" wrote in message ... | 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 ToolsReferences for a checked reference that stats with 'MISSING'. | Fix the reference. | -- | HTH, | Gary Brown | | 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compile error on VBA Function
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 ... | 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" wrote in message | ... | 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" wrote in message | ... | | 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 ToolsReferences for a checked reference that stats with | 'MISSING'. | | Fix the reference. | | -- | | HTH, | | Gary Brown | | | | 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 | | | | |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compile error on VBA Function
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 "Jim Rech" wrote: 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" wrote in message ... | 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 ToolsReferences for a checked reference that stats with 'MISSING'. | Fix the reference. | -- | HTH, | Gary Brown | | 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 | |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compile error on VBA Function
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.) Niek Otten wrote: 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" wrote in message ... | 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 ToolsReferences for a checked reference that stats with 'MISSING'. | Fix the reference. | -- | HTH, | Gary Brown | | 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 -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compile error on VBA Function
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 "Jim Rech" wrote: 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" wrote in message ... | 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 ToolsReferences for a checked reference that stats with 'MISSING'. | Fix the reference. | -- | HTH, | Gary Brown | | 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 | |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compile error on VBA Function
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 "MSweetG222" wrote in message ... 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 "Jim Rech" wrote: 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" wrote in message ... | 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 ToolsReferences for a checked reference that stats with 'MISSING'. | Fix the reference. | -- | HTH, | Gary Brown | | 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 | |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| mid function causes a compile error | Excel Programming | |||
| mid function causes a compile error | Excel Programming | |||
| Trying to use INDEX function in VBA Code, compile error | Excel Programming | |||
| Help With - Compile Error: Sub or Function Not Defined | Excel Programming | |||
| compile error using Left() function | Excel Programming | |||
Linear Mode
