byte order reversal in spreadsheet

[CuriousMark]

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 Otten]

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

 

[Tom Ogilvy]

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)

 

[CuriousMark]

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