IsValidPasswordString Function

'Following function will verify if the password string contains following characters or not?

Rem : List of Characters Group - ASCII
Rem : Group 1 : A-Z : 65-90
Rem : Group 2 : a-z : 97-122
Rem : Group 3 : 0-9 : 48-57
Rem : Group 4 : Special Characters Group 1 : 33-47
Rem : Group 5 : Special Characters Group 2 : 58-64
Rem : Group 6 : Special Characters Group 3 : 91-96
Rem : Group 7 : Special Characters Group 4 : 123-126

'Criterias of checking
'Minimum 8 Length
'At least One Capital
'At least One Small Character
'At least One Numeric
'At least One Special Character

Public Function IsValidPasswordString(ByVal passwordString As String) As Boolean
    
    'Variable declarations
    Dim txt                     As String
    Dim passwordCharacters()    As String
    Dim passwordCharacter       As Variant
    Dim testCapital             As Boolean
    Dim testLower               As Boolean
    Dim testNumber              As Boolean
    Dim testSpecialCharacter    As Boolean
    
    'Check for the Minimum Length Criteria
    If VBA.Len(passwordString) >= 8 Then
        IsValidPasswordString = True
    Else
        IsValidPasswordString = False
        Exit Function
    End If
    
    'Check rest of the conditions
    
    'Get characters in array
    txt = VBA.StrConv(passwordString, vbUnicode)
    passwordCharacters = VBA.Split(VBA.Left(txt, Len(txt) - 1), VBA.Chr$(0))
    
    'Get testified
    For Each passwordCharacter In passwordCharacters
        Select Case VBA.AscW(passwordCharacter)
            Case 65 To 90                                   'A-Z : 65-90
                testCapital = True
            Case 97 To 122                                  'a-z
                testLower = True
            Case 48 To 57                                   '0-9
                testNumber = True
            Case 33 To 47, 58 To 64, 91 To 96, 123 To 126   'Special Characters
                testSpecialCharacter = True
        End Select
    Next passwordCharacter
    
    'final conclusion
    If (testCapital And testLower And testNumber And testSpecialCharacter) Then
        IsValidPasswordString = True
    Else
        IsValidPasswordString = False
    End If
    
End Function

The benefit of Object-Oriented Programming (Class Modules) in VBA.

Many experienced professional VBA developers in the fraternity keep discussing how is OOP going to add value in writing programming? And the supporters of OOP, like me, always give them ideas on how OOP will create reusability of your code, how it will protect (encapsulate) your code from the outer world, etc., etc.

But today I want to push a very clear notion in support of object-oriented programming in VBA.

OOP will let your IMPERATIVE Programming into DECLARATIVE

IMPERATIVE means: To tell computer What to do? and How to do it?

DECLARATIVE means: to tell computer only What To Do!

Once we have designed a class for general methods and functions, our coding in General Module will be DECLARATIVE.

DECLARATIVE programming is fun, readable and neat & clean.

Hope this will help VBA Developers to get their self, practice more seriously in the OOP domain.

#kamalam

Problems and Their Solutions!

હું જ્યારે સેકેન્ડરી સ્કૂલમાં હતો, ત્યારે કમ્પ્યુટર્સ અમારા માટે એક જાદુઈ વસ્તુ હતી. હું વાત કરું છું વર્ષ 2001ની આસપાસ. હું ત્યારે ધોરણ સાતમાં અભ્યાસ કરતો હતો. મેં તરત જ કમ્પ્યુટરનાં ટીચર સાથે મિત્રતા કરી લીધી અને તેમણે મને તેમનો આસિસ્ટન્ટ બનાવી લીધો. એ સમયમાં જ ટીચરે મને માઈક્રોસોફ્ટ એક્સલ એપ્લિકેશન સાથે ઇન્ડ્રયુડુઝ કરાવ્યો. અને એમણે મને જયારે સમ ફન્કશન અને ઇફ ફંક્શન સાથે અવગત કરાવ્યો ત્યારે એ રાત્રે તો મને નીંદર જ ન આવી. મારુ મગજ ત્યારે જ દોડવા લાગી ગયું હતું. પછી મેં વધુ ને વધુ જાણવાનો પ્રયત્ન કર્યો અને મારુ પ્રથમ નાનું એપ્લિકેશન બનાવ્યું. એ હતું વિદ્યાર્થીઓના માર્ક્સને ગણી ને તેના પર્સેન્ટેજ બતાવતી શીટ.

