Computer Forensics

If you've been using Excel you'll know that macros are a great way to be more productive by automating repeated tasks. But to get the best out of Excel you need to know a little about the VBA language that enables you to edit and improve on macros and write your own routines.

This article looks at five simple ways to work with data and how it might be applied in your daily work with Excel.

1. The Do Loop Command

In programming, a common task is to repeat an operation until a certain condition is encountered or a series of changes are completed. Here's a simple example:

There are a few different varieties of the command; for example you can include an exit command within the loop when a condition is encountered.

An example might be to generate a list of 10 unique random numbers and exit the loop once the 10 numbers have been listed.

A word of warning about the do loop command; it's easy to overlook or program the condition status incorrectly and set up an endless loop which will cause your macro to run forever!

2. Searching Within A Text String To Find Some Other Text With Instr

If you're searching for a particular value in a worksheet sometimes you might want to see if your search text is partially contained in a cell. Here's the basic syntax:

The function inStr - you can remember it by thinking "in string" - tells you at what character number the search text starts in the target text; if it's greater than zero then you've found a match.

Alternatively, you can use the inStr command as a Boolean (true/false) value:

An example might be that you're searching for all the cell values that are "Las Vegas" but you're concerned some might be entered as "Los Vegas". You can use the inStr function to return the near matches:

3. Returning Part Of A Text String With Mid

Sometimes you'll just want to use part of a text value and discard the rest of the string.

The way to remember this function is "mid-string" - you're creating a new text value from mid-way through an old string and you can use other programming techniques to determine the value for first and last.

This example looks at what might be a telephone number and removes the area code:

4. Using The If Then Command To Determine An Action

A lot of programming is about making decisions within your code and the if then command is the most commonly used:

This code snippet is part of a looping routine that is searching though a column of phone numbers and stops when it finds an area code of "(06)"

5. Using For Next To Run A Series Of Commands

Similar to the do loop command for next is generally used when your code is able to act as a counter and exit when it reaches the upper limit:

Normally, you'd use the for next command when the value of the counter may determine the action within the loop. Here's a code example which examines a range determined by the first and last rows you specify.

Summary

In this article we've covered some of the basic VBA techniques and functions.

Sometimes there are several techniques available and it can be difficult to know which one to use. While it's tempting to say which ever one works is the best, a good rule of thumb is to use the technique which is most easily understandable after the code has been written.

Andy L Gibson is a former Web Site programmer rediscovering his interest in Excel applications for small business. His blog at http://solutions4business.wordpress.com/ holds the VBA code for all of his articles on Excel and is available for FREE download. He is developing an Excel application which will contain working examples of VBA methods and solutions.

View the original article here

Excel users will often need to open a text file and convert the information into a spreadsheet format. For example data from another software application, such as a PDF text file might not be structured correctly to be read directly into Excel.

This article will show you how to write VBA code to open the text file and read the contents into a text string. If necessary you can then restructure the data so that it can be read in a spreadsheet format.

Using The VBA File System Object

Before accessing the text file you'll need to set up a reference enabling access to the file system.You do this by clicking the tools tab in the VBA code window and selecting the Microsoft Scripting Runtime library.

Our code will open the file, and read the contents line by line into a text string. What you do with the file contents is up to you, and this article will just focus on opening and reading the file.

We'll assume you have a file in a folder called "files" which is a sub-folder beneath the directory holding your main Excel file.

First, we'll set up variables to hold the file name, the output from the file and an object which will open the file for us.

Next, we define the file name and open it.

With the file open, we loop through it line by line, adding the contents to a text string before closing the file.

tmpText = tmpText & fso.ReadLine & "|"
Loop
fso.Close

We've added the pipe delimiter so that we can convert the text string into an array. You could just as easily write each line to an active sheet - it all depends on what you're trying to do.

Now we can convert the text string to an array and write each item to an active sheet. The advantage in using an array is that you can work with the contents of each line before adding it to the sheet.

For x = 0 To UBound(tmpText)

ActiveCell.Offset(x, 0).Value = tmpText(x)
Next

The difficulty is sometimes working out how to manipulate the text into a usable Excel format, but at least now you're able to access the data.

Summary
Opening and reading text files is a relatively straight-forward process but a good habit to get into is to build your own VBA code library so you don't need to remember the code and can concentrate instead on solving your own particular business problem.

Andy L Gibson is a former Web Site programmer rediscovering his interest in Excel applications for small business. His blog at http://solutions4business.wordpress.com/ holds the VBA code for all of his articles on Excel and is available for FREE download. He is developing an Excel application which will contain working examples of VBA methods and solutions.

View the original article here

The Problem:

WordPress is notorious for "database bloat", in particular the "wp-options" table. This is where WordPress (WP) stores all info for plugins and themes, etc.

There are built-in functions for storing data in this table, so a lot of developers use it to store data that really should be in a separate table.

Also, most plugins don't clean up after themselves when you delete them.

To make things worse, WordPress stores a draft copy of EVERY post revision you make. This results in bloating of the "wp-posts" table.

Some Culprits:

I've come across TWO plugins that massively contribute to bloated wp-options tables:

Don't get me wrong, FWP is a fantastic plugin, BUT it stores "transient" records in wp-options, and then never cleans them up. Artiss does something similar.

