Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
String manipulation in macro runs slower each time ran.
The string manipulation is taking a long string and pasting segments of 255
characters into a cell until all the characters are in cells. After running this about 3 times Excel slows to a halt. How do I fix this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
String manipulation in macro runs slower each time ran.
I'm new to this. Here is the code I'm having problems with. Task manager
reports no memory problems. Do Until Left$(ActiveCell.Value, 1) = Chr$(12) lins = lins + 1 If lins 10000 Then Exit Do End If If Len(ActiveCell) 255 Then Fcel = ActiveCell ActiveCell.Offset(1, 0).Activate Selection.Insert Shift:=xlDown ActiveCell = Right(Fcel, Len(Fcel) - 255) ActiveCell.Offset(-1, 0).Activate ActiveCell = Left(Fcel, 255) 'ActiveCell.Offset(1, 0).Activate Else Fcel = "" End If ActiveCell.Offset(1, 0).Select Loop "Lamination Technology" wrote: The string manipulation is taking a long string and pasting segments of 255 characters into a cell until all the characters are in cells. After running this about 3 times Excel slows to a halt. How do I fix this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
String manipulation in macro runs slower each time ran.
Here is the code I'm having trouble with. Task manager reports no memory
issues. Thanks. Do Until Left$(ActiveCell.Value, 1) = Chr$(12) lins = lins + 1 If lins 10000 Then Exit Do End If If Len(ActiveCell) 255 Then Fcel = ActiveCell ActiveCell.Offset(1, 0).Activate Selection.Insert Shift:=xlDown ActiveCell = Right(Fcel, Len(Fcel) - 255) ActiveCell.Offset(-1, 0).Activate ActiveCell = Left(Fcel, 255) 'ActiveCell.Offset(1, 0).Activate Else Fcel = "" End If ActiveCell.Offset(1, 0).Select Loop "Lamination Technology" wrote: The string manipulation is taking a long string and pasting segments of 255 characters into a cell until all the characters are in cells. After running this about 3 times Excel slows to a halt. How do I fix this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
String manipulation in macro runs slower each time ran.
Without knowing the context in which you're running this, I have to
guess that you may be moving growing amounts of text each time you run this code. Do you run it from the top of a column every time, thereby moving all your previous text down with each pass through the loop? BTW, in testing this in Excel 97 I had to change the first line to get the loop to end properly: Do Until (ActiveCell.Value) = Empty |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
String manipulation in macro runs slower each time ran.
Mark,
Thanks for the response. I'll try to give you all the information without going overboard. Also I think that I'm about to track down the problem. I am taking a string of data withover 1200 characters. Take the string, copy and paste it into the next cell, back up to the previous cell, & trim the excess of 255 characters on the right. I wind up with the data string in a column of cells with 255 characters per cell. The reason for this, just in case someone else runs into this problem, I am copying each character into a cell on the next spread sheet for analysis.( Max 256 cells in a row) This is captured code from a PLC com port. Back to my problem----I think the problem occurs when the paste is being done and cells are shifted down. I'm running Excel 2003. If I shut down the spreadsheet, the open it again the problem goes away. I can run the macros about 3 times before excel crawls to a halt. Aggrivating! Would you like to look at the spreadsheet? "Mark Lincoln" wrote: Without knowing the context in which you're running this, I have to guess that you may be moving growing amounts of text each time you run this code. Do you run it from the top of a column every time, thereby moving all your previous text down with each pass through the loop? BTW, in testing this in Excel 97 I had to change the first line to get the loop to end properly: Do Until (ActiveCell.Value) = Empty |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
String manipulation in macro runs slower each time ran.
I don't want to open unknown files except as a last resort. Let's see
if we can solve this first. We're thinking the same way in that we believe the continual shifting down of cells is causing the problem. Do you *have* to shift cells? Can you just move excess text to the next cell down until you've finished? I worked up an example that works this way. But I only tested it with short strings and chopped them up into 10-character lengths. If it will work for you without hosing the rest of your workbook, modify it as needed to fit your situation and see if it keeps Excel from getting cranky. Option Explicit Sub ChopText() 'Assumes the user has activated 'the cell containing the text 'to be manipulated Dim R As Integer Dim C As Integer Dim RemainingLength As Integer Dim ExcessText As String R = ActiveCell.Row C = ActiveCell.Column Do If R 10000 Then Exit Do If Len(Cells(R, C).Value) 255 Then ExcessText = Right(Cells(R, C).Value, Len(Cells(R, C)) - 255) Cells(R, C).Value = Left(Cells(R, C).Value, 255) R = R + 1 Cells(R, C).Value = ExcessText RemainingLength = Len(ExcessText) End If Loop Until RemainingLength <= 255 End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
String manipulation in macro runs slower each time ran.
I'd recommend fetching the content of the cell, manipulating the
string, and then saving to the cell value. Each time you access a cell internally Excel has to access a data structure, find the string, and then modify it. Left, Right, Len those operations apply to Strings just as they do to Cells. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA macro runs fine, but freezes if I try to do ANYTHING else whileit runs | Setting up and Configuration of Excel | |||
String Manipulation within VBA | Excel Discussion (Misc queries) | |||
Importing Long String - String Manipulation (INVRPT) (EDI EANCOM 96a) | Excel Programming | |||
Importing Long String - String Manipulation (EDI EANCOM 96a) | Excel Programming | |||
string manipulation | Excel Programming |