Extract Calculated Value from Excel Cell

[JPimp]

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

 

[AnotherHiggins]

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.

 

[DaleWatson123321]

Hi Spunky,

With VBA, it should be as simple as

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...

Sub Get_SalesTotal()
    Dim val As Double
    val = [sales_total].Value
    MsgBox "Value is: " & val
End Sub

Hope this helps.

Regards, Dale Watson

 

[AnotherHiggins]

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.

 

[SkipVought]

Been there -- done that, John! ;-)

Skip,

_[red][/red]

 

[JPimp]

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

 

[PHV]

Why not posting your actual code (with the variables declarations) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JPimp]

 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

 

[PHV]

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

 

[JPimp]

:-( , still the same error on this line:

MsgBox "Value is: " & myVal

Just call me Spunky

 

[xlbo]

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

 

[JPimp]

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

 

[xlbo]

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

 

[JPimp]

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

 

[xlbo]

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

 

[JPimp]

Error 2029 comes up in the immediate window (twice?).

Thanks!

Just call me Spunky

 

[SkipVought]

What does the Watch Window indicate regarding the WS object?

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

Skip,

_[red][/red]

 

[xlbo]

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

 

[Rofeu]

try using

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

 

[PHV]

Anyway, I'd avoid val as variable name ...