PHASE 1: Database Cleanup.

A Solution:

There's a plugin called "Clean Options", which scans for wp-options records which are never referenced by your WP installation. It calls these "orphans", and you can tell it to remove them for you. Be careful, though: take a database backup before you delete ANYTHING. Select any options belonging to plugins you don't have installed any more. Be careful!

You should also be able to delete anything which starts with "_transient_".

A fly in the ointment:

If you have a wp-options table with 30000 records in it, then "Clean Options" isn't going to be able to cope. It will freeze-up. You won't be able to select records to delete.

Drastic Measures:

So where does that leave us? Manually deleting records from the wp-options table. Yes. That's scary. But it's the only way.

MAKE A BACKUP OF YOUR WP-OPTIONS TABLE

Use phpmyadmin to directly access your database, select the wp-options table, and use the "export" option to make a backup to your local machine.

Now execute the following sql statements:

select * from wp-options where wp-name like '_transient_%'

This will return all the transient records with names starting with "_transient_", and give you a tally of how many there are.

Check these records to make sure there's nothing else in there before you do anything drastic.

Like this:

delete from wp-options where wp-name like '_transient_%'

This deletes ALL records starting with "_transient_". You did check the results of the select statement, above, didn't you? No? That's OK, you DID make a backup copy. Didn't you?

You have just deleted (possibly) thousands of wp-options records (my record is 28000). Now, at this point, one of three things will happen:

If (3) happens, try adding "/license.txt" to the end of your site URL. If it displays the WP license agreement, your hosting is OK, but WP is having a breakdown.

Fix this by accessing the "active plugins" record in wp-options, (usually on page 2) and copying the contents somewhere safe. Now delete the contents (NOT the record itself!)

Now try accessing your site again. You should find that you can get into the admin panel and re-activate the plugins one-by-one until everything's back up.

(or paste the copy you made earlier back in)

Phase 2: Database Optimization.

You should now have a nice slimline wp-options table.

Next, we need to "optimize" the database.

By this, we mean delete all post revisions (you don't need them. You didn't know you HAD them, right?), spam comments, etc.

We also need to run standard database optimization routines on the core WP tables, remove table overheads etc..

Make a COMPLETE database backup before you start messing with this. (Same routine as before, but for ALL tables together).

We use the "wp-optimize" plugin, but there are others.

WP-Optimize has checkboxes for deleting post revisions, spam, and optimization.

We always run the optimization option ON IT'S OWN.. You DO NOT want a timeout at this point!

That's it. Hopefully nothing went horribly wrong.

If it did, it's YOUR problem. We told you to make backups several times.

View the original article here

View the original article here

Computing Languages Overview

Computer languages can be categorized into three types.

Machine Language

It is a machine language of 0's and 1's and is the most flexible of all languages, but machine dependent and not portable

Assembly Language

It is a low level language of 0's and 1's but mnemonic codes are assigned for ease of learning.

Like cp=10010000 to copy data.

Structured Languages

The Structured languages use three structures for writing programming code.

Every modern language uses above three structures with difference in syntax.

They are near to human language instructions usually in English.

Sequence Structure

In sequence structure the code lines are executed or run in sequence or order line by line.

For example BASIC Language code to display name on screen.

CLS

Print "Please Enter your name:" Input $name

Print "My Name is " $name

End

Loop Structure

In Loop Structure the program lines are executed for specified times until a condition is true

CLS

For X=1 to 100

Print X

X=X+1

End For

End

The above code will be executed until X variable value is greater than 100.

Here FOR structure is used some languages uses DO WHILE or WHILE structure for loop.

Decision Structure

Decision Structure is for decision making.

For example the below code will take input from screen and display information upon input analysis.

Here IF structure is used

CLS

Print "Please your marital status S for single and M for married "

input $STATUS

IF $STATUS="S" THEN Print "Single"

Else

IF $STATUS="M" THEN PRINT "Married"

Else

Print "Not Valid Entry"

End

There are many structured languages few of them are:

BASIC

It stands for Beginners all purposes instructions code language.

It is English like language for easy understanding of beginners for various tasks like gaming, business and mathematical problems.

Pascal

Used for engineering and scientific calculations

FORTRAN

Stands for Formula Translation used for engineering and scientific calculations

COBOL

Stands for Common Business Oriented Language used for business applications

C Language

Developed by Bell lab used for system software development

C++

An extension of C Language uses OOP or object oriented programming for Application and system software.

OOP technology uses class structures for development as it uses existing codes/classes for re-use rather than to develop new one to reduce development time. Objects have properties and methods to handle them.

Visual BASIC

Extension of BASIC Language uses OOP designed for application development.

Java

It is like C++ designed to develop web page development and make them interactive through database linkages.

HTML

Stands for Hyper Text Markup Language used for web page development.

Joomla

Developed for Internet based applications.

PHP

Developed for Internet based server side applications

LISP

Stands for List processing developed for robotics.

All languages except machine language needs compilers to convert programming code into machine language of 1's and 0's understandable to machines.

Every language has its compiler made to convert it into machine code.

Some Languages uses interpreters and assembly language uses assembler to do these tasks.

View the original article here