Posts

Problems and Their Solutions!

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

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.

Image
  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 b

ActiveObjects Turn back to me!

Image
#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 cal

Consolidating Multiple Sheets into ONE New Sheet using VBA.

Image

Delete Target Sheet Automatically if the Target Sheet is Unprotected!

Friend asked me a little query. he wants VBA to delete his Target Sheet automatically if the Target Sheet is Unprotected! Now, that's something easy but it's not! Not at all! unless you know the best way to achieve that task using an available property of the Sheet Object. There's a Sheet property called,."ProtectContents" Here's how you can perform.. Step 1: Choose TargetSheet Step 2: Open Code Editor for that Target Sheet Object from VBE. Step 3: Paste Below Code Private Sub Worksheet_SelectionChange(ByVal Target As Range)     On Error Resume Next     Application.DisplayAlerts = False     If Sheets("SheetName").ProtectContents = False Then         Sheets("SheetName").Delete     End If     Application.DisplayAlerts = True     On Error GoTo 0 End Sub That's it! Now if someone will illegally try to crack your Target Sheet password from your workbook then after any cell selection by the user will trigger the abov

Questions Should be asked by Excel VBA Developer to their Clients

Image
#Discussions Questions Should be asked by Excel VBA Developer to their Clients before initiating the project. We all are Learning lots of things as freelancers every day. Especially Non Technical part of the project. And you know it's the most important part of the project where we are getting to know everything before we begin. I'm inviting you here to take part in this post. I hope this post will help each member of the group in their freelancing carrier. General Category's Questions! What does your business actually do? (Explain in Short!) Why You want this application? What current setup you have to simplify the process? Why do you want to develop this application on Excel VBA Framework? Have you ever tried to research for the already available solutions? If Yes, then what makes you not to choose them for the process? Do you have your competitors? If Yes, then what they are using for the same process? Application-oriented questions