Merging Multiple Cells into Single One in Excel

These are very simple and useful macros to concatenate the content of multiple cells, merging it into one cell.

'Macros for Horizontal and Vertical concatenation

'Copyright, Andrés Gonzalez, 2008

Sub mezclar() 'horizontal concatenation

For Each fila In Selection.Rows

mensaje = ""

For Each celda In fila.Cells

mensaje = mensaje & celda.Value & " "

Next

fila.Clear

fila.Cells(1, 1).Value = mensaje

Next

End Sub

Sub mezclarV() 'vertical concatenation

For Each columna In Selection.Columns

mensaje = ""

For Each celda In columna.Cells

mensaje = mensaje & "- " & celda.Value & Chr(10)

Next

columna.Clear

columna.Cells(1, 1).Value = mensaje

Next

End Sub

Let's say you have the following table:

Albert	Einstein
Nikola	Tesla

With the first macro the result will be:

albert einstein

nikola tesla

And with the second, it will be:

- albert - nikola

- einstein - tesla

I think this one is cool, isn't it?

Lower-case Upper-case Macros on Excel

These are to simple macros to convert cells between uppercase and lowercase. I know it's very easy in Word... but I haven't found that damn button on Excel...

'This two macros replace the strings in a range to their

'equivalent in upper-case or lower-case

'Copyright, Andrés Gonzalez, 2008

Sub MINUSMAYUS()

For Each celda In Selection

celda.Value = StrConv(celda.Value, vbUpperCase)

Next

End Sub

Sub MAYUSMINUS()

For Each celda In Selection

celda.Value = StrConv(celda.Value, vbLowerCase)

Next

End Sub

Find a File's Modification Date in Excel

'This function gives the date of last modification of a given file

'example: fecharchivo("c:\testfile.txt")

' result: "15/12/2011 07:35:29 p.m."

'Copyright, Andrés González, 2008

Function fecharchivo(abrir As String) As Variant

Dim fs, f, s

Set fs = CreateObject("Scripting.FileSystemObject")

Set f = fs.GetFile(abrir)

fecharchivo = f.DateLastModified

fecharchivo = Format(fecharchivo, "General Date")

End Function

Count Unique Values in a Range of Cells

I'm not sure why I wrote this function... I'm sure there are easier and more useful ways to do this... but at the time I remember it accomplished its mission so, for what it's worth...

'This macro counts the number of unique values in a range and

'displays a message with the result

'Copyright, Andrés González, 2008

Sub contarunicos()

Dim matriz()

SizeRango = Selection.Count

ReDim matriz(SizeRango)

'matriz(pos) = rango(pos, 1)

ocurrencia = 0

celdas = 0

ultima = 1

For Each celda In Selection

For cont = 1 To ultima

If celda.Value = matriz(cont) Then

ocurrencia = "si"

Else

garbage = 1

End If

Next cont

If ocurrencia = "si" Then

ultima = ultima

Else

matriz(ultima) = celda.Value

ultima = ultima + 1

End If

ocurrencia = 0

Next

MsgBox ("There are " & ultima - 1 & " unique values")

End Sub

Replace Error Message with Custom Message in Excel

Another simple function... there are other ways to do it, but this one seems faster to me

'This function takes away errors in formulas and replace them for something

'more meaningful (a string defined by the user)

'example: quitarerror(1/0;"you can't divide by zero")

' result:"you can't divide by zero"

'Copyright, Andrés González, 2008

Function quitarerror(dato As Variant, reemplazo As Variant) As Variant

If IsError(dato) = True Then

quitarerror = reemplazo

Else

quitarerror = dato

End If

End Function

Extracting Substrings from Strings on Excel (III)

'This function extracts a substring from

'a bigger string, using a smaller string as "separator" and

'defining which one of the remaining substrings to take

'example: EXTRAERPARTE("this is a long string";1;" ")

' result: "this"

' EXTRAERPARTE("this is a long string";4;" ")

' result: "long"

'It can be very useful to separate long sentences into single words,

'by pointing the desired part to an array of sequential numbers

'%Copyright, 2008, Andrés González

Function EXTRAERPARTE(texto As String, queParte As Integer, separador As String) As String

Dim cont

numPartes = 0

partes = Split(texto, separador)

For Each parte In partes

numPartes = numPartes + 1

Next

If (queParte <= numPartes) Then

EXTRAERPARTE = partes(queParte - 1)

Else

EXTRAERPARTE = ""

End If

End Function

Extracting Substrings from Strings on Excel (II)

'This function extracts a substring from

'a bigger string, using a smaller string as "key" and

'defining two more "key characters" as delimiters on the left and

'the right sides of the string

'example: exizderT("givemeyourKEYsplease";"KEY";"m";"e")

' result:"eyourKEYspl"

