Archive

Posts Tagged ‘function’

Excel, VBA Codes and Formulas-1

March 8, 2020 7 comments

Learn To : In Excel – Drop Down List in a cell.

I start working on a project for a friend, he has some requests to be done on a MS-Excel, so I thought why not to write several posts about MS-Excel capabilities.

MS-Excel is a great application to be used, I will not write to praise the program, but will jump to coding. In Excel we can use dozens of built in ready to use tools and Formulas. Also MS-Excel has a very good, easy to use and learn programming language called VBA (Visual Basic for Application) which with simple code we can perform some tasks and create Menus, buttons and functions that runs in background.

Starting from this post we will use some built-in functions and VBA’s one that I am using in my Excel files.
So let’s say we have a table that has a column called “Pay-Method”, the value we save in this column always is one of three: “Cash, Card, Cheque” we don’t want the user to type it every time, but to select it from a list.

To do this we can use an static approach or a Dynamic approach.

Static Approach: if we have a specific answers that can’t be more like (Yes,No) or as our example above (Cash, Card, Cheque) or similar cases then we can use an static approach. So First Select First cell in the Table, from the menu we will select “Data” then “Data Validation” and select Data Validation.



Now,

  • From the Allow box, we select List.
  • Then in Source box, we need to type the items we want to appear in each cell as a drop-down list separated by a comma. Cash, Card, Cheque
  • We need to check [Ignore Blank and In-cell Dropdown] Then Click OK.

Now we will have a small arrow on the first cell in our Table, to copy the List to other Cells (Beneath) hold the small DOT and Drag it down.

And Now we are DONE, we just create a Dropdown List in all “Pay-Method” Column in the table..



Dynamic Approach: If we have a list of items and this list is growing (We may add more items ti it), such as Fruit list in a grocery, then we will use the Dynamic drop-down list.
In this case I prefer to use a new Excel Tab call it “Setting”, so we will create one. On that Tab create a Table call it “Fruit” and list all the fruits you want to be appear in the drop-down cell list (In this example I will list down F1, F2, F3, F4, F5), Select the table and from Menu select “Format as Table” and just chose any format you want. Here is a screen shot.

Here is a screen shot

Move the Mouse next to table head until you get small Arrow, and give it a name. as in here..



Now go-back to our main Table and using the same way we did in the Static Approach (from the menu we will select “Data” then “Data Validation” and select Data Validation.) AND

  • From the Allow box, we select List.
  • Then in Source box, we need to type the Name we select for our table “F-Table_1” for the fruit table
  • We need to check [Ignore Blank and In-cell Dropdown] Then Click OK.



Done … Now if we add new Fruit to the list “F6” it will appear in our Drop-Down cell list.

🙂 Have Fun ..



Follow me on Twitter..




By: Ali Radwani




Python: My Fake Data Generator P-1

December 11, 2019 Leave a comment


Learning : Python: Functions, Procedures and documentation
Subject: About fake data P-1

In the last post (Fake data-set) we play around with a library called “Faker” and we saw that we can call several functions to generate a fake data such as names, dresses, jobs and others. Once you use this you can figure out that a lot of data are random, some time they are random from a list or say a file. So as we are in learning sessions i thought it is a good idea if i start to write “My Fake Data Generator” functions, There is one thing that we (say I) have to consider; that’s the people behind “Faker library” are professionals and are a team not ONE person, there experience in writing codes and documenting thing are away better than whatever we will do,
BUT the goal of this task is to coding, coding and coding.

So what are the functions that we will try to write? Here we will list down the functions that we will work on, this list will grownup as we working so it is not limited to that ever written now. Also i will try to do documentation for the functions so later if we call help() function in Python and pass a function name, we will get something from there. So let’s start and see the list of functions we will work on.

Function Name Description
color To return a random color code in RGB or hex.
Date To return a random date.
Mobile To return a mobile number.
Country To return a random country name.
City To return a random City name.
ID To return 11 random dig as ID.

Done

So first one we will start with is “color”. Color function will return a value that present a color code, this number may be in decimal or hexadecimal, the function will take one argument (type) if type = d or D then the code will be in decimal, if type = h or H then the code will be in hexadecimal, if nothing passes then default will be hexadecimal.

First we need to import random

 # Generate FAKE color code.

def fcolor (t='h'):
    """
    This function named 'fcolor' will return a value that present a color code.
    Function will take one argument str (t), if t = 'd' or 'D' color code will be in RGB,
    if t = 'h' or 'H', color code will be in Hex,

    if nothing passes default color code will be Hex.

    Example:
        r,g,b = fcolor ('d')
        c = fcolor ('h') or  c = fcolor ()

    Argument: str : t

    return: ccode: list if RGB, str if Hex
    """
    ccode =[]

    r= random.randint (0,255)
    g= random.randint (0,255)
    b= random.randint (0,255)

    if t in ['d','D'] :
        ccode.append(r)
        ccode.append(g)
        ccode.append(b)
        return ccode
    else:
        # To convert the RGB color to Hex.

        for each in [r,g,b] :
            num = each
            cl=[]
            while num > 0 :
                hexr = num % 16
                if hexr < 10 :
                    cl.append(hexr)
                elif hexr == 10 :
                    cl.append('A')
                elif hexr == 11 :
                    cl.append('B')
                elif hexr == 12 :
                    cl.append('C')
                elif hexr == 13 :
                    cl.append('D')
                elif hexr == 14 :
                    cl.append('E')
                elif hexr == 15 :
                    cl.append('F')
                num = int(num / 16)

            cl.reverse()
            co=''
            for x in cl :
                co = str(co) + str(x)
            ccode.append(co)


        cc='#'

        for x in ccode :
            cc = str(cc) + str(x)
        ccode = cc

        while len(ccode)-1 < 6:
            ccode = ccode + '0'

        return ccode



 # Calling the function 3 times.
r,g,b = fcolor ('d')
print('  Color code as int RGB: ',r,g,b)
print('  Color code as list RGB:',fcolor ('d'))
print('  Color code as str Hex:',fcolor ())

[Output]:  

  Color code as int RGB:  47 202 248
  Color code as list RGB: [59, 132, 99]
  Color code as str Hex: #85B060


This is the fcolor “Fake color” function, next post will be about the random ID number and what if we want it to be meaningful.



To Download my Python code (.py) files Click-Here





Follow me on Twitter..




By: Ali Radwani