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
Labels: Excel, Excel Cell, Spreadsheets, VBA, VBA Excel Cell