'%Copyright, 2008, Andrés González

Function exizderT(texto As String, clave As String, cizquierda As String, cderecha As String) As String

inicio = InStr(1, texto, clave, 1)

izquierda = InStrRev(texto, cizquierda, inicio, 1)

If izquierda = 0 Then

izquierda = 1

Else

izquierda = izquierda + 1

End If

If inicio + Len(clave) >= Len(texto) Then

derecha = Len(texto) + 1

Else

derecha = InStr(inicio + Len(clave), texto, cderecha, 1)

End If

If derecha = 0 Then

derecha = Len(texto) + 1

End If

extraccion = Mid(texto, izquierda, derecha - izquierda)

exizderT = extraccion

End Function

Extracting Substrings from Strings on Excel (I)

Well... it does exactly what the comment says:

'This function extracts a substring from

'a bigger string, using a smaller string as "key" and

'defining a number of characters to the left and

'to the right of the key string

'example: exizderN("givemeyourKEYsplease";"KEY";2;3)

' result: "urKEYspl"

'%Copyright, 2008, Andrés González

Function exizderN(texto As String, clave As String, izquierda As Integer, derecha As Integer) As String

inicio = InStr(1, texto, clave, 1)

extraccion = Mid(texto, inicio - izquierda, izquierda + Len(clave) + derecha)

exizderN = extraccion

End Function

Extracting Numbers from Strings on Excel

Here I'll start to share some functions that I developed for Visual Basic on Excel some years ago. They all include explanatory comments in english, but the names of variables are in spanish.

This function delivers a number as double, but it can be easily modified to take it as string just erasing the last line and changing the function type to Double.

'This function extracts a numerical string from

'another string that may contain other characters

'it only extract the FIRST numerical string

'obviously, it can be pointed to a cell that contains text

'example: EXTRAERNUM("sampletext853first")=853

'example: EXTRAERNUM("sample16text853first")=16

'%Copyright, 2007, Andrés González

Function EXTRAERNUM(texto As String) As Double

Dim buscando, caracter, posCar, inicioNumero, finalNumero

buscando = "primera"

For posCar = 1 To Len(texto)

caracter = Mid(texto, posCar, 1)

If (IsNumeric(caracter) = True And buscando = "primera") Then

inicioNumero = posCar

finalNumero = posCar

buscando = "ultima"

ElseIf (IsNumeric(caracter) = True And buscando = "ultima") Then

finalNumero = posCar

ElseIf (IsNumeric(caracter) = False And buscando = "ultima") Then

buscando = "nada"

End If

Next posCar

EXTRAERNUM = Mid(texto, inicioNumero, finalNumero - inicioNumero + 1)

EXTRAERNUM = Val(EXTRAERNUM)

End Function

Resample a Matrix in Matlab

This function does the same as the previous one (resample) but for a matrix. Again, maybe not the most elegant, but works

function newmatrix=resampmatrix(matrix,newlen)

%----------------

%this function uses linear interpolation to change the number of rows in a

%matrix.

%Copyright, 2011, Andrés González

%----------------

[rows cols]=size(matrix);

newmatrix=[];

for i=1:cols

len=rows;

x=1:len;x=x';y=matrix(:,i);

xx=1:(len-1)/(newlen-1):len;

xx=xx';

yy=interp1(x,y,xx,'linear');

newmatrix=[newmatrix yy];

end

Resample in Matlab

This is a simple function to resample a vector in matlab. I now there might be more elegant ways to do it, but this one works and it's simple

function newvector=resample(vector,newlen)

%%%%%%%%%%%%%%%%%%%%

%This function uses linear interpolation to change the number of samples

% Copyright 2011 Andrés Gonzalez

%%%%%%%%%%%%%%%%%%%%

len=length(vector);

x=1:len;x=x';y=vector;

xx=1:(len-1)/(newlen-1):len;

xx=xx';

yy=interp1(x,y,xx,'linear');

newvector=yy;

end

RTAI installation on Linux (Kind of ultimate tutorial)

Well, I think this might be of interest to some people working with control systems in real time. Linux could be a good choice depending how skilled you are with it (the truth is, everything in Linux requires you to be a. very skilled and b. very patient).

