Compile Error - Visual Basic 4 / 5 / 6

Saxman

I need to copy/paste in Excel, but with a delay built in between each
copy/paste operation.

Below is the code which gives me a compile error on the 2nd Sub Delay().
It is a simple copy from sheet 1 to sheets 2,3,4 and 5. Can you see
anything wrong. Help much appreciated.

Thanks

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/12/2005
'
' Keyboard Shortcut: Ctrl+m
'
Range("A1:A5"). Select
Selection.Copy
Sheets("Sheet2" ).Select
ActiveSheet.Pas te
End Sub
Sub Delay()
Dim Beg As Long

Beg = Timer
Do
Loop Until Timer - Beg >= 30
Sheets("Sheet1" ).Select
Application.Cut CopyMode = False
Selection.Copy
Sheets("Sheet3" ).Select
ActiveSheet.Pas te
End Sub
Sub Delay()
Dim Beg As Long

Beg = Timer
Do
Loop Until Timer - Beg >= 30
Sheets("Sheet1" ).Select
Application.Cut CopyMode = False
Selection.Copy
Sheets("Sheet4" ).Select
ActiveSheet.Pas te
End Sub
Sub Delay()
Dim Beg As Long

Beg = Timer
Do
Loop Until Timer - Beg >= 30
Sheets("Sheet1" ).Select
Application.Cut CopyMode = False
Selection.Copy
Sheets("Sheet5" ).Select
ActiveSheet.Pas te
Sheets("Sheet1" ).Select
End Sub

Dec 10 '05 #1

Subscribe Reply

6039

Steve Gerrard

"Saxman" <jo************ *@btinternet.co m> wrote in message
news:px******** *************** ******@40tude.n et...

I need to copy/paste in Excel, but with a delay built in between each
copy/paste operation.

Below is the code which gives me a compile error on the 2nd Sub Delay().
It is a simple copy from sheet 1 to sheets 2,3,4 and 5. Can you see
anything wrong. Help much appreciated.

You can't have three different subs all called Delay. Besides, you don't want
to have to run them all separately.

You want to first make *one* sub called Delay, and put only the delay code in
it:

Sub Delay()
Dim Beg As Long

Beg = Timer
Do
Loop Until Timer - Beg >= 30

End Sub

Then make your main sub, which calls Delay multiple times:

Sub Macro1()

Range("A1:A5"). Select
Selection.Copy
Sheets("Sheet2" ).Select
ActiveSheet.Pas te

Call Delay

Sheets("Sheet1" ).Select
Application.Cut CopyMode = False
Selection.Copy
Sheets("Sheet3" ).Select
ActiveSheet.Pas te

Call Delay

' etc...

End Sub

Dec 10 '05 #2

argusy

Along with Steve's reply, Can you see what else is wrong?

each "Delay" is repeating the cut and paste until the loop condition is satisfied.
How many times is it doing this cut and paste? Could be one... or two.... or
several. You only need to do this once.

From the structure of your code, it looks reminiscent of the old Basic, BasicA,
MBasic etc. style of writing code. Subroutines do not run in order as you've
shown. Each one has to be triggered (by an 'event') or forced by programming.

Basically you have these subs, and you want them running concurrently

Macro1
delay()
delay() with different parameters
delay() again with different parameters
delay() ditto

VB will not allow you to call four subs by the same name. if you want your code
still separated as subroutines, call them by different names
ie
CopyToSheet2()
CopyToSheet3()
CopyToSheet4()
CopyToSheet5()

Then your program becomes
' Macro1 Macro
' Macro recorded 10/12/2005

' Keyboard Shortcut: Ctrl+m

Range("A1:A5"). Select
Selection.Copy
Application.Cut CopyMode = False

Call delay() 'wait a bit
Call CopyToSheet2()
'
Call delay() 'wait a bit
Call CopyToSheet3()

Call delay() 'wait a bit
Call CopyToSheet4()

