Want to write function/macro for DNA reverse-complement

[Bob Harris]

Howdy,

(please excuse the cross-posting; did not know which group was appropriate)

I have a spreadsheet in which I need one cell to automatically get the
reverse-complement of a DNA string that's in another cell (details below).
I am an experienced programmer but have no experience in adding functions to
excel (maybe they are called macros). Am looking for guidance on how to
write such a function and install it in my spreadsheet.

Have searched the web for a tutorial for how to write custom functions for
excel but I get a zillion hits telling me how to enter formulas into cells.
Is there a tutorial site somewhere?

What I would like to be able to do is to have one cell (say A5) contain the
DNA string, and then have another cell contain this: "=revcomp(A5)".
revcomp would be the function I need to write. It would accept a string and
produce a string.

What reverse-complement is... A DNA string contains a sequence of the
letters { A, C, G, T }. The reverse of the string is the characters read in
reverse. The complement replaces A with T, C with G, G with C, and T with
A. So the reverse complement of AGATTGGCCAAC is GTTGGCCAATCT.

Thanks very much for any help,
Bob H

 

[JE McGimpsey]

One way:

Public Function Complement(sInput As String) As Variant
Dim i As Long
For i = 1 To Len(sInput)
Select Case Mid(sInput, i, 1)
Case "A"
Complement = Complement & "C"
Case "C"
Complement = Complement & "A"
Case "G"
Complement = Complement & "T"
Case "T"
Complement = Complement & "G"
Case Else
Complement = CVErr(xlErrValue)
Exit For
End Select
Next i
End Function

Another (no error checking):

Public Function Complement(sInput As String) As Variant
With Application
sInput = .Substitute(sInput, "A", "%")
sInput = .Substitute(sInput, "C", "A")
sInput = .Substitute(sInput, "%", "C")
sInput = .Substitute(sInput, "T", "%")
sInput = .Substitute(sInput, "G", "T")
sInput = .Substitute(sInput, "%", "G")
End With
Complement = sInput
End Function

Note: Application.Substitute can be replaced by VBA's Replace(), but it
won't work in XL97 or MacXL.

 

[Jonathan Rynd]

I have a spreadsheet in which I need one cell to automatically get the
reverse-complement of a DNA string that's in another cell (details
below). I am an experienced programmer but have no experience in
adding functions to excel (maybe they are called macros). Am looking
for guidance on how to write such a function and install it in my
spreadsheet.

What you need is a user defined function written in Visual Basic for
Applications (sometimes called a UDF in VBA). VBA is a programming
language that is included with Excel and the other components of Microsoft
Office. It can control and interact with just about every aspect of Excel.

One of the things it can do is create new functions. See this webpage for
information.
http://www.vertex42.com/ExcelArticles/user-defined-functions.html

 

[JulieD]

Hi Bob

might be a better way but here's a UDF for you
Function revstr(c)
Dim i As Long
Dim newstr As String
i = Len(c)
For i = i To 1 Step -1
Select Case UCase(Mid(c, i, 1))
Case "A"
newstr = newstr & "T"
Case "C"
newstr = newstr & "G"
Case "G"
newstr = newstr & "G"
Case "T"
newstr = newstr & "A"
End Select
Next
revstr = newstr
End Function

copy it into a module and then you can type =revstr(A1) in a cell and you
should get what you're after

Cheers
JulieD

 

[Leo Heuser]

Hi Bob

One way (for Excel 2000 and onward.
StrReverse was first introduced in Excel 2000):

Function RevDna(Cell As Range) As Variant
'Leo Heuser, 3 Aug. 2004
Dim CellValue As String
Dim Counter As Long
Dim DNA As String
Dim Dummy As Long

RevDna = CVErr(xlErrValue)

CellValue = UCase(Cell.Value)
DNA = "ACGT"

For Counter = 1 To Len(CellValue)
Dummy = InStr(DNA, Mid(CellValue, Counter, 1))
If Dummy = 0 Then Exit Function
Mid(CellValue, Counter, 1) = _
Mid(DNA, Len(DNA) + 1 - Dummy, 1)
Next Counter

RevDna = StrReverse(CellValue)
End Function

