Tường nhà

Nguyen Huu Loi
Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean
Dim sht As Worksheet

If wb Is Nothing Then Set wb = ThisWorkbook
On Error Resume Next
...
Xem thêm Set sht = wb.Sheets(shtName)
On Error GoTo 0
SheetExists = Not sht Is Nothing
End Function
 .29/06/2016 09:03
Nguyen Huu Loi
On the eve before Christmas, Im writing this article on VBA code optimization for my blog readers as a gift. It contains both the knowledge and good practices which if followed and used effectively then it might put you in elite category of excellent VBA programmers. VBA programs are known to save a lot of time and now this post is to save further time. Read them carefully and open new pathways of innovation in creating excel dashboards, reports and automation. Wish my readers a very MerRy ChRisTMaS !

Here is a summary of the article:
1. Analyze the Logic 2. Turn off ScreenUpdating
3. Turn off Automatic Calculations 4. Disable Events
...
Xem thêm5. Hide Page breaks 6. Use WITH statement
7. Use vbNullString instead of 8. Release memory of Object variables
9. Reduce the number of lines using colon( 10. Prefer constants
11. Avoid Unnecessary Copy and Paste 12. Clear the Clipboard after Paste
13. Avoid Macro Recorder style code. 14. Use For Each than Indexed For
15. Use Early Binding rather Late Binding 16. Avoid using Variant
17. Use Worksheet Functions wherever applicable

Do read full article to understand the logic behind them.


--------------------------------------------------------------------------------

Analyze the logic: Before optimizing the syntax, pay more attention in optimizing the logic. Without a good logic, a good written VBA macro program has no value. So streamline your program logic and get the best performance of macros.

Avoid Screen Flickering or Screen Repainting: Use
Application.ScreenUpdating = False To Turn Off at the start of code.
Application.ScreenUpdating = False To Turn on at the end of the code.

The ScreenUpdating property controls most display changes on the monitor while a procedure is running. When screen updating is turned off, toolbars remain visible and Word still allows the procedure to display or retrieve information using status bar prompts, input boxes, dialog boxes, and message boxes. You can increase the speed of some procedures by keeping screen updating turned off. You must set the ScreenUpdating property to True when the procedure finishes or when it stops after an error. Refer MSDN for details.

Turn off automatic calculations: Whenever content(s) of a cell or range of cells are changed, the formulas dependent on them and Volatile functions are recalculated. You may turn off the automatic calculation using
Application.Calculation = xlCalculationManual To turn off the automatic calculation
Application.Calculation = xlCalculationAutomatic To turn On the automatic calculation
Now, whenever due to the program logic(that due to macros dependent on existing formulas) you need to calculate the formulas, you may use the following code accordingly.
ActiveSheet.Calculate To calculate the formulas of Active Worksheet
Application.Calculate To calculate the formulas of Active workbook or all workbooks in current application.

Stop Events: Use Application.EnableEvents to tell VBA processor whether to fire events or not. We rarely fire an event for each cell were changing via code. Hence, turning off events will speed up our VBA code performance.

Hide Page Breaks:When we run a Microsoft VBA macro in a later version of Microsoft Excel, the macro may take longer to complete than it does in earlier versions of Excel. For example, a macro that required several seconds to complete in an earlier version of Excel may require several minutes to complete in a later version of Excel. This problem may occur if the following conditions are true:

* The VBA macro modifies the properties of many rows or columns.
* An operation has been performed that forced Excel to calculate page breaks. Excel calculates page breaks when we perform any of the following operations:

o We display a print preview of your worksheet.
o In Microsoft Office Excel 2003 and in earlier versions of Excel, we click Page Setup on the File menu.
o We modify any of the properties of the PageSetup object in a VBA macro.
* In Excel 2003 and in earlier versions of Excel, we ed the Page breaks check box on the View tab of the Options dialog box.
Solution: is to disable Page breaks using ActiveSheet.DisplayPageBreaks = False
Refer to this Microsoft article for more details - http://support.microsoft.com/kb/199505

Use WITH statement when working with objects: If we have to access an objects properties and methods in several lines, we must avoid using objects name or fully qualified object path again and again. It is annoying for VBA processor as it needs to fully qualify the object each time. (Isnt it annoying for us too when some work or something is told us again and again? Got it Guys !
SLOW MACRO

FAST MACRO

Sheets(1).Range(A1:E1).Font.Italic = True
Sheets(1).Range(A1:E1).Font.Interior.Color = vbRed
Sheets(1).Range(A1:E1).MergeCells = True With Sheets(1).Range(A1:E1)
.Font.Italic = True
.Font.Interior.Color = vbRed
.MergeCells = True

End With
The point here to understand is minimum qualifying of an object by VBA processor. i.e. using minimum dots/periods(.) in the code. This concept tells us to use [A1] rather than Range(A1) and Range(StockRange)(3,4) rather than Range(StockRange).Cells(3,4)


Use vbNullString instead of (2 double quotes) : vbNullString is slightly faster than , since vbNullString is not actually a string, but a constant set to 0 bytes, whereas is a string consuming at least 4-6 bytes for just existence.
For example: Instead of strVariable = , use strVariable = vbNullString.

Release memory from object variables: Whenever we create an object in VBA, we actually create two things -- an object, and a pointer (called an object reference). We might say, VB does not use pointers, but it is not true. VB does not let you manipulate pointers is more precise. Behind the scenes, VB still makes extensive use of pointers. To destroy an object in VB, you set it to Nothing. But wait a minute. If all we ever use are object pointers, how can we set the object itself to Nothing? The answer is: We cant.
When we set a reference to Nothing, something called the garbage collector kicks in. This little piece of software tries to determine whether or not an object should be destroyed. There are many ways to implement a garbage collector, but Visual Basic uses what is called the reference count method.
When VB interprets the last line(where we generally sets our objects to Nothing), it will remove the existing reference. At this point, if the object has no more references, the garbage collector will destroy the object and deallocate all its resources. If any other references point to the same object, the object will not be destroyed.

Reduce the number of Lines: Avoid multiple statements especially when they can be clubbed into one line. For example - See these 2 macros
SLOW MACRO

With ion
.WrapText = True
.ShrinkToFit = False

End With FAST MACRO


With ion
.WrapText = True: .ShrinkToFit = False

End With
As you can see, you can club multiple statements into one using colon character(. When you do this with multiple statements, it will decrease the readability but will increase the speed.
Compiler Logic: When we save the macro, it is virtually compiled and unlike its human readable form as we saw in VBE(Visual Basic Editor), keywords(the dark blue words which you cannot use as variable) are saved as three-byte token which are faster to process as machine understand them better and variables, comments and literal strings which are not either keyword or directive are saved as is. However VBA compiler tokenizes the word but it does not compress the lines and each line is maintained as is ending with the Carriage Return. When the VBA macro is executed, VBA processor fetched single line at a time. The tokens of the fetched line saved by virtual compiler are now interpreted and executed then next line is fetched and so on. When we combine multiple lines using colon into one line, were reducing the number of fetch cycles the processor must go through.
Our Suggestion: This change will bring minor difference in time due to faster processors today. Moreover, you cannot have more than 255 characters in a line and you wont be able to debug your code using F8 efficiently. So its a kind of useless, there is no reason to trade-off with readability with such a minor change in time.

Declare variable as Variable and constant as Constant: Seems, obvious ! But many of us dont follow it. Like
Dim Pi As Double
Pi = 3.14159
instead use
Const Pi As Double
Pi = 3.14159
Since, its value is never changed so it will be evaluated once during compilation unlike variable which are evaluated many times during the run-time.

Avoid Unnecessary Copy and Paste: Follow this table rules:
Instead of Use this:
Sheet1.Range(A1:A200).Copy
Sheet2.Range(B1).PasteSpecial
Application.CutCopyMode = False
Clear Clipboard Bypass the Clipboard
Sheet1.Range(A1:A200).Copy Destination:= Sheet2.Range(B1)
Sheet1.Range(A1:A200).Copy
Sheet2.Range(B1).PasteSpecial xlPasteValues
Application.CutCopyMode=False

Clear Clipboard Bypass the Clipboard if only values are required
Sheet2.Range(B1:B200).Value = Sheet1.Range(A1:A200).Value
Sheet1.Range(A1:A200).Copy
Sheet2.Range(B1).PasteSpecial xlPasteFormulas
Application.CutCopyMode=False

Clear Clipboard Bypass the Clipboard if only formulas are required
Sheet2.Range(B1:B200).Formula = Sheet1.Range(A1:A200).Formula

Same can be done with FormulaR1C1 and Array Formulas.

Use Worksheet Functions rather developing own logic: By using Application.WorkSheetFunction, we tell VBA processor to use native code rather than interpreted code as VBA understands the worksheet functions better than your algorithm. So, for example use
mProduct = Application.WorkSheetFunction.Product(Range(C5:C10))
rather than defining your own logic like this:
mProduct = 1
For i = 5 to 10
mProduct = mProduct * Cells(3,i)

Next

Use For Each rather than indexed For: We can avoid using Indexed For when looping through collections. For example, take the code just before this tip. It can be modified to:
For Each myCell in Range(C5:C10)
mProduct = mProduct * myCell.Value

Next
This is in relation to qualifying object again and again as using WITH statements.

Avoid using Macro Recorder style code:Ya, the code will look genius and eventually perform like Genius too ! Youll better catch it with example, so use:
[A1].Interior.Color = vbRed
rather than
Range(A1).
ion.Interior.Color = vbRed
Using too many and ion effects the performance drastically. Ask yourself why to go in Cell and then change the properties? or rather ask why to go pizza shop when you can enjoy it at your home

Avoid using Variant and Object in declaration statements: Think about better logic and get rid of them. i.e. do not use Dim i As Variant or Dim mCell As Object. By trying to be specific,we will save a lot of system memory this way, particularly in case of large projects. We may not remember which has been declared variant above and misuse a variable assigning any value to it which will be type-casted without errors. A variants descriptor is 16 bytes long while double is 8 bytes, long is 4 bytes and integer is 2 bytes. Hence use Variant cautiously. As an example, use:
Dim i As Long rather than Dim i As Variant
Similarly use:
Dim mCell As Range or
Dim mSheet As Worksheet
rather than
Dim mCell As Object or
Dim mSheet As Object

Declare OLE objects directly: Declaring and defining the OLE objects in declaration statement is called Early Binding but declaring an object and defining it later is called Late Binding. Always prefer Early Binding over Late Binding. Now for example use:
Dim oXL As Excel.Application
rather than
Dim oXL As Object
Set oXL = CreateObject(Excel.Application)

 .29/06/2016 08:41
Nguyen Huu Loi
http://www.excelitems.com/2010/12/optimize-vba-code-for-faster-macros.html
 .29/06/2016 08:40
Nguyen Huu Loi
SearchV = Range("A8D8".Find(What:="Nov", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False).Column

lastrow = Cells(Rows.Count, SearchV).End(xlUp).Row
 .27/06/2016 14:57
Nguyen Huu Loi
Sub )

Dim rngX As Range

Set rngX = Worksheets("Source".Range("A1:A10000".Find(" Excel", lookat:=xlPart)
...
Xem thêm If Not rngX Is Nothing Then
MsgBox "Found at "
 .27/06/2016 13:58
Nguyen Huu Loi
Dim arrWsNames As String() = {"Value1", "Value2"}
Dim myarray As Variant
myarray = Array("Cat", "Dog", "Rabbit"


...
Xem thêm
34

down vote


In the specific case of a String array you could initialize the array using the Split Function as it returns a String array rather than a Variant array:
Dim arrWsNames() As String
arrWsNames = Split("Value1,Value2,Value3", ","

This allows you to avoid using the Variant data type and preserve the desired type for arrWsNames.

Sub Test()
Dim Cat As String, Dog As String, Mouse As String
Dim Itm

Cat = "c"
Dog = "d"
Mouse = "m"

For Each Itm In Array(Cat, Dog, Mouse)
MsgBox Itm
Next Itm
End Sub

Dim Cat, Dog, Mouse As String
Dim st
Cat = 1
Dog = 2
Mouse = 3
For Each st In Array(Cat, Dog, Mouse)
MsgBox st
Next st
 .27/06/2016 10:29
Nguyen Huu Loi
Dim x as Integer
Dim wbA as Workbook, wbB as Workbook

Set wbA = Workbooks("FileA"
Set wbB = Workbooks("FileB"
...
Xem thêm
For x=1 to wbA.Sheets.Count
wbA.sheets(x).Copy After:=wbB.sheets((2*x)-1)
Next x
 .24/06/2016 08:45
Nguyen Huu Loi
Option Explicit ' at top of module - forces explicit declaration of variables,
'a good thing particularly while learning
Sub CopySheets()
Dim wbFileA As Workbook
Dim wbFileB As Workbook
...
Xem thêm Dim sh As Worksheet
Dim shCopAfter As Worksheet

' Point to the workbooks
Set wbFileA = Application.Workbooks("NameOfFileA.xls"
Set wbFileB = Application.Workbooks("NameOfFileB.xls"

' Set pointer to first sheet in FileB
Set shCopAfter = wbFileB.Sheets(1)

' loop through the sheets in FileA
For Each sh In wbFileA.Sheets
' Copy sheet to FileB
sh.Copy After:=shCopAfter
' If last sheet in book then set shCopyAfter to last sheet
If ActiveSheet.Index >= wbFileB.Sheets.Count Then
Set shCopAfter = ActiveSheet
Else
' Else set shCopyAfter to the one after the one just copied
Set shCopAfter = wbFileB.Sheets(ActiveSheet.Index 1)
End If
Next
End Sub
 .24/06/2016 08:45
Nguyen Huu Loi
IoC là gì? Các giải pháp IoC và cách sử dụng UnityContainer trong C#


Giả thuyết vấn đề

...
Xem thêmCase: đối với các dự án vừa và lớn, với rất nhiều class và interface, có sự phụ thuộc phức tạp giữa các đối tượng trong hệ thống, việc khởi tạo các object và các service sẽ trở nên rườm rà và liên quan đến rất nhiều chi tiết.

Fact: việc viết code đòi hỏi những chuẩn mực nhất định, một ví dụ thường gặp nhất khi khởi tạo 2 object có sự phụ thuộc với nhau như sau:



1
2

IA a = new A();
IB b = new B(a);


Từ ví dụ trên ta thấy, mỗi khi cần khởi tạo object B ta luôn cần khởi tạo object A trước. Với số lượng object phụ thuộc ngày càng tăng, việc này sẽ dẫn đến khó khăn nhất định.

Vậy có cách nào để đơn giản hóa việc này?

Solution: với IoC, ví dụ trên có thể đơn giản hơn như sau:



1

IB b = ServiceLocator.Get<B>();


ServiceLocator sẽ tự biết cách khởi tạo object A và invoke hàm khởi tạo của B.

ServiceLocator làm điều đó bằng cách nào? Tất nhiên chúng ta sẽ phải cấu hình để ServiceLocator làm đc điều đó, thông qua viết code hoặc file cấu hình (tùy vào khả năng hỗ trợ của tool)

Sức mạnh của IoC nằm ở khả năng reusable và flexible, cùng 1 dòng khởi tạo như trên, code thực thi trong Runtime có thể khác nhau nếu ta thay đổi cấu hính, điều này khiến ứng dụng trở nên uyển chuyển và dễ test.

Vậy tóm lại, IoC là gì?

Định nghĩa từ Wikipedia:



1

In <a title="Software engineering" href="http://en.wikipedia.org/wiki/Software_engineering">software engineering</a>, <b>inversion of control</b> (<b>IoC</b> is a programming technique, expressed here in terms of <a title="Object-oriented programming" href="http://en.wikipedia.org/wiki/Object-oriented_programming">object-oriented programming</a>, in which object coupling is bound at <a title="Run time (program lifecycle phase)" href="http://en.wikipedia.org/wiki/Run_time_(program_lifecycle_phase)">run time</a> by an assembler object and is typically not known at <a title="Compile time" href="http://en.wikipedia.org/wiki/Compile_time">compile time</a> using <a title="Static code analysis" href="http://en.wikipedia.org/wiki/Static_code_analysis">static analysis</a>.


Lợi ích của IoC như sau (từ wikipedia):
•There is a decoupling of the execution of a certain task from implementation.
•Every module can focus on what it is designed for.
•Modules make no assumptions about what other systems do but rely on their contracts.
•Replacing modules has no side effect on other modules.

Các lợi ích trên của IoC có thể dễ dàng suy luận từ khả năng cấu hình mà IoC mang lại. Dưới đây là một ví dụ về cấu hình của IoC sử dụng StructureMap (lấy từ mã nguồn SharpLite):



1
2
3
4
5
6
7
8
9
10
11
12

public static void Initialize() {
Container container = new Container(x => {
x.For<ISessionFactory>()
.Singleton()
.Use(() => NHibernateInitializer.Initialize().BuildSessionFactory());
<span style="color:#0000ff;">x.For<IEntityDuplicateChecker>().Use<EntityDuplicateChecker>();</span>
<span style="color:#0000ff;">x.For(typeof(IRepository<>.Use(typeof(Repository<>;</span>
x.For(typeof(IRepositoryWithTypedId<,>.Use(typeof(RepositoryWithTypedId<,>;
});

DependencyResolver.SetResolver(new StructureMapDependencyResolver(container));
}


2 dòng màu xanh, chỉ định sự liên quan giữa interface và class đã implement nó. Hiểu nôm na, nó giống như bạn đăng kí với 1 người cung cấp dịch vụ:



1
2
3

- Tôi cần đồ uống (interface X)
- Xin hỏi anh cần đồ uống loại nào? cafe (A:X), trà (B:X), ...?
- Buổi sáng tôi muốn uống cafe, còn buổi trưa thì uống trà


Như vậy, mỗi khi bạn gọi đồ uống ở dịch vụ nơi đó, bạn ko cần nhắc lại yêu cầu của mình nữa
 .23/06/2016 11:33
Nguyen Huu Loi

Introduction to Dependency Injection


--------------------------------------------------------------------------------
...
Xem thêm

Dependency Injection (DI) is a design pattern that demonstrates how to create loosely coupled classes. The term loosely coupled deserves clarification and sometimes the best way to explain something is by first describing its opposite, which in this case is tight coupling. When two classes are tightly coupled, they are linked with a binary association. For example, you might have two classes, Class1 and Class2, that are joined together as an aggregation. This is shown in the following code.


Copy


public class Class1
{
public Class2 Class2 { get; set; }
}

public class Class2
{
}


The following figure illustrates the concept of tight coupling.


Two Tightly Coupled Classes

Referenced Image

Typically, if Class1 and Class2 were loosely coupled, Class1 would have a reference to an interface instead of a direct binary reference to Class2. This is shown in the following code.


Copy


public class Class1
{
public IClass2 Class2 { get; set; }
}

public interface IClass2
{
}

public class Class2 : IClass2
{

}


The following figure illustrates the concept of loose coupling.


Two Loosely Coupled Classes

Referenced Image

However, this implementation of loose coupling presents a problem. If Class1 is responsible for creating a new instance of Class2, you only have the illusion of being loosely coupled because Class1 must still know about Class2. This is shown in the following code.


Copy


public class Class1
{
public Class1()
{
Class2 = new Class2();
}
public IClass2 Class2 { get; set; }
}

public interface IClass2
{
}

public class Class2 : IClass2
{
}


As you can see, because of its constructor, Class1 is still tightly coupled to Class2. A solution to this problem is to use DI. A third class that resolves the dependencies eliminates the last bit of tight coupling. The following code uses DI to implement loose coupling.


Copy


public class Class1
{
public readonly IClass2 _class2;

public Class1():this(DependencyFactory.Resolve<IClass2>())
{

}

public Class1(IClass2 class2)
{
_class2 = class2;
}
}


Even after a demonstration of how to use DI, you still might wonder why it is useful. Two answers are that it is useful for unit testing, validation and exception management.

Unit Testing. Unit testing, or white box testing, tests a unit of work (often, this is a method). The assumption is that the developer writes the tests because it is the developer who knows the implementation details of what is being tested. A proper unit test can run without any reliance on external dependencies. DI enables you to replace complex dependencies, such as databases, with mocked implementations of those dependencies. This allows you to completely isolate the code that is being testing. For more information about unit testing in WCF, see www.microsoft.com.

Validation/Exception Management. DI allows you to inject additional code between the dependencies. For example, it is possible to inject exception management logic or validation logic, which means that the developer no longer needs to write this logic for every class. For more information about how to use DI for validation and exception management, see www.microsoft.com.

The rest of this article focuses on how to implement DI in WCF services.


Introduction to Unity


--------------------------------------------------------------------------------


Unity is Microsofts Patterns and Practices solution for DI. It can be downloaded here: http://msdn.microsoft.com/en-us/library/ff663144.aspx.

Unity can also be combined with the great features for exception management and logging that are in the full version of the Microsoft Enterprise Library, which can be found here: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=bcb166f7-dd16-448b-a152-9845760d9b4.

With Unity you can either use configuration files to load dependencies, which is an approach that offers flexibility, or you can use code to register dependencies.

A Note on Configuration versus Code: Although configuration files do offer a flexible way to register dependencies, there is currently no tool to manage those files, so it must be done by hand. In addition, in many organizations, this flexibility is often limited by IT policies. It can require just as much effort, QA testing, and paperwork to move a configuration change into production as it takes for a code change. Consequently, it may be more efficient to use code to register dependencies than to use configuration files.

Unity includes the Interception container extension, which allows you to inject exception management, logging, or even your own custom code between the caller and the called. If you consider Class1 and Class2 from the code examples in the Introduction, an example of when to inject additional logic would be if Class1 needs to make a call to an operation on Class2. You can use Interception to inject the additional logic without having to change any code in Class1 or in Class2.

This article is not an in-depth discussion on how to use Unity, or how to configure it. There is already a great deal of information available. Links to some websites are included at the end of this article, under Additional Resources. Instead, this article focuses on how to use Unity as a DI solution within WCF. There are a number of other tools, other than Unity, that are available for DI. The concepts explained in this article are valid, regardless of the particular tool that you use.
 .23/06/2016 11:31