I can't figure out what's wrong with this function. When I try to call it
from Excel it gives me the "#VALUE!" error.

This function should be an implementation of the Burrows-Wheeler
Transformation. I have gone through the code with a fine tooth comb, but
can't find the problem myself.

### START CODE ###

Public Function BWT_Encode(CodeString) As String
Dim TheStrings() As String
Dim Result As String
Dim Length As Long
Dim A As Long

Length = Len(CodeString)
Result = ""

' First build the string table
TheStrings(1) = CodeString
For A = 2 To Length
TheStrings(A) = Right(TheStrings(A - 1), 1) & Left(TheStrings(A - 1),
Length - 1)
Next A

' Now sort the array alphabetically
Call StrSort(TheStrings(), True, True)

' Now get last letter
For A = 1 To Length
Result = Result & Right(TheStrings(A), 1)
Next A
BWT_Encode = Result
End Function

### END CODE ###

What does "StrSort" do?

Barb Reinhardt




"Magius96" wrote:

I can't figure out what's wrong with this function. When I try to call it
from Excel it gives me the "#VALUE!" error.

This function should be an implementation of the Burrows-Wheeler
Transformation. I have gone through the code with a fine tooth comb, but
can't find the problem myself.

### START CODE ###

Public Function BWT_Encode(CodeString) As String
Dim TheStrings() As String
Dim Result As String
Dim Length As Long
Dim A As Long

Length = Len(CodeString)
Result = ""

' First build the string table
TheStrings(1) = CodeString
For A = 2 To Length
TheStrings(A) = Right(TheStrings(A - 1), 1) & Left(TheStrings(A - 1),
Length - 1)
Next A

' Now sort the array alphabetically
Call StrSort(TheStrings(), True, True)

' Now get last letter
For A = 1 To Length
Result = Result & Right(TheStrings(A), 1)
Next A
BWT_Encode = Result
End Function

### END CODE ###

What does "StrSort" do?

It sorts the strings in alphabetical order using the following functions:

Public Static Sub StrSort(ByRef words() As String, Ascending As Boolean,
AllLowerCase As Boolean)

'Pass in string array you want to sort by reference and
'read it back

'Set Ascending to True to sort ascending, '
'false to sort descending

'If AllLowerCase is True, strings will be sorted
'without regard to case. Otherwise, upper
'case characters take precedence over lower
'case characters

Dim I As Integer
Dim J As Integer
Dim NumInArray, LowerBound As Integer
NumInArray = UBound(words)
LowerBound = LBound(words)
For I = LowerBound To NumInArray
J = 0
For J = LowerBound To NumInArray
If AllLowerCase = True Then
If Ascending = True Then
If StrComp(LCase(words(I)), _
LCase(words(J))) = -1 Then
Call Swap(words(I), words(J))
End If
Else
If StrComp(LCase(words(I)), _
LCase(words(J))) = 1 Then
Call Swap(words(I), words(J))
End If
End If
Else
If Ascending = True Then
If StrComp(words(I), words(J)) = -1 Then
Call Swap(words(I), words(J))
End If
Else
If StrComp(words(I), _
words(J)) = 1 Then
Call Swap(words(I), words(J))
End If
End If
End If
Next J
Next I
End Sub

Private Sub Swap(var1 As String, var2 As String)
Dim x As String
x = var1
var1 = var2
var2 = x
End Sub

1. type the argument:
Public Function BWT_Encode(CodeString As String) As String

2. re-dim the array:
Length = Len(CodeString)
ReDim TheStrings(Length)
Result = ""

3. Do you want the array to start at 0 or 1?
--
Gary''s Student - gsnu200803


"Magius96" wrote:

I can't figure out what's wrong with this function. When I try to call it
from Excel it gives me the "#VALUE!" error.

This function should be an implementation of the Burrows-Wheeler
Transformation. I have gone through the code with a fine tooth comb, but
can't find the problem myself.

### START CODE ###

Public Function BWT_Encode(CodeString) As String
Dim TheStrings() As String
Dim Result As String
Dim Length As Long
Dim A As Long

Length = Len(CodeString)
Result = ""

' First build the string table
TheStrings(1) = CodeString
For A = 2 To Length
TheStrings(A) = Right(TheStrings(A - 1), 1) & Left(TheStrings(A - 1),
Length - 1)
Next A

' Now sort the array alphabetically
Call StrSort(TheStrings(), True, True)

' Now get last letter
For A = 1 To Length
Result = Result & Right(TheStrings(A), 1)
Next A
BWT_Encode = Result
End Function

### END CODE ###

1. type the argument:
Public Function BWT_Encode(CodeString As String) As String

I can't believe I forgot the type declaration!

2. re-dim the array:
Length = Len(CodeString)
ReDim TheStrings(Length)
Result = ""

Once again, how could I forget this? It's working beautifully after doing
this step.

3. Do you want the array to start at 0 or 1?

I assumed that VB string arrays start at 1 instead of 0, which seems to be
the way it's handled since the function is now giving the proper output of
"@nJ#osa" when fed the input of "#Jason@"

Thanks for your help, now that I've got the Encoding working, I'll work on
the decoding. (And hopefully remember to avoid the silly things I forgot
this time.)

I assumed that VB string arrays start at 1 instead of 0

Bad assumption. The Option Base compiler directive can be used to set the
default LBound for all arrays that do not explicitly set an LBound.

Option Base 0 ' arrays start at base 0
Option Base 1 ' array start at base 1

If Option Base is not specified for the module, VBA defaults to 0. Some
objects, though, always start arrays with 1. Properly written code should be
base agnostic. Using LBound and UBound, the code should work properly
regardless of the presence and value of any Option Base statement.

Note that Option Base affects an entire module only, not just a procedure,
and not the entire project. String functions (e.g., Len, Mid, InStr, etc)
are always 1 based, not 0 based.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"Magius96" wrote in message
...
1. type the argument:
Public Function BWT_Encode(CodeString As String) As String

I can't believe I forgot the type declaration!

2. re-dim the array:
Length = Len(CodeString)
ReDim TheStrings(Length)
Result = ""

Once again, how could I forget this? It's working beautifully after doing
this step.

3. Do you want the array to start at 0 or 1?

I assumed that VB string arrays start at 1 instead of 0, which seems to be
the way it's handled since the function is now giving the proper output of
"@nJ#osa" when fed the input of "#Jason@"

Thanks for your help, now that I've got the Encoding working, I'll work on
the decoding. (And hopefully remember to avoid the silly things I forgot
this time.)