Dingrui‘s Blog

Data Science · Accounting & Finance · Random Thoughts

Dingrui’s Useful VBA Scripts

This blog will be updated from time to time. Please check it regularly

Here are some useful VBA scripts (Macros in Excel).

You can download it through following links.

Please enable macro settings before using the following scripts.(Click this link for more information)

  1. Web Scraping Using VBA

This one calls the “IE” explorer in windows OS.(So if you are using Mac OS, please skip this one)
Please click correct button based on your location(Since chinese government blocks Google in mainland area)
It will pop up an IE window with a Google searching result page or a Baidu searching result page.

Download Link

  1. Combine Normal Excel Files

What is a normal excel files:
– Data are all on the first sheet of the Excel
– Data contains heading in the first row and data below the heading
– The output(combination) should be less than 1048576 rows

Download Link

  1. Enhanced Dictionary in VBA

The original dictionary (“scripting.dictionary”) is fast enough for data under 1 million record.
My personal working experience suggests that the speed will become relatively slow when the size jumps to more that 1 million.
Timhall provides me an enhanced version of dictionary.

Please download it and import it as a class module to use it.

Download Link

  1. Billing System

This billing system contains two main functions:
– Spliting files
– Sending emails

Since this project is quite complicated, I will write a comprehensive blog about it when I have time.(I am currently busy preparing for my final exam)

Download Link

  1. Vlookup Function between Access Database and Excel Sheets

You will find it helpful if you are struggling with depoly “vlookup” funtion in VBA and transfer data between Access database and Excel workbooks.

Download Link

Feel free to contact me if you have any questions.

Previous

Datacamp Certificates

Next

Functional Programming in R (Using purrr Package)

1 Comment

  1. Kabir Olorede

    Thanks, Dingrui! Can you explain the first one?

发表评论

电子邮件地址不会被公开。 必填项已用*标注