CODEWITHSUNDEEP
excelexcel-formulavba
Vahid
Vahid

Reputation: 3442

Excel VBA: Cell Value from Macro

I have a Macro that sets the value of a cell from other cell values.
So the Macro sets the value of a cell when I run the code as usual.

But I want the Macro work like a Formula.
So the value of a cell always changes when those cells are change.

Is there any way to do this?
Or Maybe a way to call the Macro from formula?

Edit: (After Comments)

Ok. This is what I want from the Macro.

I have Hours:Minutes, and I want to SUM all of them.
The problem is I have negative Hours:Minutes that I've calculated them using the formula below:

=TEXT(ABS(H10-E10),"-h:mm")

So I have something like this:

08:20, 02:10, -03:20, [and so on...]

And I want the result: 

 08 + 02 - 03 = 07
 20 + 10 - 20 = 10
 = 07:10 (The Result)

Because we don't have negative Time in Excel, and I have Text instead of Time, so I have to calculate them with ForLoop in VBA.

Upvotes: 0

Views: 3274

Answers (2)

Amit Panasara
Amit Panasara

Reputation: 680

Shortcut Notation method for getting values from range

Sub getting_value()

'Static Cell-pointing
    Debug.Print [a1]                      'for referring Active Sheet's cell
    Debug.Print [Sheet1!a1]               'for referring Sheet1's cell 

'Partial Dynamic Cell-pointing
    Debug.Print [a:b].Cells(1,1)               'for referring Range
    Debug.Print [5:5].Cells(1,1)               'for referring Range
    Debug.Print [a:b 5:5].Cells(1,1)           'for referring Intersect

    Debug.Print [a1,a3].Cells(1,1)              'for referring a1 AND a3

    Debug.Print [a1.a3].Cells(1,1)              'for referring a1 TO a3
    Debug.Print [a1..a3].Cells(1,1)             'for referring a1 TO a3
    Debug.Print [a1:a3].Cells(1,1)              'for referring a1 TO a3

'Dynamic Cell-pointing
    Debug.Print [Table1[Column1]].Cells(1,1)   'for referring Table1's column1 
    Debug.Print [NamedRange]                   'for referring Named Range (making named range dynamic by vba -> ThisWorkbook.Names.Add) 

End Sub

Reference

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

This is a very simple example. Say your macro changes A1 based on the value in cells B1 thru D1.

Sub MyMac()
    Range("A1").Value = Range("B1").Value + Range("C1").Value + Range("D1").Value
End Sub

We will use an event macro to detect manual changes to any of the input cells and run MyMac. Put this event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Set r = Range("B1:D1")
    If Intersect(Target, r) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        Call MyMac
    Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

  1. right-click the tab name near the bottom of the Excel window
  2. select View Code - this brings up a VBE window
  3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

  1. bring up the VBE windows as above
  2. clear the code out
  3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

NOTE:

This is only good for Manual changes to the input cells. If any of B1 thru D1 contained formulas, the Calculate event would be used.

Upvotes: 1

Related Questions