એ સમયે એ મારા માટે બહુ જ મોટી વાત હતી. એ માર્કશીટ બનાવવા માટે મેં એક જ એક્સલ શીટનો ઉપયોગ કર્યો હતો. મેં મારા ટીચર ને એ શીટ બતાવી અને તેઓ ખુશ થઇ ગયા પણ એક જ શીટ ની અંદર બધું હતું એટલે અસ્તવ્યસ્ત લાગતું હતું એટલે ટીચરે મને તરત જ કહ્યું કે, "તે આ બધું એક જ શીટ માં કેમ બનાવ્યું છે?"
એ પછી ટીચરે બીજી શીટ ઇન્સર્ટ કરી અને મારી દુનિયા ફરીથી બદલી ગઈ. હું જાણતો ન હતો કે હું બીજી શીટ પણ ઇન્સર્ટ કરી શકું છું.

 
જીવન પણ કઈંક આવું જ હોય છે. આપણે બસ લાગેલા રહીએ છીએ કે એક જ પ્રકારની સમસ્યા માટે એક જ પ્રકારનાં સમાધાન સાથે. આપણે જીવનના બીજા આયામો તરફ તો નજર કરતા જ નથી અથવા તો જાણવાનો પણ પ્રયત્ન કરતા નથી.

 
જીવનના નવા આયામો કદાચ એક ક્લિક પર સામે નથી આવતા પણ એ હોય છે એ નક્કી.

#Kamalam

Recipe of Smart User interfaces on Excel VBA

When we talk about Developing Apps on Excel VBA, we have two straight options for designing the user interface of the app.

1. Worksheet based
2. UserForm based

Now, UserForm based user interface is common to understand but in the last few months, I understood the potential of the NAMED RANGES to build dynamic User interface on worksheets. Named Ranges are very helpful when user interfaces are changing frequently. when the named range is combining with conditional formattings, it creates magic! you can loop through all the named ranges and validate all name ranges based on the conditional formatting applied to them. It's blessings.

I'm now more confident building user interfaces on worksheets just like user forms.

Named Ranges + Conditional Formattings = Recipe of Smart User interfaces on Excel VBA.

#excel #vba #userinterface #userforms #worksheets #appdevelopment

#Kamalam

VBA or Office.JS! The dialogue of dilemma.

 

I have been a part of one Office.JS WhatsApp group for a long and it's not an ordinary group at all! I found a bunch of good excel professionals there but still, no conversation had happened in that group since it's been created. 

I know it's too early to decide or compare the interests of people for Office Js but it's also a reality that we do not have a proper driving force that can lead us to do some learning and experiment with the Office JS framework. We learned Excel and VBA just by doing some random stuff here and there and developed interests too. Even the freshers of this phase, tending to learn VBA because it's easier to understand and write. 

Python is still evolving to compete with VBA in all areas. I do not know whether people will accept Python as an interface for office automation or not! Most data scientists are learning python these days but excel is not only used for data science alone. Excel with VBA making that framework easy to build various business solutions prototypes, report automation, and somehow ERPs too! So, python has still a very long to chase to compete with Excel+VBA in all areas. 

If Office Js is the output of having thought of creating macros for a universal platform then they should have adopted & implemented C# and ASP.NET framework for the office automation scripting. It is cross-platform, adaptable, modern and most importantly it is user-friendlier than office JS in the context of coding! I would say, with just a little effort, current VBA professionals would have had become a part of that framework but I do not know about the likable future of Office JS. Please comment on your opinion here as well. 

are you guys a part of any "ACTIVE" group on Office Js? Please let us know. So we could join them as well and learn to support the future!

#OfficeJS #excel #vba #office365 #javascript #python #Csharp

ActiveObjects Turn back to me!

#excelvba #vba #excel

I was in the huge misunderstanding that ActiveSheet, ActiveCell objects are created normally by the recorded Marcos and so, in the dynamic programming/automation we should avoid using that because of its ACTIVE properties. I followed that for a long but today when I was working on creating a simple customized TreeView Tool for worksheet creating out of checkboxes, I came into the situation where I have to use Application. Caller property so I can determine dynamically what object has called the central module to apply further changes. We know that CheckBoxes are the Worksheet objects or we can say that the parent object of Checkbox control is the worksheet. I further noticed that, if I have to use the same TreeView tool across multiple sheets then my code should be worksheet independent. So to achieve that dynamically, I fall back to my own discarded thoughts which are to avoid ActiveObjects.

I have to use the ActiveSheet property to determine which object has called the module! Such uncertainty. But I learned a lot today.

Share your thoughts on using ActiveObjects as well.

Regards

Kamal.

Consolidating Multiple Sheets into ONE New Sheet using VBA.