Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Extract Calculated Value from Excel Cell

Status
Not open for further replies.

JPimp

Technical User
Mar 27, 2007
79
US
I was trying to get the calculated value from an Excel cell using VB 6.0, but someone recommended I try this using Excel VBA, and then call the variable somehow from VB? I am clueless (well, not completely), when it comes to VB and VBA, so any pointers would be greatly appreciated!

Just call me Spunky
 
Example:

MyVariable = range("A1").value

For future assistance, please post VBA/Macro specific questions in forum707.



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.
 
Hi Spunky,

With VBA, it should be as simple as
Code:
Sub Get_Value()
    Dim val As Double
    val = [A4].Value
    MsgBox "Value is: " & val
End Sub
A preferred method, however, is to reference a cell that has been assigned a "range name". This way, if the sheet gets modified, then you don't have to modify your code to change the cell address. Internally, Excel maintains the connection between range names and the cells. An example of code that references a range name is...
Code:
Sub Get_SalesTotal()
    Dim val As Double
    val = [sales_total].Value
    MsgBox "Value is: " & val
End Sub

Hope this helps.

Regards, Dale Watson
 
ME said:
For future assistance, please post VBA/Macro specific questions in forum707: VBA Visual Basic for Applications (Microsoft).
Sorry - I thought I was in a different forum.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.
 
seems like every time i try to read A1, it gives me a type mismatch error, like it does not like the value in this cell....

Just call me Spunky
 
Why not posting your actual code (with the variables declarations) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Code:
 Private Sub Command1_Click()
   Dim ExcelApp As Excel.Application
  Dim WS As Excel.Worksheet
  Dim i As Integer
  Dim stra As String

  Set ExcelApp = CreateObject("excel.application")
  ExcelApp.Workbooks.Open ("C:\Book1.xls")
  Set WS = ExcelApp.ActiveWorkbook.Sheets(1)
 
 
    Dim val As Double
    val = [A1].Value
    MsgBox "Value is: " & val

 ExcelApp.Quit
    Set WS = Nothing
    Set ExcelApp = Nothing


End Sub


Just call me Spunky
 
Replace this:
Dim val As Double
val = [A1].Value
with this:
Dim myVal As Variant
myVal = WS.[A1].Value
or this:
Dim myVal As Variant
myVal = WS.Range("A1").Value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
:-( , still the same error on this line:

Code:
MsgBox "Value is: " & myVal



Just call me Spunky
 
What is in that cell ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
It has a calculated value, in fact it is pulling a piece of data from a server in to this excel cell, so when the spreadsheet opens, the cell displays a number.

Just call me Spunky
 
Just to be clear, your new code is:

Dim myVal As VARIANT
myVal = WS.[A1].Value
MsgBox "Value is: " & myVal


One other question - how many sheets in the workbook ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Yes, that is correct, that's the code I am using, just one sheet in the workbook, sheet1, and the cell in question is A1. Thanks xlbo!

Just call me Spunky
 
what happens if you add

debug.print WS.Range("A1").value
debug.print myVal

before the

MsgBox "Value is: " & myVal

line

Check what appears in the 'immediate' window....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Error 2029 comes up in the immediate window (twice?).

Thanks!

Just call me Spunky
 




What does the Watch Window indicate regarding the WS object?

Put a break in your code before the statement that errors an observe.

Skip,

[glasses] [red][/red]
[tongue]
 
Hmmm - does your commandbutton have a "TakeFocusOnClick" property ?

You may also try to ensure that you are referencing the correct workbook:

Dim ExcelApp As Excel.Application
Dim WB as Excel.Workbook
Dim i As Integer
Dim stra As String

Set ExcelApp = CreateObject("excel.application")
Set WB = ExcelApp.Workbooks.Open ("C:\Book1.xls")

Dim val As VARIANT
val = WB.Sheets(1).Range("A1").Value
MsgBox "Value is: " & val

ExcelApp.Quit
Set WS = Nothing
Set ExcelApp = Nothing

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
try using

Code:
Private Sub Command1_Click()
Dim ExcelApp As Object
Dim WS As Object
Dim i As Integer
Dim stra As String
Dim val As Variant

Set ExcelApp = CreateObject("excel.application")
Set WS = ExcelApp.Workbooks.Open("C:\Book1.xls").Sheets(1)

  val = WS.Cells(1, 1).Value
  MsgBox "Value is: " & val

ExcelApp.Quit
  Set WS = Nothing
  Set ExcelApp = Nothing

End Sub


btw, the only times I've had a Type Mismatch when calling a variable in a messagebox was when it turned out to be an array, although I don't know how that would be possible from a single cell you might want to check the datatype of val before the messagebox.

Cheers,

Roel

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top