Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-01-2019, 06:30 AM   #1
Guus2005
AWF VIP
 
Guus2005's Avatar
 
Join Date: Jun 2007
Location: The Netherlands
Posts: 2,523
Thanks: 53
Thanked 85 Times in 78 Posts
Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about
Sort the DIM statements on length and alfabet

I wanted to sort the dim statements on length as part of my vb_Beautifier code: https://www.access-programmers.co.uk...d.php?t=210180
It can't be done they said.
This code fixes a problem that doesn't exist.

It is just a cosmetic fix. It runs some string manipulations on the clipboard.

This is what you do: copy the dim statements into the clipboard.
Code:
    Dim intElements As Integer
    Dim intX As Integer
    Dim arr As Variant
    Dim intPrefix As Integer
    Dim intAs As Integer
    Dim intLongeste As Integer
Run this command in the immediate window

DoFixDim


Paste the clipboard
Code:
    Dim arr         As Variant
    Dim intX        As Integer
    Dim intAs       As Integer
    Dim intPrefix   As Integer
    Dim intElements As Integer
    Dim intLongeste As Integer
Share & Enjoy!

Code:
Option Explicit

Public Sub DoFixDim()
'Run this sub in the immediate window.
'Sort dimensions in clipboard.

    Dim arr         As Variant
    Dim intX        As Integer
    Dim intAs       As Integer
    Dim intPrefix   As Integer
    Dim intLangste  As Integer
    Dim intElements As Integer
    
    arr = ClipToArray() 'From clipboard to array.
    
    intElements = UBound(arr, 1)
    
    'Add variable length, separated by |
    For intX = LBound(arr) To intElements
        If Len(Trim(arr(intX))) > 0 Then
            arr(intX) = Format(Len(Split(Trim(arr(intX)), " ")(1)), "0#") & "|" & Trim(arr(intX)) 'Store length of variable as a prefix
        End If
    Next intX
    
    BubbleSort arr                                    'Sort the array
    intLangste = Int(Split(arr(UBound(arr)), "|")(0)) 'What is the longest variable?
    StripSpaces arr                                   'Remove double spaces using regex

    For intX = LBound(arr) To intElements
        If Len(Trim(arr(intX))) > 0 Then
            intAs = InStr(1, Split(Trim(arr(intX)), "|")(1), " As ")
            If intAs = 0 Then
                'Remove length from string (first element) there is no type
                arr(intX) = Trim(Split(arr(intX), "|")(1))
            Else
                'Remove length from string (first element) add spaces before 'As'
                arr(intX) = vbTab & Trim(Left$(Split(arr(intX), "|")(1), intAs)) & Space(intLangste - Int(Split(Trim(arr(intX)), "|")(0))) & Mid$(Split(Trim(arr(intX)), "|")(1), intAs)
            End If
        End If
    Next intX
    
    'Array to clipboard.
    ArrayToClip arr
    
End Sub

Public Function RegExpReplace(ByVal strWhichString As String, ByVal strPattern As String, ByVal strReplaceWith As String, Optional ByVal IsGlobal As Boolean = True, Optional ByVal IsCaseSensitive As Boolean = True) As String
'Thanks to arnelgp @AWF
    With CreateObject("vbscript.regexp")
        .Global = IsGlobal
        .Pattern = strPattern
        .IgnoreCase = Not IsCaseSensitive
        RegExpReplace = .Replace(strWhichString, strReplaceWith)
    End With
    
End Function

Private Sub ArrayToClip(arr As Variant)
'must add the reference “Microsoft Forms 2.0 Object Library” or FM20.DLL

    Dim objData As New MSForms.DataObject
    
    objData.SetText Join(arr, vbCrLf)
    objData.PutInClipboard
    
End Sub

Private Sub BubbleSort(arr)
'Small dataset, Bubblesort will suffice

    Dim i       As Long
    Dim j       As Long
    Dim lngMax  As Long
    Dim lngMin  As Long
    Dim strTemp As String

    lngMin = LBound(arr)
    lngMax = UBound(arr)

    For i = lngMin To lngMax - 1
        For j = i + 1 To lngMax
            If arr(i) > arr(j) Then
                strTemp = arr(i)
                arr(i) = arr(j)
                arr(j) = strTemp
            End If
        Next j
    Next i
  
End Sub

Private Function ClipToArray() As Variant
'must add the reference “Microsoft Forms 2.0 Object Library” or FM20.DLL
    Dim clip As New MSForms.DataObject
    Dim lines As String
    
    clip.GetFromClipboard
    lines = clip.GetText
    lines = Replace(lines, vbCr, "")
    ClipToArray = Split(lines, vbLf)
    
End Function

Private Function StripSpaces(ByRef arr As Variant)

    Dim intI    As Integer
    Dim strText As String
    
    For intI = LBound(arr) To UBound(arr)
        arr(intI) = RegExpReplace(arr(intI), "[\s]{2,}", " ")
    Next intI

End Function

Guus2005 is offline   Reply With Quote
The Following 2 Users Say Thank You to Guus2005 For This Useful Post:
Minty (10-04-2019), NauticalGent (10-01-2019)
Old 10-01-2019, 07:09 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,370
Thanks: 0
Thanked 743 Times in 728 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Sort the DIM statements on length and alfabet

who is that OCD to need that?
Ranman256 is offline   Reply With Quote
Old 10-01-2019, 07:26 AM   #3
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,435
Thanks: 165
Thanked 1,738 Times in 1,707 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Sort the DIM statements on length and alfabet

Neat - the old version of VBA indenter did this (not the sorting) but the proper indentation, and I loved it, it doesn't work with 64 Bit, unfortunately.

MZ tools indenter doesn't line it up as nicely.

(Like the labels on the tin cans in the larder)

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 10-01-2019, 07:39 AM   #4
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,238
Thanks: 93
Thanked 2,026 Times in 1,973 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Sort the DIM statements on length and alfabet

MZTools will allow you to sort your DIM statements alphabetically.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 10-01-2019 at 08:53 AM.
jdraw is offline   Reply With Quote
Old 10-03-2019, 04:24 AM   #5
Guus2005
AWF VIP
 
Guus2005's Avatar
 
Join Date: Jun 2007
Location: The Netherlands
Posts: 2,523
Thanks: 53
Thanked 85 Times in 78 Posts
Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about
Re: Sort the DIM statements on length and alfabet

Yes, it requires some level of OCD. I know. But i like my code neat and tidy.

I have had contact with Carlos Quintero (creator of MZTools) about this feature or the ability to run code on the clipboard or under a shortcut. He told me that it is not going to happen any time soon, so i made this.

Try to see the trees in the forrest and you might like it

Guus2005 is offline   Reply With Quote
Reply

Tags
array , bubblesort , clipboard

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] SQL View (with sort) as Linked Table looses sort on Access side - ODBC Rx_ SQL Server 5 01-12-2015 09:43 AM
how to sort datasheet and how to sort tables-what is the difference please florida2001 General 2 04-07-2014 04:01 AM
IIF Statements maybe IIFElse Statements pbate Queries 3 08-20-2012 12:15 PM
Nested if statements in select case statements Mcgrco Modules & VBA 2 02-19-2003 01:36 AM
how do i sort my data by a report field (override query sort)? starfailure Reports 2 01-04-2002 08:10 PM




All times are GMT -8. The time now is 11:31 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World