Call delay() 'wait a bit
Call CopyToSheet5()

End Sub

private sub CopyToSheet2()
' repeat with other subs with sheets matching sub heading

Sheets("Sheet2" ).Select
ActiveSheet.Pas te

end sub

You could also have only one sub and pass it a parameter

' Macro1 Macro
' Macro recorded 10/12/2005

' Keyboard Shortcut: Ctrl+m

Range("A1:A5"). Select
Selection.Copy
Application.Cut CopyMode = False

Call delay() 'wait a bit
Call CopyToSheet(par ameter) '"sheet2"
'
Call delay() 'wait a bit
Call CopyToSheet(par ameter) '"sheet3"

Call delay() 'wait a bit
Call CopyToSheet(par ameter) '"sheet4"

Call delay() 'wait a bit
Call CopyToSheet(par ameter) '"sheet5"

End Sub

Now that's a different kettle of fish.
BUT - the coding is more efficient, and less to type
(and less to debug)
Also, take a look at VB's intrinsic timer
That can be your research in the VB help for the weekend

Argusy

Saxman wrote:

I need to copy/paste in Excel, but with a delay built in between each
copy/paste operation.

Below is the code which gives me a compile error on the 2nd Sub Delay().
It is a simple copy from sheet 1 to sheets 2,3,4 and 5. Can you see
anything wrong. Help much appreciated.

Thanks

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/12/2005
'
' Keyboard Shortcut: Ctrl+m
'
Range("A1:A5"). Select
Selection.Copy
Sheets("Sheet2" ).Select
ActiveSheet.Pas te
End Sub
Sub Delay()
Dim Beg As Long

Beg = Timer
Do
Loop Until Timer - Beg >= 30
Sheets("Sheet1" ).Select
Application.Cut CopyMode = False
Selection.Copy
Sheets("Sheet3" ).Select
ActiveSheet.Pas te
End Sub
Sub Delay()
Dim Beg As Long

Beg = Timer
Do
Loop Until Timer - Beg >= 30
Sheets("Sheet1" ).Select
Application.Cut CopyMode = False
Selection.Copy
Sheets("Sheet4" ).Select
ActiveSheet.Pas te
End Sub
Sub Delay()
Dim Beg As Long

Beg = Timer
Do
Loop Until Timer - Beg >= 30
Sheets("Sheet1" ).Select
Application.Cut CopyMode = False
Selection.Copy
Sheets("Sheet5" ).Select
ActiveSheet.Pas te
Sheets("Sheet1" ).Select
End Sub

Dec 10 '05 #3

argusy

Steve
He might need a "doevents" in that sort of timer as well
Argusy
Steve Gerrard wrote:

"Saxman" <jo************ *@btinternet.co m> wrote in message
news:px******** *************** ******@40tude.n et...
I need to copy/paste in Excel, but with a delay built in between each
copy/paste operation.

Below is the code which gives me a compile error on the 2nd Sub Delay().
It is a simple copy from sheet 1 to sheets 2,3,4 and 5. Can you see
anything wrong. Help much appreciated.

You can't have three different subs all called Delay. Besides, you don't want
to have to run them all separately.

You want to first make *one* sub called Delay, and put only the delay code in
it:

Sub Delay()
Dim Beg As Long

Beg = Timer
Do
Loop Until Timer - Beg >= 30

End Sub

Then make your main sub, which calls Delay multiple times:

Sub Macro1()

Range("A1:A5"). Select
Selection.Copy
Sheets("Sheet2" ).Select
ActiveSheet.Pas te

Call Delay

Sheets("Sheet1" ).Select
Application.Cut CopyMode = False
Selection.Copy
Sheets("Sheet3" ).Select
ActiveSheet.Pas te

Call Delay

' etc...

End Sub

Dec 10 '05 #4

Similar topics

2232

compile error? why?

by: Danny Boelens | last post by:

