I have a spreadsheet with a very long hex string containing data stored on a
big endian computer (or is it little, doesn't matter it's reversed) and the
byte order is reversed. I have a VBA sub that takes a string as input and
returns a reverse byte ordered string as a result. That is simple and
straightforward. Now the hard part, how do I call that sub in the
spreadsheet as if it were a function? I find help information on how to use
spreadsheet functions in VBA, but not the other way around. I should point
out this is my first VBA usage (office 2003) that wasn't just a modification
of a recorded macro. So I expect the answer will be basic and obvious (once
you know it).
Thanks in advance for your help,
Mark

Hi Mark,

Why not post the code of your Sub, so we can see how to transform that into
a Function?

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"CuriousMark" wrote in message
...
I have a spreadsheet with a very long hex string containing data stored on
a
big endian computer (or is it little, doesn't matter it's reversed) and
the
byte order is reversed. I have a VBA sub that takes a string as input and
returns a reverse byte ordered string as a result. That is simple and
straightforward. Now the hard part, how do I call that sub in the
spreadsheet as if it were a function? I find help information on how to
use
spreadsheet functions in VBA, but not the other way around. I should
point
out this is my first VBA usage (office 2003) that wasn't just a
modification
of a recorded macro. So I expect the answer will be basic and obvious
(once
you know it).
Thanks in advance for your help,
Mark

In a general module in that workbook put in your code as a function
(insert=Module in the VBE)

Public Function MyReverseString(rng as Range)
Dim s as String
s = rng(1).Value
' code to reverse s and place results in s
MyReverseString = s
End Function

then in a cell put in

=MyReverseString(A1)

--
Regards,
Tom Ogilvy

"CuriousMark" wrote in message
...
I have a spreadsheet with a very long hex string containing data stored on
a
big endian computer (or is it little, doesn't matter it's reversed) and
the
byte order is reversed. I have a VBA sub that takes a string as input and
returns a reverse byte ordered string as a result. That is simple and
straightforward. Now the hard part, how do I call that sub in the
spreadsheet as if it were a function? I find help information on how to
use
spreadsheet functions in VBA, but not the other way around. I should
point
out this is my first VBA usage (office 2003) that wasn't just a
modification
of a recorded macro. So I expect the answer will be basic and obvious
(once
you know it).
Thanks in advance for your help,
Mark

Niek, Tom
Thank you.
My code started out as a sub, but I started changing it to a function on my
own, since I wanted a return variable behavior anyway. My current code is:

Function ByteReverse(InputString As String)
Dim i As Long
Dim ByteStr, ResultStr As String
ResultStr = ""
For i = (Len(InputString) - 1) To 1 Step -2
ByteStr = Mid(InputString, i, 2)
ResultStr = ResultStr & ByteStr
Next i

ByteReverse = ResultStr

End Function

And seeing Tom's response I realized (slap hand on forehud and yell DUH)
That what I have would already work as is, well sort of, I should use the
range thing instead of just assuming string, which I will fix forthwith.

Sure enough it just works. A VBA Function becomes a spreadsheet function
without even needing to be told. If only I had found that in the help file!

Again, thank you very much.

Mark Evanetich

"Tom Ogilvy" wrote:

In a general module in that workbook put in your code as a function
(insert=Module in the VBE)

Public Function MyReverseString(rng as Range)
Dim s as String
s = rng(1).Value
' code to reverse s and place results in s
MyReverseString = s
End Function

then in a cell put in

=MyReverseString(A1)

--
Regards,
Tom Ogilvy

"CuriousMark" wrote in message
...
I have a spreadsheet with a very long hex string containing data stored on
a
big endian computer (or is it little, doesn't matter it's reversed) and
the
byte order is reversed. I have a VBA sub that takes a string as input and
returns a reverse byte ordered string as a result. That is simple and
straightforward. Now the hard part, how do I call that sub in the
spreadsheet as if it were a function? I find help information on how to
use
spreadsheet functions in VBA, but not the other way around. I should
point
out this is my first VBA usage (office 2003) that wasn't just a
modification
of a recorded macro. So I expect the answer will be basic and obvious
(once
you know it).
Thanks in advance for your help,
Mark