A brief description:
RTAI: Real-time environment for Linux ( https://www.rtai.org/ )
COMEDI: It's an interface for sensing and control devices (http://www.comedi.org/doc/)
Scilab/Scicos: A computational package, kind of a Matlab-Simulink, but open source ( http://www.scilab.org/)
HART Toolbox: A group of modules to work with RTAI using Scicos ( http://hart.sourceforge.net/index.html )

I spent several weeks trying to make it work all together so no, it's not straight forward always (but, who knows, you could be lucky). But this tutorial can make it easier (I hope)

Most of it is taken from http://hart.sourceforge.net/rtai_installation_10_04.html and adapted to solve some of the most common problems.Without further introduction, here is the tutorial:

Installation RTAI

Packages:

UBUNTU 10.04

Install the following packages to proceed:

general:

sudo apt-get install cvs subversion build-essential

rtai:

sudo apt-get install libtool automake libncurses5-dev

comedi-lib:

sudo apt-get install bison flex

comedi-calibrate:

sudo apt-get install libboost-dev libboost-program-options-dev libgsl0-dev

scilab 4.1.2:

sudo apt-get install gfortran sablotron tcl8.5-dev tk8.5-dev xaw3dg-dev libpvm3 pvm-dev libgtkhtml2-dev libvte-dev ocaml-native-compilers

qrtailab:

sudo apt-get install libqt4-dev libqwt5-qt4-dev

Installation of all components

RTAI kernel from EMC

echo deb http://www.linuxcnc.org/lucid lucid base emc2.4 > /tmp/linuxcnc.list

echo deb-src http://www.linuxcnc.org/lucid lucid base emc2.4 >> /tmp/linuxcnc.list

sudo mv /tmp/linuxcnc.list /etc/apt/sources.list.d/

gpg --keyserver pgpkeys.mit.edu --recv-key 8F374FEF

gpg -a --export 8F374FEF | sudo apt-key add -

sudo apt-get update

sudo apt-get install linux-headers-2.6.32-122-rtai linux-image-2.6.32-122-rtai

If there is a problem downloading the patched Kernel, you can try this (taken from http://www.rtaixml.net/realtime-suite/part-2---machine-setup)

sudo wget http://sourceforge.net/projects/rtaixml/files/realtimesuite/1.0/realtimesuite-1.0.tar.gz/download

Decompress the downloaded file, navigate into the subfolder “kernel” inside realtimesuit-1.0, and run the .deb installers

Reboot in the new installed RTAI-kernel.

RTAI (https://www.rtai.org) #1

(cvs-Version)

cd /usr/src

sudo cvs -d:pserver:anonymous@cvs.gna.org:/cvs/rtai co magma

sudo ln -s magma rtai

(tar.gz-Version)

cd /usr/src

sudo wget --no-check-certificate https://www.rtai.org/RTAI/rtai-3.8.1.tar.bz2

sudo tar xjvf rtai-3.8.1.tar.bz2

sudo ln -s rtai-3.8.1 rtai

Installation of RTAI

cd /usr/src/rtai

sudo make menuconfig

Ensure directories are correct:

• Installation: /usr/realtime

• Linux source tree: /usr/src/linux-headers-2.6.32-122-rtai

• Under Machine, choose number of CPUs (check running cat /proc/cpuinfo and verifying how many processors are listed)

sudo make

sudo make install

sudo sed -i 's/\(PATH=\"\)/\1\/usr\/realtime\/bin:/' /etc/environment

now run export PATH=/usr/realtime/bin:$PATH for each open shell or do logout and login Now we need to load the rtai modules.

/sbin/insmod /usr/realtime/modules/rtai_smi.ko

/sbin/insmod /usr/realtime/modules/rtai_hal.ko

/sbin/insmod /usr/realtime/modules/rtai_lxrt.ko

/sbin/insmod /usr/realtime/modules/rtai_fifos.ko

/sbin/insmod /usr/realtime/modules/rtai_sem.ko

/sbin/insmod /usr/realtime/modules/rtai_mbx.ko

/sbin/insmod /usr/realtime/modules/rtai_msg.ko

/sbin/insmod /usr/realtime/modules/rtai_netrpc.ko

/sbin/insmod /usr/realtime/modules/rtai_shm.ko

Copy the lines to /usr/local/bin/start_rtai

Do a sudo chmod a+x /usr/local/bin/start_rtai

If there is trouble inserting the rtai_hal.ko module then:

Go to boot/grub/menu.lst or boot/grub/grub.cfg and add “lapic” at the end of this line:

kernel /boot/vmlinuz-2.6.22-realtime root=UUID=45d21232-cf0d-cc3d-87ce-8453214d7a6f ro quiet splash vga=794 lapic

COMEDI (www.comedi.org)

cd /usr/local/src

sudo cvs -d :pserver:anonymous@cvs.comedi.org:/cvs/comedi login

sudo cvs -d :pserver:anonymous@cvs.comedi.org:/cvs/comedi co comedi

sudo cvs -d :pserver:anonymous@cvs.comedi.org:/cvs/comedi co comedilib

sudo cvs -d :pserver:anonymous@cvs.comedi.org:/cvs/comedi co comedi_calibrate

sudo cvs -d :pserver:anonymous@cvs.comedi.org:/cvs/comedi co comedi-nonfree-firmware

If the comedi server is down, you can also find the packages in:

http://www.mev.co.uk/comedi/

Download them and decompress to /usr/local/src

COMEDILIB

cd /usr/local/src/comedilib

sudo sh autogen.sh

sudo ./configure

sudo make

sudo make install

sudo mkdir /usr/local/include/linux

COMEDI

cd /usr/local/src/comedi

sudo sh autogen.sh

sudo ./configure --with-linuxdir=/usr/src/linux-headers-2.6.32-122-rtai --with-rtaidir=/usr/realtime

sudo make

sudo make install

sudo make dev

sudo ldconfig

sudo depmod -a

COMEDI-CALIBRATE

cd /usr/local/src/comedi_calibrate

sudo autoreconf -i -B m4

sudo ./configure

sudo make

sudo make install

RTAI #2

sudo cp /usr/local/src/comedi/include/linux/comedi.h /usr/local/include/

sudo cp /usr/local/src/comedi/include/linux/comedilib.h /usr/local/include/

sudo ln -s /usr/local/include/comedi.h /usr/local/include/linux/comedi.h

sudo ln -s /usr/local/include/comedilib.h /usr/local/include/linux/comedilib.h

cd /usr/src/rtai

sudo make menuconfig

Under “Add-Ons”, select “Real Time COMEDI support in user space”

Check COMEDI installation directory, it should be /usr/local/

sudo make

sudo make install

sudo cp /usr/local/src/comedilib/include/comedilib.h /usr/local/include/

Now we need to load the comedi-modules. All rtai modules have to be loaded before! This is a example for the ni 6024e.

modprobe comedi

modprobe kcomedilib

modprobe comedi_fc

modprobe ni_mio_cs

insmod /usr/realtime/modules/rtai_comedi.ko

comedi_config -v /dev/comedi0 ni_mio_cs 0x0100

comedi_calibrate --calibrate

chmod a+rw /dev/comedi0

Copy the lines to /usr/local/bin/load_daq

Do a sudo chmod a+x /usr/local/bin/load_daq

If there is a error message "comedi.o not loaded", then you have to create and edit /etc/modprobe.d/comedi and add one of the following lines:

options comedi comedi_num_legacy_minors=4

(you can change the number 4 if you want), or:

options comedi comedi_autoconfig=0

The first option, comedi_num_legacy_minors=4 reserves 4 comedi devices for use by non-autoconfigured devices. The second option comedi_autoconfig=0 disables auto-configuration completely.

If there is this error:

comedi_calibrate: error while loading shared libraries: libcomedi.so.0: cannot open shared object file: No such file or directory

Then do:

cd /lib/

ln -s /usr/local/lib/libcomedi*

SCILAB 4.1.2

cd /opt

sudo wget http://www.scilab.org/download/4.1.2/scilab-4.1.2.bin.linux-i686.tar.gz

sudo tar xvzf scilab-4.1.2.bin.linux-i686.tar.gz

cd /opt/scilab-4.1.2

sudo wget http://hart.sourceforge.net/scilablibs.tar.gz

sudo tar xvzf scilablibs.tar.gz

sudo make

sudo ln -s /opt/scilab-4.1.2/bin/scilab /usr/local/bin/scilab

QRTAILab (www.qrtailab.sf.net)

(svn -version)

cd /usr/local/src

sudo svn co https://qrtailab.svn.sourceforge.net/svnroot/qrtailab/trunk qrtailab

(tar.gz - version)

cd /usr/local/src

sudo wget http://downloads.sourceforge.net/qrtailab/QRtaiLab-0.1.12.tar.gz

sudo tar xvzf QRtaiLab-0.1.12.tar.gz

If you use a tar.gz-version please replace qrtailab with qrtailab-x.x.x (x.x.x is the number of your installed version )

cd /usr/local/src/qrtailab

Edit qrtailab.config and replace '/usr/include/qwt/' with '/usr/include/qwt-qt4/' and '-lqwt' with '-lqwt-qt4'

sudo qmake-qt4

sudo make

sudo make install

HART Toolbox (www.hart.sf.net)

Chose a directory (/home/username/software, ...)

(svn -version)

svn co https://hart.svn.sourceforge.net/svnroot/hart/trunk hart

(tar.gz -version)

wget http://dfn.dl.sourceforge.net/sourceforge/hart/hart_0.4.1-src.tar.gz

tar xvzf hart_0.4.1-src.tar.gz

Go in your hart-directory.

Edit Makefile and set 'SCILAB = ...' to the binary file of scilab 4.1.2

Warning: sudo make will not install RTAI module! Either be a normal user and install HART or change to root (using sudo su) and then do make.

make

cd /..../scilab-xxx/contrib

sudo ln -s {link to hart-toolbox} .