Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does VBA support multi-thread?
I have a workbook with too many rows and columns of data. My macro run very
slowly. Can I use multithread in VBA? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does VBA support multi-thread?
Unless you want to get into some seriously complicated programming (and a
lot of trouble) you can't. RBS "palo" wrote in message ... I have a workbook with too many rows and columns of data. My macro run very slowly. Can I use multithread in VBA? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does VBA support multi-thread?
palo;127305 Wrote: I have a workbook with too many rows and columns of data. My macro run very slowly. Can I use multithread in VBA? Thanks How many Columns & rows are you using. One thing that many users do that slows code down is to continually Select and/or activate ranges. This is not usually necessary. Also, check if your code contains Loops that could possibly be unnecessary -- royUK Hope that helps, RoyUK For tips & examples visit 'my web site' (http://www.excel-it.com/) ------------------------------------------------------------------------ royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=35293 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does VBA support multi-thread?
There are ways of speeding up the code. Post code if possible
Some tricks are to turn off screen up[dating and turn off events Application.ScreenUpdating = False Application.EnableEvents = False If yu are deleting rows delete all the rows in one instruction rather than one line at a time. I usually mark the rows I want to delete and then use the mark to sort the rows I want to delete so the are all adjacent. There are other tricks but without seeing the code I can't determine what is slowing down your code. "palo" wrote: I have a workbook with too many rows and columns of data. My macro run very slowly. Can I use multithread in VBA? Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does VBA support multi-thread?
If VB6 support multithread, Can I to associate VB6 and VBA?
"RB Smissaert" wrote: Unless you want to get into some seriously complicated programming (and a lot of trouble) you can't. RBS "palo" wrote in message ... I have a workbook with too many rows and columns of data. My macro run very slowly. Can I use multithread in VBA? Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does VBA support multi-thread?
Thank you very much. I want to get the information of multithread in VBA. Can
I do this with VB6? "Joel" wrote: There are ways of speeding up the code. Post code if possible Some tricks are to turn off screen up[dating and turn off events Application.ScreenUpdating = False Application.EnableEvents = False If yu are deleting rows delete all the rows in one instruction rather than one line at a time. I usually mark the rows I want to delete and then use the mark to sort the rows I want to delete so the are all adjacent. There are other tricks but without seeing the code I can't determine what is slowing down your code. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does VBA support multi-thread?
I think you're on the wrong track in trying to get multithreading to work.
The reason for poor performance is in the code, not in the number of processors used. Post your code. I'm sure you'll get good advice on how to improve performance. -- Kind regards, Niek Otten Microsoft MVP - Excel "palo" wrote in message ... Thank you very much. I want to get the information of multithread in VBA. Can I do this with VB6? "Joel" wrote: There are ways of speeding up the code. Post code if possible Some tricks are to turn off screen up[dating and turn off events Application.ScreenUpdating = False Application.EnableEvents = False If yu are deleting rows delete all the rows in one instruction rather than one line at a time. I usually mark the rows I want to delete and then use the mark to sort the rows I want to delete so the are all adjacent. There are other tricks but without seeing the code I can't determine what is slowing down your code. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does VBA support multi-thread?
I've never tried any of this myself, so I don't know how easy or hard it
might be, nor whether the principles can be extended to VBA or not, but here is a link about implementing multithreading in VB5/6 from one of the most credible authors of API techniques; perhaps you can make use of the material he presents in it, but make sure to read the "Sequel #1- Regarding VB6" section at the end of the article (actually, I think you should read it first, before you get started).... http://www.freevbcode.com/ShowCode.Asp?ID=1287 -- Rick (MVP - Excel) "palo" wrote in message ... Thank you very much. I want to get the information of multithread in VBA. Can I do this with VB6? "Joel" wrote: There are ways of speeding up the code. Post code if possible Some tricks are to turn off screen up[dating and turn off events Application.ScreenUpdating = False Application.EnableEvents = False If yu are deleting rows delete all the rows in one instruction rather than one line at a time. I usually mark the rows I want to delete and then use the mark to sort the rows I want to delete so the are all adjacent. There are other tricks but without seeing the code I can't determine what is slowing down your code. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does VBA support multi-thread?
This is my code. The code run with 3000 file word in Foldername-folder:
Option Explicit Sub GetDoc_Properties() Dim FolderName As String, wbName As String Dim rw As Integer Dim lrow As Long, lrow2 As Long Dim ObjWord As Object Dim DoSubj As String, DoTit As String, DoAut As String Dim sDate On Error Resume Next lrow = ActiveSheet.Range("B65000").End(xlUp).Row ActiveSheet.Range("A9:G" & lrow + 9).ClearContents FolderName = Cells(4, 5) wbName = Dir(FolderName & "\" & "*.doc") Application.ScreenUpdating = False rw = 9 Set ObjWord = CreateObject("Word.Application") While wbName < "" With ObjWord .Visible = True .Documents.Open (FolderName & "\" & wbName) DoSubj = .ActiveDocument.BuiltinDocumentProperties(2) DoTit = .ActiveDocument.BuiltinDocumentProperties(1) DoAut = .ActiveDocument.BuiltinDocumentProperties(3) .Documents(wbName).Close End With Cells(rw, 1) = rw - 8 Cells(rw, 2).Value = DateSerial(2008, Mid(wbName, 4, 2), Mid(wbName, 1, 2)) Cells(rw, 3).Value = Mid(wbName, 7, Len(wbName) - 10) Cells(rw, 4).Value = DoSubj sDate = Split(DoTit, " ") Cells(rw, 6).Value = DateSerial(sDate(2), sDate(1), sDate(0)) Cells(rw, 5).Value = DoAut Cells(rw, 7).Value = Cells(rw, 6).Value - Cells(rw, 2).Value rw = rw + 1 wbName = Dir Wend Cells(5, 5) = rw - 9 ObjWord.Quit lrow2 = Range("B65000").End(xlUp).Row Range("B9:H" & lrow2).Select Selection.Sort Key1:=Range("B9"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Cells(1, 1).Select If rw = 9 Then MsgBox "Duong dan hoac tap tin khong ton tai ", , "Thong bao" End If Application.ScreenUpdating = True End Sub "Joel" wrote: There are ways of speeding up the code. Post code if possible Some tricks are to turn off screen up[dating and turn off events Application.ScreenUpdating = False Application.EnableEvents = False If yu are deleting rows delete all the rows in one instruction rather than one line at a time. I usually mark the rows I want to delete and then use the mark to sort the rows I want to delete so the are all adjacent. There are other tricks but without seeing the code I can't determine what is slowing down your code. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does VBA support multi-thread?
The Word application does not need to be visible while running the code.
Also, the "SaveChanges" option should be used for both the Close and Quit statements... SaveChanges:=wdDoNotSaveChanges However, your best bet may be to read the document properties without opening the Word files using the Dsofile.dll from Microsoft. See Chip Pearson's "Returning Property Values From a Closed File" here... http://www.cpearson.com/excel/DocProp.aspx and also... http://support.microsoft.com/kb/224351/en-us -- Jim Cone Portland, Oregon USA "palo" wrote in message This is my code. The code run with 3000 file word in Foldername-folder: -snip- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does VBA support multi-thread?
Actually that should be... SaveChanges:=0
-- Jim Cone Portland, Oregon USA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multi thread in Excel | Excel Worksheet Functions | |||
Does not support automation or does not support expected interface | Excel Programming | |||
Excel is multi thread program? | Excel Discussion (Misc queries) | |||
Does excel support multi-value picklists? | Excel Worksheet Functions | |||
How to thread | Excel Programming |