dark

如何大量加快工作簿速度并使宏运行得更快-Excel VBA很有趣

Total
0
Shares
0
0
0



了解为什么您的工作簿/宏运行缓慢，为什么要使一些提示更快？ 很多时候，您呆滞的宏会变成不到一秒钟的长度！ **限量提供****学习使用Ultimate Excel Programmer课程让Excel为您工作的乐趣–使用优惠券代码获取95％的完整9小时高级高级课程，需使用优惠券代码：https://www.udemy.com/ultimate- excel-programmer /？couponCode = 2016YOUTUBE75单击立即订购并获得对课程，工作簿，更新和支持的终身访问！ ————————使用Excel VBA创建自己的条形码查找系统。 学习轻松制作自己的条形码标签，并与条形码扫描仪一起玩乐，以自动执行工作流程！ 在此项目驱动的课程中，您将学习如何使用分步视频说明来构建自己的自定义库存系统。 这将深入到一些高级的用户窗体策略中，您可以将其用于其他项目。 一个很棒的周末学习！ YouTube Subsciber只需支付四分之一！ 在此获得您的75％优惠券：https://www.udemy.com/barcodes-excelvba/?couponCode=2016YOUTUBE75 ————————太忙了学习钢琴？ 使用我的秘密，立即播放您喜欢的歌曲！ 你知道吗; 您可以在大约5分钟内学习4个基本和弦，并立即可以播放数百万首歌曲。 认真地检查此课程。 我的YouTube朋友可享受75％的折扣！ 单击此处：https://www.udemy.com/play-piano-by-ear-today-supercourse/?couponCode=2016YOUTUBE75有关文章，更新和与我联系以咨询或只是给我打个电话，请访问http ：//www.ExcelVbaIsFun.com要下载此免费工作簿（包括源代码）：单击下面的链接。 http://excelvbaisfun.com/mdocs-posts/how-to-massively-speed-up-your-workbook-and-make-macros-run-faster-excel-vba-is-fun/– ——————播放列表：Excel Vba基础知识http://www.youtube.com/watch?v=AIhKNNXzZLM&list=PLw8O1w0Hv2ztGjIkrW7suD6oNDaOk3vbR&index=1技巧N技巧https：// www .youtube.com /播放列表？列表= PLw8O1w0Hv2zsPU-k2vPZ_6brD5Gk1sVy_用户窗体https://www.youtube.com/playlist?list=PLw8O1w0Hv2zvnLFyiMrihcaOqA0sT0X2U示例Excel程序https://www.youtube.com/playlist?list=PLw8O1w0Hv2zuP-BE7tV1UjWa0ZexZqY89活动系列https://www.youtube.com/playlist?list=PLw8O1w0Hv2zvf-YaBApsIHloI9G6Cq1Wh Activex控件https://www.youtube.com/playlist?list=PLw8O1w0Hv2zupzNIWkZCOYYngTtMH5nSf —————- ——–拿起Excel书呆子T恤或连帽衫：http://ExcelVbaIsFun.spreadshirt.com/ ———————- -进行互动并在以下位置关注我：LinkedIn：http://www.linkedin.com/in/danielcstrong Facebook：https://www.facebook.com/Excelvbaisfun/ Twitter：https://twitter.com/ExcelVbaI sFun Google Plus +：http://www.google.com/+ExcelVbaIsFun“ http://audiomicro.com/royalty-free-music提供的免版税音乐”。

24 comments

  1. How do you replace copy paste method to (a)=(b) or actual cell referencing for variable ranges of cells? I understand it will be a lot faster but how do i do that? Like for example for copy method you can select range("a1:z" & lastrow) with lastrow being a variable and copy then paste, but if i can also do that using direct referencing then it will be much faster ?

  2. everybody in the comments is crying about 20 seconds and 1 minute. I've been developing a project for my company for the past half a year that is a constant work in progress. it's a combination of metrics and reports and data is constantly getting bigger and bigger. My code went from 25 seconds to minutes, to 45 minutes and lately one hour and 30 minutes.

    I'm really looking to find out if the application.calculation = manual part is going to do the magic and speed it up. I don't mind waiting few minutes and watching it dance but one hour and 30 minutes is mental at this point. I understand that it would take about 2 weeks worth of manual labor to compile all of it but still. I hope this trick works. Thanks for sharing and uploading

  3. Followed instructions, this actually disabled all the subs. My Form was worthless. Cool video though, just not what I need. I’ll figure it out. Keep making these! They’re awesome! Subscribed!

  4. Oh my, thank you so much – had a big macro that included other macros – was taking 50 seconds to complete – now takes around 7 seconds. Amazing.

  5. My command buttons need to clicked once then again, then again sometimes, that's annoying. I'll try these tips! thank you.

  6. you can speed up that for x slope massively with factor 1/(number of rows) by making the goalrange to an data array, change the array and make the changed array to range again. Its also faster to read out of a array instead of a cell a=r (y,x) much faster than a=cells (y,x)!
    So first grab the whole range to an data array before processing!

  7. My UserForm was taking 20-30 seconds to finish and add data to the sheet (over 5000 rows). After I applied the code shown in this video to the area of my code that assigns values to the worksheet; my form finishes and updates the sheet in 2-3 seconds!

    Awesome tip!

  8. After watching 20 youtube videos, 100% did not solve your problem. Let me pour a few cents here :
    1) Delete the end cells (Shift+Ctrl+End), you need to delete whole rows & columns – 98% of slow excel problem
    2) The others are not much help
    – disable auto calculation
    – dis-format especially conditional formatting
    – Data validation
    – Pivot table
    – Macro
    – Use better and shorter formula

  11. Hey, Can some one tell me What amount of RAM is used during Your calculations?
    I have 64 bit windows and64 bit office on a 16 GB RAM pc but only use 4 GB wtf ? Did I do something wrong?

  14. All, I found something big I think.  My macro was taking 10-20 seconds each time to run.  I was already using screenupdating which before that the macro never seemed to finish (almost a minute).  Even after all above tips, I was stuck at 10-20 seconds.  I found that after a few seconds, if I clicked off the Excel workbook onto any other running window on my desktop, getting focus OFF of Excel, it would finish "instantly".  This bugged me because I knew something could be done to seriously speed it up.  I used the command "application.visible=false" at the start of my main macro in the MODULE code, and then visible=true at the end.  Voila!  My marco now runs in 1 second!  Sure, a little odd that the entire excel spreadsheet window disappears temporarily, but in my case, my macro runs on workbook_open, when you first double click the file, so it's not so bad to have the window "flash" for 1 second off then back when opening, to make the macro run in 1 second rather than see a spinning circle cursor for 10-20 each time you open it.  I am thinking there must be yet another similar command to divert excel away from itself, to do the same thing, rather than application.visible=false.  For example, I noticed if I have the VBE window open and I was tweaking code, when I would run /continue running the macro from that window, since FOCUS remains OFF the excel window, in some cases it would also run in just one second, and I would be thinking the macro didn't run.  I am using application.visible=false then back to true following my startup macro now as the biggest improvement factor yet.

Comments are closed.