problem implementing excel function in vba

Microsoft Windows xp professional editio...
December 16, 2009 at 07:35:13
Specs: Windows XP

I have just been working on vba for the last 2 days only so please pardon my lack of understanding of even the very basic terms

Basically, I have made an excel sheet where I have automated a process of copying a specific range of rows from sheet 1 to different sheets using for loop. everytime the loop finishes, it moves onto the next column to copy the values again. This goes on when I press the start timer button

Ideally what I want to do is when I press the stop timer button, my code should automatically move onto the next column and do a calculation (variance) of all the previous column that was being copied before i pressed the stop button.

Sub Insert_Formula()
Call IncrementCounter
For i = 3 To 53
If IsEmpty(Cells(i, GetCounter())) Then
Worksheets("USD").Cells(i, GetCounter()).Formula = "=VAR(B3:GetCounter-1)"
End If
Next i
End Sub

What I am doing is first incrementing my column and checking whether it is empty.Counter is how I defined my columns as and I am very aware of the fact that the way I have written the code above is not the way to do it. So could you please tell me how to go about pursuing this?

Please tell me if you need more details to help me out. Thanks

See More: problem implementing excel function in vba

Report •

December 17, 2009 at 11:27:47
I'm pretty sure VBA is single threaded, so you cannot do what you want. I might be wrong, however, so I'll move this thread to where the VBA experts hang out.

Report •

December 17, 2009 at 13:05:11
I'm not a big forms user, so I'll just throw this out to see if it helps...

VBA has something called the DoEvents function that yields control to the OS for a brief period of time (one cycle?)

By utilizing DoEvents, you can "interrupt" your code.

For example, the code below will allow you to pause the counter by selecting cells to run another Sub.

With the DoEvents line commented out, you won't be able to select any cells in Sheet1 until the counter is done.

Remove the ' and you can select cells while the counter is running.

Sub AllowPause()
Dim I
 For I = 1 To 20000000    ' Start loop.
    If I Mod 1000 = 0 Then ' If loop has repeated 1000 times.
        Cells(1, 1) = I  'Show value of I
        'DoEvents    ' Yield to operating system.
    End If
 Next I    ' Increment loop counter.
MsgBox "Counter Done"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 MsgBox Target.Address
End Sub

This might be more in line with what you are looking for since it relates to forms...

Report •

Related Solutions

Ask Question