EXPERIMENT 002

EXPERIMENT 002: Invoking of 'SHELL' function; shows exact address of an Excel file on one of the cells on one of the sheets AND opens the folder of current location of this file as the file loads.

The Shell Function: This function is well equivalent to 'Run' item on the Windows start menu. This VBA Shell function can be used to start an external program or perform any operation. This page describes a procedure named mRunCal that will call the Shell function and then invoke the executive file of Calculator from Windows.

Public Sub mRunCal()

Dim aShell

aShell = Shell("calc.exe")

'The function will search the executive file of calculator,

'otherwise you may give complete address of the file also.

End Sub

Assign this procedure to any command button on excel sheet. This button when clicked, will run the calculator.

You can run any programme on excel sheet using this function.

Given below are two formulae. Copy and paste these two in two different cells. But before doing it, your file must have been saved.

1. This will display the address of current location of the file.

=MID(CELL("filename"),1,FIND("[",CELL("filename"))-1)


2. This changes into a hyperlinked text and when clicked, opens the folder in which the file lies.

=HYPERLINK(MID(CELL("filename"),1,FIND("[",CELL("filename"))-1),"CURRENT LOCATION")

If you want, you can lock this file, so that no body has opportunity of seeing the formula.

EXPERIMENTS 001

All experiments will start the way as shown and written below.

Open MS Excel. Save it as 'Book1'. Press Alt + F11. Following screen appears:

Right click 'VBAPROJECT (Book1).  Select Insert à  Module. A module in the name of 'Module1' opens.  The codes are to be written on this module. You can start another module by repeating the step at written above. After you have written down the code, come out of Visual Basic window by closing it.

The command button is created by right clicking menu bar, clicking 'Forms'. 'Forms' window opens. Click 'Button' and drop onto the excel sheet. It appears with its default caption 'Button 1' or 'Button 2'. Double click the caption, edit the name the way you like and as you do in MS Word. A prompt window will appear asking you to 'Assign Module' to this button. If you have already some corresponding module written in MS Visual Basic window, you can assign the same to it. The click of button will run it.

EXPERIMENT 001: CREATING LIST OF SERIAL NUMBERS AGAINST ANOTHER LIST.

Please see the given figure. The names are given in 'Name' column. The first column is 'No,' we have created a command button, which when clicked, fills the serial number against each name starting from 1.

Open MS Visual Basic window by pressing Alt + F11. Write the code given below:

Obviously the line of code starting with a single quote i.e. ' is the remark where the coding has been explained.

Public Sub pCountF()

'pCountF is the name of the procedure.

Dim P As Integer, I As Integer

Range("B3").Select

While ActiveCell.Value <> ""

ActiveCell.Offset(1, 0).Select

P = P + ActiveCell.Count

Wend

'The code written above works like this.

'The cell 'B3" is selected, where first name appears.

'All the cells down this cell are selected one by one.

'An integer 'P' created for this work, stores the value

Range("C1").Select

ActiveCell.Value = P

'The cell 'C1" is selected, where the value of P is written.

'Though it is not necessary to take the value of P on excel sheet.

'In big programmes, you may need to use it at later stage, hence taken.

Range("A2").Select

For I = 1 To P

ActiveCell.Offset(1, 0).Select

ActiveCell.Value = P - (P - I)  '=I

Next I

'The code written above works like this.

'The cell 'A2" is selected, below which the counts have to begin.

'All the cells down this cell are selected one by one.

'The serial numbers starting from 1 to P are written one by one

'on empty cell below one another.

Range("A2").Select

Exit Sub

End Sub

Assign this procedure to command button. The click of button will run it.

DATA COLLECTION BY A SUGAR MILL FOR SUGAR CANE MANAGEMENT WITH PDA FORMS

In so far as the disposal of sugarcane crop is concerned, sugar mills are the one of best examples of PPP (Public Private Partnership) application, since only the sugar mills buy cane crop, and not any other system of agricultural marketing can do it. In this context, we have further to remember that agricultural produce, after harvesting, other than sugarcane, can be stored, and later sold at some more remunerative prices.

Such agricultural produce can be marketed directly to a consumer

Obviously, for other agricultural produce, the government, apart from ensuring crop protection, has various means of their disposal from providing mandis' facilities in the block area upto the government procurement of various crops coupled with ensuring timely payment to the farmers finally, Cane growers grow sugarcane, and it is the sugar mill only, which ensures not only the timely disposal of sugarcane, but its proper care also.

Hence a bond between sugarcane producer and sugar mill is not only a family type of one-one relation, it is a very amazing PPP arrangement too,

But this arrangement demands something else too.

For a good and entrepreneurial sugar factory, the need to integrate all levels of the manufacturing and process environment in order to optimize production and meet key business metrics cannot be undermined. Rather it is key tactic in the present highly competitive environment. By establishing means of having closed feedback, time to time, of cane growers and of their crop and produce, the management ensures to meet the critical goals of increasing throughput, particularly recovery of sugar from cane, reducing time that takes in bringing the cane from the field to factory and ensuring efficiency in cultivation and harvesting programmes.

