Showing posts with label VBA. Show all posts
Showing posts with label VBA. Show all posts

VBA Excel Cell: How to connect custom VBA program with Excel Spreadsheet
Friday, December 17, 2010

A part from working with my company, I used to do some freelance work on the custom VBA Excel program. Usually I’ll do some advertised on the web offering people on the automated VBA program that I think would ease their daily basis work. There are a lot of my customers who want me to program some easy application that would give the result or output in the Excel Spreadsheet. This is actually a very basic fundamental knowledge for one VBA Excel programmer in dealing and connecting data between VBA and Excel Cell within Excel Spreadsheet.

I would not like to mess your mind up with all the VBA coding but with some easy practical example. I hope by going through this example you can easily understand and hence applying it on your work or task immediately.

As we all know the spreadsheet excel is sort in cell form indicating by alphabet (A-Z) for the column and digit (1-infinite) for the row. For example Column 1 and Row 1 are donated by A1 in Excel cell. Ya, I know everybody knew this but what I would like to stress here is actually the characteristic of Excel cell that you must know in order to establish the connection with the VBA program within Excel itself. For this example I would like to give some example on how you can extract data from the text file and put it within excel spreadsheet cell. Below are the data from the text file:

From these data I would like to build some easy coding on VBA in order to extract those data and placed it in Excel cell. For example I would like to read the value of OD (0.2191), TH (0.0191), SRHO (7850), E (207000000000) and POISS (0.3) and then placed it in the Excel Cell.

In order to read the text file, first you need to open the file by using this coding:


Open Pathname For Input As #1

‘Pathname here need to be defined first on where the file is located in your directory. For example you can set Pathname to be = “C:\Users \Documents\VBA Excel Cell \Input Data\LMEP0000”

After that you can program your VBA to be able to read the text file line after line. Below is the code:

Input #1, xx(i), OD(i)
Line Input #1, textline
Input #1, xx(i), TH(i)
Line Input #1, textline
Input #1, xx(i), SRHO(i)
Line Input #1, textline
Input #1, xx(i), E(i)
Line Input #1, textline
Input #1, xx(i), POISS(i)

After you have successfully read all the required values then you’ll just need to write a simple VBA coding to place them in the Excel cell. Below is how you can write it:

With Worksheets ("VBAExcelCell")
.Cells(1,1).Value = “OD Value”
OD(i) = .Cells(1,2).Value
.Cells(2,1) .Value = “OD Value”
TH(i) = .Cells(2,2).Value
.Cells(3,1) .Value = “OD Value”
SRHO(i) = .Cells(3,2).Value
.Cells(4,1) .Value = “OD Value”
E(i) = .Cells(4,2).Value
.Cells(5,1) .Value = “OD Value”
POISS(i) = .Cells(5,2).Value
End With

And the result is as below:

I hope you can try it by your own to make your understanding on the VBA Excel cell clearer. It is actually a very fundamental knowledge in connecting any input file with Excel Cell. Till then~

Posted byMatt at 9:42 PM 0 comments  

Labels: , , , ,

VBA Excel: Develop Engineering Economy Factor Table
Sunday, October 10, 2010

Hi all. A warm greeting from me to everyone..This morning I wake up at 7.00am washing my face and have a very quick breakfast and I'm at my room while writing this entry.It has been some time but today I'm about to share with you on how to perform and create some basic table on Excel worksheet by using visual editor built in Excel. If you are studying Engineering Economy, you must probably learning about engineering economy factor which is important in calculating the Present Worth value, Annual Worth, Future Worth and etc. To calculate or predict these value you must first know how to calculate the factor that used in those calculation. For example you are planning to invest some amount of bucks for your business. While knowing the interest involved and the time for your investment to gain profit you can predict the future worth by evaluating the engineering factor in your prediction.


And today, I would like to share with you step by step on how to developed your own engineering factor table by coding some simple visual basic code in VB editor. You can also make it more attractive by creating some simple interface in order user to key in the required input for the interest, i and time,n .

Here is how your interface may look like.You can also create your own interface that is more attractive in the VB editor.

You can insert the percent of interest,i and number of year,n. In the "Show Table" command button you can coding some visual basic code in order to execute those input in one table in Excel Worksheet. OK, here is how it work.First, you can start writing the code inside command button by double click on it as shown in the following figure;

Now I would like to briefly explained on how you can write your code inside any object that you'd inserted within your interface. Say you have already create your own interface as shown in the first figure. double click on the "Show Table" command button. You'll be directed to the source code environment where you can start your coding. Once you enter inside any object it will automatically define the way you can activate it that is _Click(Single Click). You may also change the way it will be execute as you like say _DbClick, _Initialize, _activate and .etc.

Private Sub CommandButton1_Click()

{Your code}

End Sub

{Your code} ---> For calculating Engineering Economy factor for F/P, P/F, A/F, F/A, A/P and P/A

Private Sub CommandButton1_Click()

With Worksheets("Engineering Economy Table") '' Define your sheets name in Excel Worksheet

Format_Table.Format_Table '' The Format of your table.You can just simply macro it

n = 480 '' The number of years, n .you can either fix it or flexible its value in user interface
a = 480
i = TextBox7.Value * 1 / 100 '' The interest rate read from Textbox7 in your userform

Dim MyArrayFactor(12, 12) '' Define your Array to matrix 12 x 12

For n = 1 To a '' Use For Function to iterate your calculation from n = 1 to say 480

''Inserting your input in Excel Cell and calculating for each of the factor
************************************************************************
.Cells(5 + n, 1) = n
.Cells(5 + n, 8) = n
.Cells(5 + n, 2) = Format((1 + i) ^ n, "0.0000") ' *p
.Cells(5 + n, 3) = Format((1 + i) ^ -n, "0.0000")
.Cells(5 + n, 7) = Format(((((1 + i) ^ n) - 1) / (i * (1 + i) ^ n)), "0.0000") ' *A
.Cells(5 + n, 6) = Format(((i * ((1 + i) ^ n)) / (((1 + i) ^ n) - 1)), "0.0000") '*P
.Cells(5 + n, 4) = Format((i / (((1 + i) ^ n) - 1)), "0.0000") '*F
.Cells(5 + n, 5) = Format(((((1 + i) ^ n) - 1) / i), "0.0000") '*A
************************************************************************

Next n "For every For Function it must end with Next n (iteration)

.Cells(1, 5) = TextBox7.Value * 1 & " % percent"
.Cells(1, 3) = TextBox7.Value * 1 / 100
End With " For every With function it must end with End With

" Format of your table
************************************************************************
ActiveWindow.View = xlPageBreakPreview
ActiveWindow.Zoom = 100
Range("C1,A6:A485").Select
Range("A6").Activate
Range("C1").Select
Selection.Interior.ColorIndex = 6
Range("A6:A485").Select
Selection.Interior.ColorIndex = 6
Range("A1:H5").Select
Range("H5").Activate
Module1.Macro4
Module1.Macro11
Range("A1:H5").Select
************************************************************************
Application.WindowState = xlMaximized " Maximized your Excel Worksheet once the_ calculation completed.

End Sub

The result should be as in the following figure:

You can try to copy paste this code into your command button and try to evaluate it. If there's a debug error just let me know. I'll try to advice on how to solve it . Till Then.. ^^

Posted byMatt at 7:29 PM 0 comments  

Labels: , , ,

Subscribe to: Posts (Atom)