Hi all, today I ran into a compile error after a compiler upgrade. I made a small example to demonstrate my compile error: template<typename T1, typename T2> class A {}; class B

C / C++

3336

compile time optimization problem

by: Carmine Cairo | last post by:

Hi, I'm working on a project and today I've note a little problem during the compile fase. Here a little piece of code: // 1st version welldone = 0; size = p->getSize(); backbone = new rightType;

C / C++

3822

XmlSchemaSet Compile error ?

by: Brice Prunier | last post by:

Here under 4 schemas i'm working with ( it may be long: sorry...) The context is the following : Resident.xsd imports Person.xsd and includes Common.xsd ( anonimous schema: no TargetNamespace ) Person.xsd includes Common-Naming.xsd ( anonimous schemas ) Common-Naming.xsd includes common.xsd ( both are anonimous schemas ) Compilation of Resident.xsd raise the following exception: "System.Xml.Schema.XmlSchemaException: The attribute 'oid'...

.NET Framework

19730

"Use of possibly unassigned field" compile error

by: Chris LaJoie | last post by:

Our company has been developing a program in C# for some time now, and we haven't had any problems with it, but just last night something cropped up that has me, and everyone else, stumped. I have a struct that contains several different types of data. This struct is used throuout the program. Now, when I compile, I get 6 errors, all of them "Use of possibly unassigned field 'awayTime'" or "Use of possibly unassigned field 'intlTime'"....

C# / C Sharp

2860

Compile error when 2 pages use same codebehind file

by: Thomas Connolly | last post by:

I have 2 pages referencing the same codebehind file in my project. Originally the pages referenced separate code behind files. Once I changed the reference to the same file, everything worked fine while the file was in the project directory. When the obsolete file was removed from the project directory, my application will no longer compile. Can someone please help with this issue? Thank in advance, Tom

ASP.NET

1904

Internal Compile Error

by: Jim Heavey | last post by:

Internal Compiler Error: stage 'BEGIN' Hello, I had an application which was working just fine and I decided to modify all database access within the application to utilizie a new Namespace that I had developed which would handle all database access. After I modified all of the code to point to the new namespace and created all of the necessary references, I get an internal compile error when I attempt to re-compile. I have create a new...

ASP.NET

3519

creating a compile time error... is it possible?

by: ThunderMusic | last post by:

Hi, I'd like to create a compile time error in my class... maybe there's a way already built in in the framework so I can achieve what I want... I have 2 constructors in my class. One of them has mandatory parameters, I mean, they should not be null nor empty (for strings). So I'd make the validation in the constructor and generate a compile-time error if the validation does not match... Is there a way to achieve this or to specify...

.NET Framework

1845

strange compile when using inheritance

by: tony | last post by:

Hello! My question is about calling this method CollectData below but I get a compile error that I shouldn't have because the type parameter is correct. The compile error is the following: C:\PK\Development\Products\UTCAS\4.0\SRC\MeltPracApplication\Dialog\Composit ionForm.cs(942): Argument '1': cannot convert from 'ref MeltPracData.MeltPracDataComposition' to 'ref MeltPracCommon.IDialogPostData'

C# / C Sharp

5045

compile error: new : cannot specify initializer for arrays

by: wong_powah | last post by:

#include <vector> #include <iostream> using std::cout; using std::vector; enum {DATASIZE = 20}; typedef unsigned char data_t;

C / C++

4143

failed to compile boost.system with msvc-7.1

by: BruceWho | last post by:

I downloaded boost1.35.0 and built it with following command: bjam --toolset=msvc-7.1 --variant=release --threading=multi -- link=shared --with-system stage and it failed to compile, error message is: E:\software\development\boost_1_35_0\boost_1_35_0>bjam -- toolset=msvc-7.1 --variant=release --threading=multi --link=shared --

C / C++

9650

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9497

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10363

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10110

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

9962

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

5398

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

4067

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3670

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2894

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General