Make Excel VBA Macros execute faster

Rows or even cells update one by one like a retarded snail. I know, it's so frustrating.
I'm going straight to the point: Here is the solution to make your VBA (VB Script) Macro runs much faster:


1 - The default file format of Macro-containing Excel files is “*.xlsm”. If you save your file as “Excel Binary Workbook (*.xlsb)” :

• Excel washes unnecessary rows/columns that reduces the file size. Less file size, faster process.
• Excel Binary File uses traditional Excel xls format, which is proved to have better performance

2 - Use Functions instead of repeating the code.  For example, if you need to repeatedly put Integer value of a float number, simply create a Function like this:

Public Function Floor(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double

Floor = Int(X / Factor) * Factor

End Function

3- Use “Select Case” instead of multiple “IF Then Else”

4- Add following two lines immediately after “Sub” declaration:

Sub mySub()

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

5- And of course, turn them on right before “End Sub”:

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

MsgBox ("Calculation Finished!")

Duplicate Multiple Queues and Topics to one with HermesJMS

Sometimes it is much easier to listen to only one Topic or Queue during a test. Therefore, I either use my local method that I explained in the other post, or duplicate and redirect the messages in one Queue. I'm not going to point why we should do it since there might be many different reasons. I personally prefer to use this solution to duplicate JMeter controllers and modify them with least effort. Also it is beneficial if you don't know to what topic you should listen to.

As you know, HermesJMS has it's own integrated programming interface with Jython language. I never liked the idea of combining two languages (worse than combining is using override approaches), but in this case it's mostly Python and there is no other choice.

I found some sample codes of how to do this in HermesJMS, therefore no credit goes for me.

Let's say there are Topic1 and Queue1 in session X1, and you want to forward them to an existing queue XQ in session X2 (or another Queue/Topic in X1):

Step 1- Open a text editor, modify following code and save as *.py file. You might need to modify lines 23-46 only.

from com.jidesoft.document import DocumentComponent
from com.jidesoft.document import DocumentComponentListener
from hermes.swing import SwingRunner
from javax.swing import JTextArea 
from javax.swing import JScrollPane
from java.lang import Runnable
from java.lang import Thread
from javax.jms import MessageListener
from org.apache.log4j import Logger

area = JTextArea()

class Forwarder (Runnable, DocumentComponent, DocumentComponentListener):
 def __init__(self):
  DocumentComponent.__init__(self, JScrollPane(area), "Message Forwarder")  
  self.__running = 1
  area.setColumns(80)
  area.setLineWrap(1)
  self.addDocumentComponentListener(self)  
  area.append("Ready\n")

 def run(self):
  hermesIn = browser.getContext().lookup("X1")
  hermesOut = browser.getContext().lookup("X2")  
  inbound1 = hermesIn.createTopic("Topic1")
  inbound2 = hermesIn.createQueue("Queue1")
  outbound = hermesOut.createQueue("XQ")
  
  while self.__running:
   message1 = hermesIn.receive(inbound1, 500)
   message2 = hermesIn.receive(inbound2, 500)
 
   if message1 != None:
    area.append("\nTransferred Topic1:")
    hermesOut.send(outbound, message1)  
    hermesOut.commit()  
    hermesIn.commit()
    area.append(message1.toString())
   if message2 != None:
    area.append("\nTransferred Queue1:")
    hermesOut.send(outbound, message2)  
    hermesOut.commit()  
    hermesIn.commit()
    area.append(message2.toString())
  hermesIn.close()
  hermesOut.close()

 def stop(self):
  self.__running = 0 

 def documentComponentOpened(self, event):  
  return

 def documentComponentClosing(self, event):
  return 

 def documentComponentClosed(self, event):
  self.stop()
  return

 def documentComponentMoving(self, event):
  return
  
 def documentComponentMoved(self, event):
  return

 def documentComponentActivated(self, event):
  return

 def documentComponentDeactivated(self, event):
  return
forwarder = Forwarder()
browser.addDocumentComponent(forwarder)
thread = Thread(forwarder)
thread.start()

Step 2: In your HermesJMS, open Jython tab in bottom-left corner. Then right-click and "load script" your Python file.

That's it. you will notice all incoming messages in X1 and X2 are getting duplicated into XQ

Simple Mutex implementation with SQL

When data in a database are used and changed by multiple processes, you have to make that piece of data isolated from other ones. In other words, you can't let two or more changes happen for one particular field in a database.

Let me provide an example. Assume that you have a phone book table that contains numerous rows of names, phone numbers, and status:

Name	Phone	Status	Last Check
John Doe	111-111-1111	OK	1-1-2014 00:00:12 UTC
Mrs. Peach	222-222-2222	OK	1-1-2014 00:00:14 UTC
Curious George	333-333-3333	Disconnected	1-1-2014 22:22:22 UTC
Roger Waters	444-444-4444	Busy	1-1-2001 01:00:12 UTC
Bill Gates	555-555-5555	Busy	1-1-1998 10:05:23 UTC
The God	000-000-0000	Unreachable	1-1-2014 20:00:10 UTC

The "Status" field shows what happened when our application tried to call "Phone" of "Name" on "Last Check".

Now you want to run your app to constantly keep the table up to date. To do that, it keeps calling all numbers in a multithreaded loop, in which number of threads are total number of the rows.  Therefore, all rows should be busy at the same time.

Here is a simple flowchart of each thread (but a bad one):

Before we talk about the problem that will occur, please see the famous Sleeping Barber problem, to understand why we need Mutex (Mutual Exclusion) and Semaphore.

For instance:

1. Thread A "Selects" one row (John Doe)
2. At the same time, Thread B "Selects" the same row.
3. Thread A calls John Doe and it's ringing. That means the "Status" is OK.
4. Thread B calls John Doe and it is busy, because thread A is calling.
5. Thread A "Updates" the row to OK
6. Thread B immediately "Updates" the row to Busy, that was not what we really meant.

Now we have some understanding. The next step comes to mind is to have a "flag" to let other threads know that particular phone number is under process, so no intervention should occur. As an example, we add a flag column to our table, and will set it to "true" when is in use, and "false" when it is not.

Name	Phone	Status	Last Check	Flag
John Doe	111-111-1111	OK	1-1-2014 00:00:12 UTC	True
Mrs. Peach	222-222-2222	OK	1-1-2014 00:00:14 UTC	True
Curious George	333-333-3333	Disconnected	1-1-2014 22:22:22 UTC	False
Roger Waters	444-444-4444	Busy	1-1-2001 01:00:12 UTC	True
Bill Gates	555-555-5555	Busy	1-1-1998 10:05:23 UTC	False
The God	000-000-0000	Unreachable	1-1-2014 20:00:10 UTC	False

And here will be the flowchart (again, not so good):

This is a better solution and a naive developer might think it's sufficient. It actually is a good solution if we're dealing with an array of objects in RAM, instead of a database resides in a hard disk and perhaps also needs some time to establish connection and getting the response. Most relational databases such as MySQL, MSSQL, Oracle and Postgres support different isolation levels. We also have predefined Table-lock and Row-lock strategies for different engines. For example, InnoDB engine is a very good choice when it comes to concurrency and its isolation level is  REPEATABLE READ , that means we can have multiple reads (Select) on each row, but only one write (Update) against the same row, and we can have multiple writes against multiple rows.
Changing the isolation level to "Serializable" makes almost same behavior, unless we would be able to write only one row at a time.
That means changing the isolation level to the highest (Serializable) will NOT help to prevent a problem like this:

1. Thread A "Selects" one row (John Doe)
2. At the same time, Thread B "Selects" the same row. (it is possible in serializable mode)
3. Thread A calls John Doe and it's ringing. That means the "Status" is OK.
4. Thread B calls John Doe and it is busy, because thread A is calling.
5. Thread A "Updates" the row to OK
6. If thread B immediately tries to "Update" the row at the same time  of Thread A, we will get an error (because the row was locked at the moment, this is a deadlock situation) 
7. If thread B  has a slight delay (most of the time), that means the job of Thread A is done and it can set the row to Busy, same problem as above.

Now we can talk about the real solution. There are three different approach we can do to prevent this race conditions:

1. (Not a good idea, but yet a solution): Set max connection to your database to "1", and increase the timeout to a bigger number. This makes all queries get into a line and get executed one by one.
2. (A better solution, not the best): You can lock the table/row even when you're doing a Select, by adding "Lock in Share Mode" at the end of select query. If you're doing this, I strongly suggest to lower your isolation level to something like READ COMMITTED.
3. (The good solution) instead of using "true" and "false" in our flag, we can have something like "UUID" and "False". Then we need to conduct the UPDATE query ALONG with our SELECT as a sub-query. This approach keeps the other threads away while the UPDATE is doing it's job (and the row is locked by the DB engine).

Here is an example:

Instead of doing following two queries in above (bad) approaches:

-> Mysql: Select name,phone From mytable Where Flag='False';
-> Script: set name_var = name
-> Mysql: Update mytable Set Flag='True' Where name = ${name_var};

Do it this way:

-> Script: generate a UUID (or a long random string or number)
-> Mysql: Update mytable Set flag= ${UUID} Where name in (Select name From (Select name From mytable Where flag='False' Order By RAND() Limit 1 ) As dummyTableName);
-> Mysql: Select name,phone From mytable Where Flag=${UUID};

SSH and remote commands during automation.

Sometimes, you have to check a system's log or run some commands during automation or load test.

For example, recently I had to perform some end-to-end tests against enterprise outdoor modems. The test case requires to perform 100 "provisioning" against each modem per hour, having 50 modems at a time. Here is a brief about what "provisioning" means:

1- Script picks one modem from data source and apply a mutex (do-not-use flag).

2- Get corresponding parameters that should be used in web service requests from data source.

3- Generate additional needed parameters (Random IDs, Names created from timestamp , etc)

4-Send some requests in ordered manner (add to ESB DB, create a traffic service, activate)

5- Listen to JMS for the response,

6- Reboot the device

7- wait for the device to get back online

8- check device status, resources and logs.

9-continue to activate more services (repeat steps 1 to 8) for x more times.

The lines I marked as red are not the hardest parts, but are the ones I'm going to write about. 

I'm going to use Putty as the extension tool because:

• I can run multiple Putty instances in multi-thread load tests,
• We can easily save logs through Putty output.

Step 1: Create sessions for each remote device. Here is what I suggest in Putty config:

1. Name the session to IP or NS to limit your data-driven setup efforts.
2. "Close window on exit" : Always
   
3. Logging: Printable output with a unique file name, Always append to the end.
   
4. Connection keepalive check interval: ~5 (that depends, but is necessary to make the window closed when inactive)
   
5. Define Data -> Auto-Login username
   
6. if you are going to send only one line of command, put it in SSH -> Remote Command; otherwise, you can put all commands in a text file and call it externally as I explain below.
   

Step 2: create command files:

Since we have two group of commands (Reboot and Monitor), then I will use external files:

• Reboot: It is usually needed to send the "reboot" command with /sbin/ prefix if  you are sending it over SSH remotely. Therefore, my reboot.txt file only contains one line (/sbin/reboot)
• Monitoring: I want to constantly tail logs, and monitor CPU and RAM usage at the same time. So I have to run one of them in the background. something like: 

                      tail -f /var/log/messages & top -ibd2

Step 3: Call putty (and ping) as OS processes:

The way to conduct this step depends the tool or language you are using, but the concept is the same. You simply need to execute a command using OS command shell. for example in Java you'll need to run it like this:

exec(String command, String[] envp, File dir)

Remember, "String command" is a little tricky in windows: you usually need to add "cmd /c" before the main command string.

What you are going to call putty is: 

c:/puttyFolder/putty.exe -load "session_name" -pw password -m c:/textFiles/Reboot.txt

And if you are going to use it in a tool like JMeter:

Comparing worksheets in older version of Excel

What would you do if there are two big Excel worksheets with different data types and you have to compare them quickly and efficiently? Indeed, there is the Sheet comparison feature in latest version of Excel (Excel 2013 and higher), but what if you have to make it available for many other testers who have older versions?

Well, many think that there is no way other than using VBA and creating Macros. I say, that would be my last choice. Excel macros are not anti-virus friendly and some users won't or even can't use macros because of that. Also many users have limited knowledge of Excel, hence we can't expect them to run or probable modify macros for their needs.

 I use macros only if cell data must get processed, converted and re-shaped to be comparable with the other sheet. If I have to create a macro, will try my best to make it as simple as possible, and accessible easy (probably by implementing buttons in the sheets).

There is another way, however, that won't need to use macros. Here I give you some examples of how we can compare two sheets using IF function in Excel Formulas.

As you know, with IF function we can examine some criteria and the set cell content based on that examination. for example we want to set A1 to True if  2+2=4 Else set the cell value to False:

IF(2+2=4,True,False)

Now assume we have Sheet1 and Sheet2 like this:

Sheet1:

Name	datetime	Score
John	4/29/2014 0:00	17
John	4/29/2014 1:00	11
Mike	4/29/2014 2:00	19

Sheet2:

Name	datetime	Score
John	4/29/2014 0:00	18
Mike	4/29/2014 1:00	12
Mike	4/29/2014 2:00	19

We can create the third Sheet to compare each row properly:


For normal textual content such as "Name" or even "Hour" column use a formula like this in topmost cell and drag it down to where you think maximum number of the rows of comparing sheets:
will be:

=IF(Sheet1!A1=Sheet2!A1,Sheet1!A1,CONCATENATE("False: ",Sheet1!A1," <> ",Sheet2!A1)

If you have date and time (datetime) in the column: You can even compare specific pieces of the Date/Time such as Hour, Day or Minute by formatting it as TEXT()
=IF(Sheet1!B1=Sheet2!B1,TEXT(Sheet1!B1,"m/d/yyy H:mm"),CONCATENATE("False: ",TEXT(Sheet1!B1,"m/d/yyy H:mm")," <> ",TEXT(Sheet2!B1,"m/d/yyy H:mm")))


The outcome will be something like:

Sheet3:

Name	datetime	Score
John	4/29/2014 0:00	False: 17 <> 18
False: John <> Mike	4/29/2014 1:00	False: 11 <> 12
Mike	False: 4/29/2014 2:00 <> 4/29/2015 2:00	9

It's almost done. Do you want it more professional and to be able to find diffs faster? use conditional formatting feature for all cells. Something like:" If Cell Value contains 'Fail' then Format it to this bg color".  Here is how it would look after:

Name	datetime	Score
John	4/29/2014 0:00	False: 17 <> 18
False: John <> Mike	4/29/2014 1:00	False: 11 <> 12
Mike	False: 4/29/2014 2:00 <> 4/29/2015 2:00	9

This would help a lot for big worksheets, MUCH FASTER THAN MACROS!

A workaround to use JMS in LoadUI

This summary is not available. Please click here to view the post.

SoapUI - Groovy Hints

Save HTTP Raw Request (in script assertion):
def rawRequest = new String( messageExchange.getRawRequestData() )



TestRunner Results:

All:
for( r in testRunner.results )
{
log.info("Name" +r.testStep.name)
log.info("SLA" + r.timeTaken)
log.info("Status" + r.status)
}

Step number # (starting from 0):
log.info testRunner.results[3].testStep.name





TimeStamp:

long timestamp = System.currentTimeMillis();

String a = timestamp.toString()
long b = a.toLong()



Remove all special characters:

.replaceAll("[^a-zA-Z0-9 ]+"," ")

How to improve freeRadius performance.

I was bored, had nothing to do, so decided to play with freeRadius configurations and see how they affect the overall performance. I was amazed that how this tricky settings can decrease or increase the power.

I was able to tweak freeRadius maximum performance from 400/sec to almost 1000 !

.conf File	Parameter	Before	After	Default	Notes
radiusd.conf	max_request_time	60	15	30	30 is suggested in Prod.
radiusd.conf	Cleanup_delay	5	2	6	3 is suggested in Prod.
radiusd.conf	max_requests	1024	5120	256
radiusd.conf	max_servers	32	64	?
radiusd.conf	min_spare_servers	5	15	?
radiusd.conf	max_spare_servers	10	35	?
radiusd.conf	max_queue_size	65536	131072	65536
sql.conf	num_sql_socks	5	32	?
sql.conf	connect_failure_retry_delay	60	5	60	10 is suggested is prod.

Indeed, this is a customized settings based on our servers and other resources. But I think it can give you some ideas.

New way of FreeRadius Performance test.

I had to conduct a heavy performance test against our AAA freeRadius server. I googled around and found there are not many options available. There are, however, few tools available around such as evolynx , JRadius and RadPerf, that were not helpful a lot in our requirement prospect.
Specially, like in our case, using EAPol (Extensible Authentication Protocol over LAN Software), leaves no choice other than using RadPerf if you're not creative enough. The problem is, RadPerf is only a name! the download link has been broken for years and no one "wants" to fix it up.
I sent several request to the RadPerf website admin (that turned out to be Alan Dekok, founder of freeRadius), who always promises to provide the updated link in "few weeks", of course if bothers to reply at all.

Therefore, I decided to find a new way to perform this test, and here is how I achieved that victory:

If you're a freeRadius user and working around EAPol protocols, you are most probably introduced to eapol_test command. That is a simple inline command to test AAA process remotely, but not designed for multi-thread processes. Obviously, you can mimic "multi-threading" by using pipelines as one command, but it's not feasible if you need to benchmark the process with 10s or even 100s of requests per second constantly.
The good news is, there are many good performance test tools that allow you to run a "process" in concurrent threads. JMeter and LoadUI are both free, and are capable to do this.
So for me, it was enough to open a "process runner" in my LoadUI, and run my eapol_test command from there: ./eapol_test -c ./eapol_test.conf.tls  -a10.80.10.109  -p1812 -stesting123 t16

If you have a powerful test machine connected to your AAA server with no bandwidth problem, then the only bottleneck you may encounter is the target itself. I could successfully ramp up to 300/second until the AAA servers CPU and swap memory got fully populated.

JMeter and JMS (HornetQ)

Here is how I send/receive messages to hornetQ  JMS:

- Create a Thread Group,
- To Send messages, use JMS publisher sampler and to receive create a JMS subscriber sampler.
- Copy library files into jmeter/lib/ext:

• hornetq-commons.jar
• hornetq-core-client.jar
• hornetq-jms-client.jar
• jnp-client.jar
• netty.jar

Set following parameters as:

• initial Context Factory: org.jnp.interfaces.NamingContextFactory
• Provider URL: (your provider url, seriously!, usually starts with jnp:// and ends with the port 1099)
• Connection Factory: ConnectionFactory
• Destination: The topic or queue you want to connect.

For publisher sampler you can add more properties in the JMS Properties box. I like to add something to JMSCorrelationID header, so I add that property as well.

For subscriber sampler, you can parametrize the JMS Selector parameter. As an instance, if you want to search for a queue with a specific correlation id, put something like: JMSCorrelationID = '${yourVariable}'


The most possible problem you migh encounter is the blocked ports. Please make sure ports 1099 and 1098 are both open in both local and network firewalls.

That's it.

Some thoughts about DHCP testing.

Writing test cases and even use cases against DHCP servers are not so easy. That is becasue most testers don't have proper networking knowledge or experience and likewise, most network guys don't know about test plans and use cases.

This is an old report I had created few years ago, but I think it still has some useful words.

DHCP Server

Suggested use cases for test cases

Functional test:

Suggested Tools:

·         In Linux Ubuntu: dhcpdump+ dhcping link

·         In Windows: Wireshark + ipconfig / dhcpcheck.exe from ks-soft.net link

Test case	Description (windows steps)	Expected Results	Comments
Verify the DHCP is enabled and active	1- In Windows (client) open cmd panel. 2- Type: ipconfig /all 3- Look for the adapter connection related to the test subject	DHCP Enabled -> Yes
Assign a new IP address automatically	1- record your client current ip address 2- In windows cmd, type: ipconfig /release 3- Type command: Ipconfig /renew 4- Check if new ip is assigned. (Use Wireshark to capture and read packets if no response received)		Make sure no firewall or static ip is active for the client. To have better results, reduce the ip expiration config in dhcp server.
Request for a new valid ip address.	1- Download dhcpcheck.exe from 2- Use cmd as administrator: dhcpcheck.exe –host:dhcp host –clientIP:valid ip address 3- Check if the new ip is assigned. (Use Wireshark to capture and read packets if no response received)
Request for invalid ip address	1- Use cmd as administrator: dhcpcheck.exe –host:dhcp host –clientIP:invalid ip address 2- Check if the expected response from server is received. . (Use Wireshark to capture and read packets if no response received) 3- Repeat the test with invalid IPs, special IPs, random string, null
Request for Duplicated ip address.	1- Use cmd as administrator: dhcpcheck.exe –host:dhcp host –clientIP:duplicated ip address 2- Check if the expected response from server is received. (Use Wireshark to capture and read packets if no response received)
Change hardware address field to invalid values	1- Use cmd as administrator: dhcpcheck.exe –host:dhcp host –clientIP:valid ip address –clientMAC: invalid MAC 2- Check if the expected response from server is received. (Use Wireshark to capture and read packets if no response received)
Sending request to an invalid host	1- Use cmd as administrator: dhcpcheck.exe –host:invalid dhcp host –clientIP: valid ip address 2- Check if the expected response from server is received. . (Use Wireshark to capture and read packets if no response received) 3- Repeat the test with invalid IPs, special IPs, random string, null
Send a request with invalid timeout, min timeout, max time out.	1- Use cmd as administrator: dhcpcheck.exe –host:valid dhcp host –timeout:1 2- Check if the expected response from server is received. . (Use Wireshark to capture and read packets if no response received) 3- Repeat the test with valid minimum timeout (based on server configuration and requirement)
Manipulate packet of requests.	This test needs more creativity. You’ll need to download Bit-Twist link 1- Capture a packet of a valid DHCP request with Wireshark. 2- Modify the packet carefully using Bit-Twist 3- Send the packet with Bit-Twist 4- Check if the expected response from server is received. (Use Wireshark to capture and read packets if no response received)		This test is checking the security and vulnerability aspects of the DHCP server and might need advance approval.

Performance Test:

Suggested Tools:

·         In Linux Ubuntu: dhcperf+ dhcpdump

·         In Windows: IXIA (if available - Recommended)

Performance Metrics

To determine the performance of a DHCP server, several performance metrics will be used in a test plan. The following terms are defined and suggested to use in the test plan and test report:

·         Connection – A single TCP connection between two end hosts, using connection establishment (3-way handshake).

·         Transaction – A single request for an object from a client to server. A transaction is made within an established Connection.

·         Connections-per-second – The rate at which new TCP connections are initiated per second.

·         Transaction Response Time – The amount of time elapsed between the time a client sends a request and the time an acceptable response to it is received.

·         Complete Transaction Response Time – The amount of time elapsed between the time a client sends the first request and the time the last acceptable response is received successfully from subsequent requests.

·         Client Capacity – Number of clients that a server can support. The maximum client capacity of a server is the threshold beyond which the server can no longer support any additional clients.

·         Success Rate – The ratio of successful transactions calculated over the total attempted transactions.

To do tests using IXIA: Follow this LINK

To do test using dhcperf: Follow this Link

Use Case 1, Max client Capacity:

To support the growing requirements of a DHCP server to handle various load conditions, it is critical to characterize its peak capabilities.

This test will help determine the maximum steady-state DHCP requests/transactions handling capability of the server. The maximum client capacity is reached when no additional clients can be realized. At this operating limit, the server will sustain a steady flow of DHCP requests, and then drop or not process any additional packets, achieving a 100% success rate.

Use Case 2, Max Transaction Rate:

This test will help determine the maximum transaction rate that the DHCP can sustain without any errors or packet losses (100% success rate). The maximum transaction rate will include all transactions for a new DHCP client to request and obtain its network configuration information from the DHCP server.

Use Case 3: Optimal Client Response Time:

This test will help determine the optimal client response time by verifying an acceptable level of performance for the DHCP server. The maximum client capacity and the maximum transaction rates will be used to help determine the upper limits of the DHCP server.

To achieve the maximum client capacity, the rate of arrival of DHCP requests at the server may be reduced to allow the server to service more requests. Therefore, to highest client capacity may require a reduced rate of arrival (transaction-rate) of DHCP requests.