From the sheet, e.g.
=revdna(a1)

 

[Bob Harris]

and Jonathan Rynd answered:

What you need is a user defined function written in Visual Basic for
Applications (sometimes called a UDF in VBA). VBA is a programming
language that is included with Excel and the other components of Microsoft
Office. It can control and interact with just about every aspect of Excel.

One of the things it can do is create new functions. See this webpage for
information.
http://www.vertex42.com/ExcelArticles/user-defined-functions.html

BINGO! We have a winner. Thanks Jonathan! That is exactly what I was
looking for. And now knowing that they are called UDFs I have a much better
chance of googling for info about them.

Thanks also, though to a much lesser extent, to the other posters who took a
shot at writing the function I need but didn't mention how I can hook it up
to my spreadsheet.

Thanks!
Bob H

 

[JulieD]

Hi Bob

down the bottom of "my solution" i mention on how to use it - please post
back if you need further instructions.

Cheers
JulieD

 

[Bob Harris]

down the bottom of "my solution" i mention on how to use it - please post
back if you need further instructions.

Hmmm... here are the the instructions you posted:

copy it into a module and then you can type =revstr(A1) in a cell and you
should get what you're after

Well, understand that I didn't have enough knowledge about excel for the
first part of that ("copy it into a module") to be helpful. No mention of
Visual Basic, and nothing telling me how to enter the code for the function
(beyond "copy it into a module"). I asked the excel 'assistant' about
"module" but had little success. The assistant doesn't know the term UDF
(but google does, so that's a help).

The way to get there seems to be via Tools:Macro:Visual Basic Editor. Only
if I do that do I discover that I can create a new module and copy the text
into it.

No offense, but my post was a plea for "how do I create functions and attach
them to a worksheet", not a request for someone to write my function. I am
appreciative that so many people wrote the function for me. That's helpful.
But more than anything else, by far, I needed to know the mechanism for
adding such functions to a spreadsheet.

Thanks,
Bob H

 

[JulieD]

Hi Bob

one of the problems with answering newsgroup requests is that you don't know
the exact level of experience the posters have ... and you don't want to
insult them by spelling every single step out in detail if they don't need
it, but you hope not to confuse them either from your original post i
assumed that you had some experience with VBA ("i am an experienced
programmer") ... the bit about "have no experience in adding functions to
excel" i assumed to mean adding user defined functions not all code/macros
.... if i had twigged that you had no experience in excel code at all then i
would have spelt out the steps in more detail - my apologies.

but putting that aside - has your original request been met or do you
require further assistance?

Cheers
JulieD

 

[Bob Harris]

one of the problems with answering newsgroup requests is that you don't know
the exact level of experience the posters have ... and you don't want to
insult them by spelling every single step out in detail if they don't need
it, but you hope not to confuse them either

Yep. Very true. The same is true when posting a request. I tried to
indicate my level of experience regarding the question I had.

from your original post i assumed that you had some experience with VBA ("i
am an experienced programmer") ...

Seems like an extremely narrow viewpoint of the programming universe. Do
you really think all experienced programmers have some experience with
VBA????

I've been programming for roughly 30 years. I've worked on mainframes using
fortran, algol, pascal, and c; written video games in the early days of
that field, in 6502, z80, and 6809 assembly language; have written object
oriented apps for the mac in c++; have written game and puzzle applets in
java; have written audio and video signal processing code for DSPs; have
even used forth; currently write stuff to search the human genome.

That's really only the tip of the iceberg of my resume but I never once
before yesterday encountered a need to have anything to do with visual
basic. Let alone VBA which seems some specific variant of it. That's not
the part of the programming field I've been in. And there are a lot of
other experienced programmers out here that have never used VBA. You really
think ALL experienced programmers have used VBA???? There are a lot of
programming fields where that's just not gonna ever be needed.

but putting that aside - has your original request been met or do you
require further assistance?

Yes it has been met, thanks. I have successfully adapted the code posted by
Leo Heuser. I had to modify it because the excel on my machine (which calls
itself Excel 2001) does not have the string reversal function he used. Had
a little difficulty understanding the behavior of the VBA debugging
environment, but have things working now

Thanks again,
Bob H