10 Ways to Make your VBA Code Better

Web & Code No Comments

1. Better way to write formulas to cells

If using the same formula over the entire row or column (sometimes even uniformly repetitive formulas), its better to update the whole column or row at the same time than each cell.

Range(E4:P4).Formula = "=A$1 "
Is faster than
For k = ActiveCell.Column To 12
j=k-3
ActiveCell.Formula = "=A" & j & ""
ActiveCell.Offset(0, 1).Select
Next k
2. How to effectively copy-paste cell value
Range("E135:AH174") = Range("E135:AH174").Value
Is faster than
Range("E135:AH174").Select
Selection.Copy
Range("E135:AH174").Select
Selection.PasteSpecial Paste:=xlPasteValues
3. Turning off screen refreshing
Use
Application.ScreenUpdating=False
'code
Application.ScreenUpdating=True
At the beginning and end of the subroutine to achive a faster executing code.
4. Not using VBA helps [At times]
There are some complex ways to do conditional formatting through VBA. However, I feel one can easily implement the same using he conditional format button (Format>Conditional Format) in excel main menu.

NI DAQ – VB 6 – Analog In and out

Academics, Web & Code No Comments

This is one of the pieces of code I wrote for National instruments – data acquisition device. Again… unformatted uncommented code. Sorry!!!

The need:

Some high end academic need. Basically a combination of analog inputs gives an analog out.

The Setup ( a lot of unedited text from my report):

The system consists of hardware inputs and outputs (analog) which are interfaced with the PC using the DAQ (NI USB 6008 module). Controlling the system is a software program written in Visual basic 6. The program interacts with a database(access) to read and write values.

The data acquisition system is a National Instruments USB-6008 that has 4 Analog inputs, 2 analog outputs and 12 digital I/O lines. The kit comes with NI-DAQmx driver software and LabVIEW Signal Express software. The system can be programmed using  ANSI C and visual basic 6 (VB 6). Ref. Appendix 1 for detailed specification sheet for the NI USB-6008 DAQ

The hardware consists of

  • Toggle Switch (DEV1/AI0)
  • Push buttons (DEV1/AI1, DEV1/AI2)

The buttons indicate operations (steps of a task)

  • LED lights in serial with resistor (DEV1/AO1, DEV1/AO0)

The lights indicate success or failure of task

Read the rest of this entry »

© 2009, Nikhil Hullur | Entries RSS Comments RSS