Skip to main content

Posts

How to use Excel Sum Function in VBA

Sum Function using VBA Excel
What would be the first thing that you will do if you want to check how to code Sum function in VBA?
Record a Macro, of course and boom,  code in VBA is ready and done.
Recording a Sum macro in Excel, will have a code like this one below:
 ActiveCell.FormulaR1C1 = “=SUM(RC[-9]:RC[-2])”
Now the question is how to interpret this formula?
What if the range change or the requirements need a different cells location?
Record again a new VBA? I don’t think it’s a good idea.
The formula that Excel uses when you record isn't that hard to interpret.
You just need to get the hang of it.
Let's interpret the above excel formula.
 ActiveCell.FormulaR1C1 = “=SUM(RC[-9]:RC[-2])”
If you will just do a basic formula function, you can just type:
=SUM(Start_Range:End_Range)
The R1C1 formula follows same idea.
So, =SUM(RC[-9] is the start range and the RC[-2] is the end range.
RC[-9] means 9 columns before the active cell.
RC[-2] means 2 columns before the active cell and i…

Basic network monitoring tools

Basic network, monitoring tools and troubleshooting on Windows.
For those who have just embark on the field of Information Technology and even for those who are eager to learn on how to troubleshoot or do some network basic monitoring, Windows  has a lot of built in command to do this basic tasks.
In a small office or even in a large environment, there will always come a time that you will be disconnected to the internet  or even at your own home.
Basic or let's say common problem is unable to print, unable to connect to network share, unable to connect to "Facebook", or unable to connect to Google to find some programming code. Basically cannot connect to any websites on the internet and other things that user should do at home but love to do it at work.
Before we discuss on how to use those basic tools, let's define and discuss about two important and basic configuration on a local computer or workstation.
In every computer or workstation that is connected to the i…

How to print double sided in Excel

VBA Duplex Printing in Excel 2010

In my previous post I have shown how to manually print duplex using Excel 2010.

Using VBA we can set the printer settings in duplex mode and print.

Microsoft engineers are kind enough to show some code on how we can achieve it.

The code sample on the links provided below is for Microsoft Word but it also works on Excel as well.

Here's the link: Set Duplex Printing via VBA

In the link above, there is an instruction on how to use the code.

Open Excel and click on Developer Tab. If Developer tab is not yet enable on your Excel.

The quick way to enter code is to right click on the name of the worksheet (like right click on "Sheet 1") and you will see "View Code".

Click on "View Code" and it will open up the Visual Basic editor.

The instruction on the Microsoft link above is to "Insert a Module".

Create or Insert a Module, then copy the code on the link above that starts with "Option Explicit

Public Type P…

Tutorial on Vlookup function for Excel

A quick notes on Vlookup Excel formula.

Vlookup formula is quite useful to find and locate data on Excel.

Table below will be used on how to demonstrate and use vlookup. But this basic idea is the same way you can apply even on a complicated or huge amount of excel data.

First we examine the parameters of Vlookup.

By typing Vlookup formula on Excel, Excel will prompt or display the parameters needed to complete the function.

Let's try to simplify or use lay man terms for the vlookup parameters.

In Excel 2007 that I'm using it displays these parameters below when I type Vlookup formula :

=Vlookup (look_up_value, table_array,col_index_num, [range_lookup])

The look_up_value is indeed the value that you're looking for or the data you're trying to find.

The table_array is the range of cells, or basically the whole cells or columns in which the data will be search.

The col_index_num, is the data that will be returned once the look_up_value matches any data define on th…

Launch Program with Admin Privileges using an elevated command prompt

Running Control Panel Applets with administrative privileges.
A quick notes on how to launch a control panel applet from a user with no Admin rights. Elevated command prompt, is running the command prompt with Administrative privileges.
In an Active Directory environment, it's quite a bad idea giving Domain Admin rights to any users or setting the domain user  account as a member of Local Administrator.
Why it is a bad idea? I guess  you know the downside of it.
Troubleshooting or doing some simple tasks  on AD  or domain environment, is quite a hassle.  If the active or current user does not have an Administrative privileges.
I bet if you like to do things, like installing or removing programs. It would be better if you will login with Administrator account, in such a way you can do things smoothly.
If you're not login as an Administrator then you have to bear the annoying pop up box that keep asking for an Admin password.
Well, of course there are a lot of ways to uninstall…

How DHCP works?

A quick notes on how DHCP process works. DHCP - an acronym for Dynamic Host Configuration Protocol

DHCP is one of the most important thing in a network infrastructure.
Why it is most important? Would you link to configure 30 or more computers with an static IP?

Or would you prefer,  just plugging  a network cable and that's it DHCP will do the rest.
If DHCP works as cool as that, why bother how it works on the background?
It's good to know how things works on the background, not just because you're preparing for some certification and you got to nail down that cert.

It still a very good thing to know how things are being done on the background.
Download Network Monitor Tools from Microsoft site on this link below:

http://www.microsoft.com/en-sg/download/details.aspx?id=4865

On start page of Network Monitor, click the Network Interface on which the packet will be sniff.

If the PC has only 1 NIC, it will be check by default. If PC has multiple NICs check the desired NIC.



Cli…

Basic Linux File Permissions

A good start in learning Linux is to understand how to setup file permissions.
Linux has this permissions: "No Access" or "No Permissions", "Execute" only, "Write" only, "Write and Execute", "Read" only, "Read and Execute", "Read and Write", and of course "Full Access" or "RWX"

Below is a table that lists on how permissions is being derive.
Permissions is converted from Octal to Decimal.

4 2 1 Equivalent Access RightEquivalent Decimal ValueWhy?000No Access (zero)0Zero001X (execute)10+0+1=1010W (write)20+1+0=2011W+X30+1+1=3100R (read)41+0+0=4101R+X51+0+1=5110R+W61+1+0=6111RWX (Full Rights)71+1+1=7
From the table above we can come up with this summary:

0 is no permissions, 1 is execute only, 2 is write only, 3 is write and execute, 4 is read only, 5 is read an execute, 6 is read and write and 7 is full access which hackers want to have.

To set file permissions we can use a "chmod&quo…