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

 

[JPimp]

Hmm, it is possible it thinks it is an array as the data I am reading from usually sends its data in arrays, but in this case I am capturing what's "current" so to speak, but it is a time-based variable, so it could very well treat it as an array in Excel....if that were the case, can I even call to the cell, and what type would I declare Val as?

Thanks!

Just call me Spunky

 

[Rofeu]

Hi,

as PHV stated, avoid using names for variables that are also used as VBA commands. Use something like MyVal.

Declaring it as a variant is ok. The problem arises when you try to call it in a messagebox.

You could see what IsArray(MyVal) returns to check whether the variant has been instantiated as an array.

To get it in the messagebox, you can try something like Join(MyVal, " ") or whichever delimiter you want.

Cheers,

Roel

 

[JPimp]

I did this:

sArray (myVal)
      
  myVal = WS.Range("A1").Value
        
Debug.Print (IsArray(myVal))

And the immediate window output was FALSE

So does that mean it is not an array?

Thanks

Just call me Spunky

 

[xlbo]

The debug.print sahouldn't have failed even if it is an array

As per my prior post:

does your commandbutton have a "TakeFocusOnClick" property ?

Have you checked that WS has been set properly and is referencing the sheet?

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]

Hi xlbo, no it does not have TakeFocusOnClick property, should it? I am wondering if maybe I have to copy the "value" of the cell A1 to A2 and then call A2 in the VB code instead of trying to read the original formula results of A1?

Just call me Spunky

 

[xlbo]

That really shouldn't be an issue. If a cell has a value returned from a formula, you should be able to pick it up in code very easily

Can you post the latest code you have tried ?

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]

 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)
 
      
  myVal = WS.Range("A1").Value
       
          
  MsgBox "Value is: " & myVal





 ExcelApp.Quit
    Set WS = Nothing
    Set ExcelApp = Nothing


End Sub

Just call me Spunky

 

[PHV]

What about replacing this:
myVal = WS.Range("A1").Value
with this ?
myVal = WS.Range("A1").Text

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

 

[combo]

What is .Sheets(1) in your workbook? This reference includes hidden sheets and other sheet types (as chartsheet, old dialogsheets etc.).

I'd follow some of above hints:

1. set proper references:
Dim WB as excel.workbook
'''
Set WB = ExcelApp.Workbooks.Open ("C:\Book1.xls")
Set WS = WB.WorkSheets(1)
This will remove any ActiveSomething from your code too.

2. test the references, either by Msgbox (WB.Name, WS.Name, WS.Range("A1")) or line by line code execution with 'Locals' window on.

I would also, while testing, set excel visible (xlapp.visible=true).

combo

 

[JPimp]

ok, I did something different, I copied the value from A1 to A2 (paste special - value only), and now I can read the value in A2 using the VB code, however, it does not read the updated value, i.e. when Excel opens the file Book1.xls, I have a macro named Autpen, which automatically populates A1 with the most recent data, copies the value over to A2, but my VB code only reads the "old" saved value of A2 from Book1, so I assume that the VB code is not invoking the macro or something like that??

Kai-What?