Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed of Applications in VBA vs VB
,I have written an application in VBA that reads a very large binary file.
I am pleased that it works at all .. but it runs too slow to be of any practical use. I read 4 bytes at a time, then convert. The file is a mixture of integer, ascii and floating point data. And very large. I want to read several of these files in succession. Would the stream reader speed things up? Or, if I transformed it into VB, would it go faster? Is there an inheriant speed problem with VBA because it is wrapped up in EXCEL? I only use VBA because I am most familiar with that user interface. Any comments are greatly appreciated. Mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed of Applications in VBA vs VB
If I recall correctly the engine for VB is actually derived from VBA (which
seems backwards until you think about it). VB just wraps a bunch of functionality around the VBA engine that is not available in VBA (creating dll's and such). So converting to VB probably will not help. As for the stream reader speeding things up... Give it a try and let me know. Beyond that post your code and let some of the greater minds here have a crack at it. Perhaps there are some code changes that will optimize your speed. -- HTH... Jim Thomlinson "Mark HOlcomb" wrote: ,I have written an application in VBA that reads a very large binary file. I am pleased that it works at all .. but it runs too slow to be of any practical use. I read 4 bytes at a time, then convert. The file is a mixture of integer, ascii and floating point data. And very large. I want to read several of these files in succession. Would the stream reader speed things up? Or, if I transformed it into VB, would it go faster? Is there an inheriant speed problem with VBA because it is wrapped up in EXCEL? I only use VBA because I am most familiar with that user interface. Any comments are greatly appreciated. Mark |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed of Applications in VBA vs VB
Thanks .. I have tried to make the code concise but it might be too big to
post. There is a commerical code that reads the same binary file, but does it very fast. I'm missing somthing. I will try the streamreader. perhaps getting the data into memory, as opposed to hitting the disk every 4 bytes will make a difference. I found that the built in math functions dec2hex and hex2dec slowed the code down a lot. created my own functions to perform this task and made a huge performance improvement. I will let you know about the streamreader .. "Jim Thomlinson" wrote: If I recall correctly the engine for VB is actually derived from VBA (which seems backwards until you think about it). VB just wraps a bunch of functionality around the VBA engine that is not available in VBA (creating dll's and such). So converting to VB probably will not help. As for the stream reader speeding things up... Give it a try and let me know. Beyond that post your code and let some of the greater minds here have a crack at it. Perhaps there are some code changes that will optimize your speed. -- HTH... Jim Thomlinson "Mark HOlcomb" wrote: ,I have written an application in VBA that reads a very large binary file. I am pleased that it works at all .. but it runs too slow to be of any practical use. I read 4 bytes at a time, then convert. The file is a mixture of integer, ascii and floating point data. And very large. I want to read several of these files in succession. Would the stream reader speed things up? Or, if I transformed it into VB, would it go faster? Is there an inheriant speed problem with VBA because it is wrapped up in EXCEL? I only use VBA because I am most familiar with that user interface. Any comments are greatly appreciated. Mark |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed of Applications in VBA vs VB
Hi Mark,
When I read your post it simply struck me that I had actually seen a performance comparison between different Excel technologies this afternoon. As all such investigations, the stats you end up with obviously depend heavily on exactly what type of processing you're doing, how you're reading your data, the specific characteristics of teh data you're operating on, what system configuration you've got, etc, etc. Still, the comparison may be of some use, as a little rough estimate of what you should be able to expect: http://www.codematic.net/excel-user-...-functions.htm However, I'm not entirely sure what you're trying to achieve; you're saying you're reading a file and processing it, but at the same time I get the impression that you're not using Excel at all, apart from "hosting" your VBA project, are you? Or has that part of the discussion simply been left out of the discussion? If you are not using Excel, then I would strongly suggest moving to e.g. the new .NET technologies. The entire Visual Studio enviroment can actually be downloaded for free, in a light version (e.g., search for Visual Studio Express Edition on Google) Cheers, /MP "Mark HOlcomb" wrote: Thanks .. I have tried to make the code concise but it might be too big to post. There is a commerical code that reads the same binary file, but does it very fast. I'm missing somthing. I will try the streamreader. perhaps getting the data into memory, as opposed to hitting the disk every 4 bytes will make a difference. I found that the built in math functions dec2hex and hex2dec slowed the code down a lot. created my own functions to perform this task and made a huge performance improvement. I will let you know about the streamreader .. "Jim Thomlinson" wrote: If I recall correctly the engine for VB is actually derived from VBA (which seems backwards until you think about it). VB just wraps a bunch of functionality around the VBA engine that is not available in VBA (creating dll's and such). So converting to VB probably will not help. As for the stream reader speeding things up... Give it a try and let me know. Beyond that post your code and let some of the greater minds here have a crack at it. Perhaps there are some code changes that will optimize your speed. -- HTH... Jim Thomlinson "Mark HOlcomb" wrote: ,I have written an application in VBA that reads a very large binary file. I am pleased that it works at all .. but it runs too slow to be of any practical use. I read 4 bytes at a time, then convert. The file is a mixture of integer, ascii and floating point data. And very large. I want to read several of these files in succession. Would the stream reader speed things up? Or, if I transformed it into VB, would it go faster? Is there an inheriant speed problem with VBA because it is wrapped up in EXCEL? I only use VBA because I am most familiar with that user interface. Any comments are greatly appreciated. Mark |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed of Applications in VBA vs VB
I look forward to finding out your results. The commercial code is probably
written in C/C++ which is quite a bit faster than VB. Without seeing your code one of the biggest cuplrets that slow things down is the calculation settings. If you are looping and the spreadsheet needs to recalc with each loop it is going to be slow. Set your application.calculation to xlManual and then back to xlAutomatic at the end. Deletes and inserts can be slow. Print settings are slow. Here is a good link to a site that help with tweaking perfomance... http://www.decisionmodels.com/index.htm -- HTH... Jim Thomlinson "Mark HOlcomb" wrote: Thanks .. I have tried to make the code concise but it might be too big to post. There is a commerical code that reads the same binary file, but does it very fast. I'm missing somthing. I will try the streamreader. perhaps getting the data into memory, as opposed to hitting the disk every 4 bytes will make a difference. I found that the built in math functions dec2hex and hex2dec slowed the code down a lot. created my own functions to perform this task and made a huge performance improvement. I will let you know about the streamreader .. "Jim Thomlinson" wrote: If I recall correctly the engine for VB is actually derived from VBA (which seems backwards until you think about it). VB just wraps a bunch of functionality around the VBA engine that is not available in VBA (creating dll's and such). So converting to VB probably will not help. As for the stream reader speeding things up... Give it a try and let me know. Beyond that post your code and let some of the greater minds here have a crack at it. Perhaps there are some code changes that will optimize your speed. -- HTH... Jim Thomlinson "Mark HOlcomb" wrote: ,I have written an application in VBA that reads a very large binary file. I am pleased that it works at all .. but it runs too slow to be of any practical use. I read 4 bytes at a time, then convert. The file is a mixture of integer, ascii and floating point data. And very large. I want to read several of these files in succession. Would the stream reader speed things up? Or, if I transformed it into VB, would it go faster? Is there an inheriant speed problem with VBA because it is wrapped up in EXCEL? I only use VBA because I am most familiar with that user interface. Any comments are greatly appreciated. Mark |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed of Applications in VBA vs VB
Thanks .. yet another language to learn (I dont know C#)! Great comparison!
Excel is a good container for the data mining. I haven't really used the spreadsheet piece yet. I will be reading 200 of these files and making comparision between them. "Mat P:son" wrote: Hi Mark, When I read your post it simply struck me that I had actually seen a performance comparison between different Excel technologies this afternoon. As all such investigations, the stats you end up with obviously depend heavily on exactly what type of processing you're doing, how you're reading your data, the specific characteristics of teh data you're operating on, what system configuration you've got, etc, etc. Still, the comparison may be of some use, as a little rough estimate of what you should be able to expect: http://www.codematic.net/excel-user-...-functions.htm However, I'm not entirely sure what you're trying to achieve; you're saying you're reading a file and processing it, but at the same time I get the impression that you're not using Excel at all, apart from "hosting" your VBA project, are you? Or has that part of the discussion simply been left out of the discussion? If you are not using Excel, then I would strongly suggest moving to e.g. the new .NET technologies. The entire Visual Studio enviroment can actually be downloaded for free, in a light version (e.g., search for Visual Studio Express Edition on Google) Cheers, /MP "Mark HOlcomb" wrote: Thanks .. I have tried to make the code concise but it might be too big to post. There is a commerical code that reads the same binary file, but does it very fast. I'm missing somthing. I will try the streamreader. perhaps getting the data into memory, as opposed to hitting the disk every 4 bytes will make a difference. I found that the built in math functions dec2hex and hex2dec slowed the code down a lot. created my own functions to perform this task and made a huge performance improvement. I will let you know about the streamreader .. "Jim Thomlinson" wrote: If I recall correctly the engine for VB is actually derived from VBA (which seems backwards until you think about it). VB just wraps a bunch of functionality around the VBA engine that is not available in VBA (creating dll's and such). So converting to VB probably will not help. As for the stream reader speeding things up... Give it a try and let me know. Beyond that post your code and let some of the greater minds here have a crack at it. Perhaps there are some code changes that will optimize your speed. -- HTH... Jim Thomlinson "Mark HOlcomb" wrote: ,I have written an application in VBA that reads a very large binary file. I am pleased that it works at all .. but it runs too slow to be of any practical use. I read 4 bytes at a time, then convert. The file is a mixture of integer, ascii and floating point data. And very large. I want to read several of these files in succession. Would the stream reader speed things up? Or, if I transformed it into VB, would it go faster? Is there an inheriant speed problem with VBA because it is wrapped up in EXCEL? I only use VBA because I am most familiar with that user interface. Any comments are greatly appreciated. Mark |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed of Applications in VBA vs VB
I have turned all of the screen updataing an auto recaluating toggles off.
I'm not really using the spreadsheet yet and it doent make much difference at this point. I appreciate the feedback. Thanks Mark "Jim Thomlinson" wrote: I look forward to finding out your results. The commercial code is probably written in C/C++ which is quite a bit faster than VB. Without seeing your code one of the biggest cuplrets that slow things down is the calculation settings. If you are looping and the spreadsheet needs to recalc with each loop it is going to be slow. Set your application.calculation to xlManual and then back to xlAutomatic at the end. Deletes and inserts can be slow. Print settings are slow. Here is a good link to a site that help with tweaking perfomance... http://www.decisionmodels.com/index.htm -- HTH... Jim Thomlinson "Mark HOlcomb" wrote: Thanks .. I have tried to make the code concise but it might be too big to post. There is a commerical code that reads the same binary file, but does it very fast. I'm missing somthing. I will try the streamreader. perhaps getting the data into memory, as opposed to hitting the disk every 4 bytes will make a difference. I found that the built in math functions dec2hex and hex2dec slowed the code down a lot. created my own functions to perform this task and made a huge performance improvement. I will let you know about the streamreader .. "Jim Thomlinson" wrote: If I recall correctly the engine for VB is actually derived from VBA (which seems backwards until you think about it). VB just wraps a bunch of functionality around the VBA engine that is not available in VBA (creating dll's and such). So converting to VB probably will not help. As for the stream reader speeding things up... Give it a try and let me know. Beyond that post your code and let some of the greater minds here have a crack at it. Perhaps there are some code changes that will optimize your speed. -- HTH... Jim Thomlinson "Mark HOlcomb" wrote: ,I have written an application in VBA that reads a very large binary file. I am pleased that it works at all .. but it runs too slow to be of any practical use. I read 4 bytes at a time, then convert. The file is a mixture of integer, ascii and floating point data. And very large. I want to read several of these files in succession. Would the stream reader speed things up? Or, if I transformed it into VB, would it go faster? Is there an inheriant speed problem with VBA because it is wrapped up in EXCEL? I only use VBA because I am most familiar with that user interface. Any comments are greatly appreciated. Mark |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed of Applications in VBA vs VB
I would just like to say a big thank you to everyone who has added comments
to this thread, I was having performance problems with Excel reading a 17meg text file, I was taking about 5 mins to read the whole file. With the comments and help added here, I have been able to get this down to seconds. Again, a big thank you to everyone. -- Cheers... "Mark HOlcomb" wrote: ,I have written an application in VBA that reads a very large binary file. I am pleased that it works at all .. but it runs too slow to be of any practical use. I read 4 bytes at a time, then convert. The file is a mixture of integer, ascii and floating point data. And very large. I want to read several of these files in succession. Would the stream reader speed things up? Or, if I transformed it into VB, would it go faster? Is there an inheriant speed problem with VBA because it is wrapped up in EXCEL? I only use VBA because I am most familiar with that user interface. Any comments are greatly appreciated. Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Applications in Excel | New Users to Excel | |||
Can you speed UP drag speed? | Excel Discussion (Misc queries) | |||
SWITCH BETWEEN APPLICATIONS | Excel Programming | |||
Other applications | Excel Programming |