An efficient and robust Cane Management (ICT based) System of a Sugar Mill offers a great possibility of comparatively a greater production of sugarcane by extending well-organized coverage to the entire cane development cycle from sugarcane cultivation, crop management and finally harvesting and transportation. The timely treatment of sugarcane with fertilizers and pesticides etc. coupled with regular monitoring of taking care for various diseases and pests, and finally acquisition of sugarcane is primary job of any sugar mill; since it is the availability of cane, nay, of healthy cane which decides the balance sheet of a sugar mill.

In this whole exercise, the strong field work, manual as well as of information gathering, is core issue of the subject. The fieldworker will have to gather and submit multiple kind of information on various papers and format and finally process it for reaching certain conclusion and chalk out the plans and strategies in order to have the cane development management system. And here comes the role of technology, and that is ICT (Information and Communication Technology), which seeks a paradigm shift in the traditional methods of cane management, i.e. a fundamental change in approach or underlying assumptions.

The activities involved in a sugar factory particularly for cane management are in stark contrast to that in most other industries, as it is the sugar mill that has to cater for ensuring good and profound supply of cane. Needless to say, the production activities are very much seasonal in nature, and the long off-season is dedicated for plant maintenance on one side, and cane crop management on the other. A sugar mill cane management system involves elaborated study and documentation of many crop related issues. This system includes comprehensively a plan to transform these typical manual processes into an all-inclusive ERP (Enterprise Resource Planning) solution. This may, inter-alia, include:

• Nutrient and Pest management
• Continuous crop monitoring
• Adopting latest technologies
• Diffusion of labor for harvesting and
• Arranging labour for harvesting.
• Cane procurement
• Crop Insurance and Crop loans

In this connection, the data collection and other field work which is associated with having a recorded history of cane crop development of any plot is concerned, the role of PDA (Personal Digital Assistant) i.e. of Wireless Connectivity cannot be undermined in the presence scenario of overall role of ICT in planning and finally coming up with workable strategies. Further , the installation and use of Personal Digital Assistants and wireless communication technologies, linked to demonstrate real-time transfer of information of harvest, transport and a live one to one connection between harvester and mill to improve operations and relationships between growers and millers. This is based on one simple thought: How Can I Manage My Time Better?

A PDA is a computer that fits in a field worker's hand. A web and PDA-based software application is fitted into it that enables field worker to collect data on pre-defined forms on that software, take pictures, record various historical events like fertilizer or pesticides dosing, This collected data can be viewed, aggregated and analyzed quickly and easily on any server attached to this device through wireless network of any service provider. A compatible software, having the facility of preparing various reports, charts and further analyzing and helping the management to work out plans and strategies can be loaded in the server.

Most modern PDAs have Bluetooth wireless connectivity, an increasingly popular tool for mobile devices. It can be used to connect keyboards, headsets, GPS* and many other accessories, as well as sending files between PDAs. Many mid-range and superior PDAs have Wi-Fi/WLAN/802.11 connectivity, used for connecting to Wi-Fi hotspots or wireless networks.

*GPS (Global Positioning System) refers to a well organized system of satellites and receivers that allow electronic devices to identify their location on the earth very precisely. The system comprises of 24 satellites that orbit the earth twice per day. Devices that are equipped with GPS equipment receive transmissions from at least a few of the satellites and are able to provide very precise positioning data. Each of these solar-powered satellites circles the globe at about 12,000 miles (19,300 km), making two complete rotations every day. The orbits are arranged so that at any time, anywhere on Earth, there are at least four satellites "visible" in the sky.

The use of this technology ensure the data transfer from the field 'just in no-time', with minimum of the scope of any paper work. The system facilities transfer of pictures of crop to the factory, thus giving an opportunity to the factory to come up with some solution and convey the same to the farmer, thus ensuring a timely treatment to the disease or any other problem with the plant within no time. Further, it shall facilitate the management with a real time detailed survey information to serve as the basis for cane procurement planning. In the crushing season, the factory shall use this data to schedule the issuance of daily permits to growers thereby guaranteeing regular and regulated supply, and optimising plant utilization and yield, and on the top of all, recovery of sugar.

The system can be implemented as an individual application or as a fully integrated set of solutions. The system is scaleable at both the plant and field level. The operational efficiencies can be optimized and made compatible with the latest technologies in this are coming up with the time. 

Further the benefits of ICT can be percolated down to the rural society by a big leap of sugar factory of that area. A sugar factory should possess an embodiment of a very senior member of a large family, who takes care of all other members of that family in all their affairs. The diffusion of ICT in a farming community can touch upon many areas directly related with socio-economic arena, like:

1. Latest information regarding agriculture and best practices as being evolved.
2. Dairy farming and poultry.
3. Farmyard manure
4. Welfare schemes of government.
5. Information regarding NGOs working in the area.
6. Weather forecasts.

Thus the use of ICT in general shall prove enhancing the efficiency of the sugar cane growing and harvesting process, both in terms of time saved by the factory on field data collection and transactions as well as in terms of monetary gains. It should prove to be a win-win situation both for sugar factory and cane growers.