Replies

Here’s a Zip file containing a VB project that has a module that sorts 2 dimensional arrays using up to 3 keys. The module contains documentation as to how to call the subroutine.

I have tested this as much as I can but I can’t absolutely guarantee that it is completely bug free. Please make sure you test it against your data before implementing it.

If anyone has any questions, suggestions fit to print, mods or bugs please post to this thread and I will try to respond.

Regards,

Kevin Bell

Thanks for the posts. I worked around the error just as you both said but it’s still anoying to have to code differently because VB/VBA doesn’t terminate the If.
All the other languages I have used, either on the PC or on various other computers have terminated an If statement as soon as it becomes False. I do believe that this is considered the ‘norm’. I’ll have to ask Norm and see what He says.

You live and learn.

Cheers,

Kevin Bell

Kev,

Try this:

boolFileInUse = True
On error goto FileInUse
Open “The file you want to open” for input lock read as 1
on error goto 0
close #1
boolFileInUse=false
FileInUse:

etc.

Cheers,

Kevin Bell

Integers are actually stored that way on PCs. They will retrieve correctly using a GET.

I assume you are transferring the file to a non-PC computer and that’s why you want them to be changed.

This will do it.

Dim myInteger as Integer
Dim myByte as Byte

myByte=myInteger / 256
Put #?, , myByte
myByte = myInteger And 255
Put #?, , myByte

Hope this helps.

Kevin Bell

Interesting,

I must confess to not using the WordBasic.SortArray function. I have a horrible feeling that MS will just say, ‘WordBasic functions are no longer supported’, one day. In cases like yours, I would modify the Quicksort routine from the code librarian to add the additional dimensions, but that’s just me. I will definitely look at Romke Solaat’s site.

As for a bubble sort being too slow, that depends on what you are actually doing. If you are adding another element to an already sorted array and re-sorting, bubble sorts can be faster than other sorts as the new element will float to its correct position and the comparisions only performed once.

As an aside Kevin. Another of your posts asks whether or not you can find out how many dimensions an array has. The sort class from Romke must be able to do that or how can he determine whether or not the .SortColumn property is correct, or indeed know how many columns there are to sort?

Keep the faith guys.

Regards,

Kevin Bell

Happy New Year everybody.

Sortarray does that if the array is type String. If you want to sort numeric string data then it must be normalised by adding as many leading zeros as are necessary to make the numbers the same length, ie, in a population where the maximum numeric value is 10000, 1 must be 00001 for things to sort correctly.

If you declare the array as Variant and populate it with Integers or any other kind on numeric data, then Sortarray will sort it correctly.

Kevin Bell

Erik,

Here’s a version of a simple Encryption and Decryption that is more akin to what you are looking for. I’m sorry the attachment is a Word document and not a spreadsheet. I realised that it was Excel you were talking about only after I had finished it. The code will work in Excel as there are no specific references to Word.

Regards,

Kevin Bell

Here is a very simple Encryption/Decryption routine that uses XOR. In the Function pick a number that you want to use to scramble the text.

The beauty of using XOR is that the same routine is used to Encrypt and Decrypt.

Calling convention:

Dim strEncryptedDecrypted as String
Dim strStringToEncryptOrDecrypt

strStringToEncryptOrDecrypt=”Something to Encrypt”
strEncryptedDecrypted=EncryptDecrypt(strStringToEncryptOrDecrypt) ‘ Encrypt

strStringToEncryptOrDecrypt=SomethingAlreadyEncrypted
strEncryptedDecrypted=EncryptDecrypt(strStringToEncryptOrDecrypt) ‘ Decrypt

Function EncryptDecrypt(strIn As String) As String

Const XOR_VALUE As Byte = 123 ‘ Pick a value here between 1 and 255

Dim i As Integer
Dim strOut As String

If Trim(strIn) = “” Then
EncryptDecrypt = “”
Exit Function
End If

For i = 1 To Len(strIn)
strOut = strOut & Chr(Asc(Mid(strIn, i, 1)) Xor XOR_VALUE)
Next i

EncryptDecrypt = strOut

End Function

As I say, this is a very simple function and not particularly secure but I hope it helps.

Regards,

Kevin Bell

John,

I can’t exactly duplicate your problem but how about this. Map the shared directory to a drive letter, say X, on your local computer and then use ChDrive “X:” This works for me.

Regards,

Kevin Bell

I take it that you want to display the euro symbol in front of currency numbers. The currency symbol is part of Window’s regional settings.

In your regional settings, select the currency tab (where this is varies with the version of Windows you are running). Select the euro symbol as the currency symbol.

Then in Excel, whenever you format a cell as currency it will display the euro symbol. At least that’s what it does on my system.

Regards,

Kevin Bell

Thanks Klaus. That’s what I ended up doing.

I still think there should be an easier way and I’m sure that I remember way back in the mists of time a WordBasic function or property that returned the length in points.

Looks like another ‘feature’ that MS decided wasn’t neccessary.

Regards and thanks again.

Kevin Bell

As an aside to this thread but a related topic. Does anybody know how to invoke the built-in Graphics Converters in Office from VB or VBA?

Just my devious mind I suppose, I’m thinking of how to go the other way, from .BMP to other graphics formats without resorting to third party software. I know there is a lot of freeware out there that’ll probably do the trick but Enquiring Minds Just Have To Know.

Regards,

Kevin Bell

You are more than welcome Chris. Can you tell my boss that the code ran first time? It’ll give him the heart attack he deserves. LOL

Regards,

Kevin Bell

You can do this using an Image control and the LoadPicture and SavePicture statements.

I put together this quick and dirty.

Add a Userform with an Image control and a CommandButton.

Then:

Private Sub CommandButton1_Click()
Dim myfile As String
Dim mybmpfile As String
Dim i As Integer

Const pic_dir As String = “C:Documents and SettingsAdministratorMy DocumentsMy PicturesTest”
Const bmp_dir As String = “C:Documents and SettingsAdministratorMy DocumentsMy PicturesBMPS”

myfile = Dir(pic_dir & “*.*”)

While myfile “”
i = InStr(1, myfile, “.”)
mybmpfile = Left(myfile, i – 1)
myfile = pic_dir & myfile
Image1.Picture = LoadPicture(myfile)
SavePicture Image1.Picture, bmp_dir & mybmpfile & “.bmp”
myfile = Dir()
Wend

End Sub

The test directory contains a few jpg, gif and bmp files.

Hope this gives you a starting point. Note that I assumed that the file name contains only one period (.).

Regards,

Kevin Bell

We use Acrobat/Distiller to automatically produce our price lists every month.

Basically the sequence is:

1. Define a Postscript printer and set it to ‘Print to File’. It can be any device. We use an HP LaserJet 8100 PS. We don’t actually have one but after experimenting I found this to generate .ps files that Distiller really likes.

2. Set up a folder for Distiller. Then set Distiller to ‘watch’ this folder. It will create two sub-folders, In and Out. Let Distiller run all the time.

3. In your code, print the document you want converted to a PDF to a file with a .ps extension in the ‘In’ folder using the postscript printer. When the file is printed Distiller will pick up the .ps file and generate a PDF file of the same name in the ‘Out’ folder.

I hope this at least gives you a basis to work with. I can’t post the code we use due to company policies.

Regards,

Kevin Bell