Category Archives: VBA
VBA and Regular Expression
Posted by Shawn Zhang
on December 19, 2012
No comments
a job task about how to apply regular expression on VBA .
in Excel 2010 , a string "a;b;c" can be easily split into 3 cells with value "a" ,"b" ,"c" . but in Excel 2003 (ok, I'm a caveman ), it doesn't support such function .
Tutorial on VBA is a good start . let get down to real business:
We need to split via a character ";" from a string "Test#=14;NA=1;Email=4;Defective Email=0;" to get value 14,1,4,0 .
there are 2 types in regular expression testing :
Match or Not : to Read more [...]
Test a Element in List or Not in VBA
Posted by Shawn Zhang
on March 11, 2012
No comments
Unlike Python with functionality for set manipulation ,which will generate a easy unique set via below code:
>>> a = ("a","b","c","b","a")
>>> set(a)
set(['a', 'c', 'b'])
we can easily to test if specific element in a set via python:
>>> b = set(a)
>>> "a" in b
True
>>> "d" in b
False
But it won't be easy in VBA code . Here 's code that will iterated Read more [...]
Dive into VBA—File and Folder Operation
Posted by Shawn Zhang
on March 2, 2012
No comments
For language perspective, VBA is not the best language to perform file and folder operation, but it is easy to implement in VBA to fulfill the task. Otherwise , it is a bad design that involving two language to finish one mission , not only will increase the complexity of process but also not elegant solution .
Now, lets go through VBA function on File and Folder operation via three parts: Basic System Ops, Folder and File.
Basic System Operation
CurDir --Stands for “Current Directory”, Read more [...]