I've written a VBA function that will be used on a worksheet as a worksheet

function. I want to modify the formatting of the cell that the function is

in. I haven't found any way that works. How do you reference the cell that

the formula is in and make changes to it?

Thanks, -phil

--

no place like 127.0.0.1

function. I want to modify the formatting of the cell that the function is

in. I haven't found any way that works. How do you reference the cell that

the formula is in and make changes to it?

Thanks, -phil

--

no place like 127.0.0.1

- Updating cells with VBA functions having a Range argument
- How to assign a value to a cell with VBA?
- Calculting SUM of cells with IF functions inside
- How to insert a complex formula in a cell with VBA
- Want to Copy the COntents of a cell and not the cell with VBA Macro
- Get current cell address
- Transpose and link cells with drag function in bottom right corner of an cell
- How to insert a complex formula in a cell with VBA
- Scroll with VBA to get active cell near top of window
- GETTING part of a STRING from a CELL with VBA
- Query an Access query with VBA function from Excel
- VBA Help: Perform Calculations Between Cells with VBA
- Problem with inserting formula in cell with VBA
- Row Number of Current Cell - within a Function
- Insert sum of cells in VBA using worksheet change event
- Getting Cells With Similar Criteria
- Coloring Cells With VBA
- Color Coding Cells With Vba
- Highlight current cell with or without conditional formating
- How can i change cell a2 with a function in cell a1?
- Referencin "Calling" Cell from VBA Function
- Delete Empty Cells With VBA
- VBA Function not refresh
- Dynamically associate a cell with a function

when updating cells in a sheet. Here is the essence of the problem.

Suppose you have three functions fnA fnB and fnC (see below), each takes

a Range argument and returns a String "A", "B", and "C" respectively.

Suppose you put fnA(A1) in A2, fnB(A2) in A3 and fnC(A3) in A4

and then type in something to A1. The functions are called but the order

is unpredictable. It seems to depend on the order I typed things in.

Moreover, if I then rearrange the links, you do not get the order of

updates that you would expect from the order of linkage.

Here is some code to show this. You use the VBA Debug window to see the

execution order.

'*******************************************************************

Private Function fnA(r As Range) As String

Debug.Print "exec: a"

fnA = "A"

End Function

'*******************************************************************

Private Function fnB(r As Range) As String

Debug.Print "exec: b"

fnB = "B"

End Function

'*******************************************************************

Private Function fnC(r As Range) As String

Debug.Print "exec: c"

fnC = "C"

End Function

Now, the interesting thing is that if you modify these functions

so that the argument is a type like double or String, then Excel

finds the correct order. For example these functions will always update

correctly:

'*******************************************************************

Private Function fnA(r As Double) As Double

Debug.Print "exec: a"

fnA = 1

End Function

'*******************************************************************

Private Function fnB(r As Double) As Double

Debug.Print "exec: b"

fnB = 2

End Function

'*******************************************************************

Private Function fnC(r As Double) As Double

Debug.Print "exec: c"

fnC = 3

End Function

My questions are:

What are the rules for Excel updating when VDA functions have Range

parameters (and why are these rules not the same as when the arguments

are Double or String)?

Could I rewrite my first set of VBA functions so that they will update

correctly, given that I must use the same Range argument in the function

calls and return a String that doesn't change?

Oh, I have an old version of Excel (Office 97), but I don't think this

is part of the problem (running under XP).

I have an Excel file with multiple VBA functions, but the most simple (theorically speaking) is making me crazy: I need to assign a value to a cell of one of the sheets, but... I cannot! The sheet is named "TOTALE", the cell is the G7. This is my function (it checks if in a table of the sheet "Foglio" there are some data highlighted and return the number of them. If they are highlighted with pink, it writes the numbers of them in the cell G7 of the sheet TOTALE):

Function GiocTotale(Row1 As Integer, ColStart As Integer, Row2 As Integer, ColEnd As Integer, Foglio As String) Dim Contatore As Integer, i As Integer Contatore = 0 For i = ColStart To ColEnd If Worksheets(Foglio).Cells(i, Row1).Interior.ColorIndex <> xlColorIndexNone And Trim(Worksheets(Foglio).Cells(i, Row1)) <> Empty Then Contatore = Contatore + 1 If Worksheets(Foglio).Cells(i, Row1).Interior.ColorIndex = 38 Then Worksheets("TOTALE").Row("G7").Value = Worksheets("TOTALE").Row("G7").Value + 1 End If If Worksheets(Foglio).Cells(i, Row2).Interior.ColorIndex <> xlColorIndexNone And Trim(Worksheets(Foglio).Cells(i, Row2)) <> Empty Then Contatore = Contatore + 1 Next i GiocTotale = Contatore End FunctionWithout the Worksheets("TOTALE").Row("G7").Value = Worksheets("TOTALE").Row("G7").Value + 1 the function work perfectly. With it, I get a #VALUE error. I tried everything, but it didn't work. =__=

based on the employees age. My IF functions are working properly. However,

when I try to insert a SUM function at the bottom to get the total cost, I

get a 0. Why won't it total my columns just because there's an IF function

in each cell?

example : if formula is "=if(a3=2;a3;a2)"

i used

cells(2,3).Formula="=if(a3=2;a3;a2)"

bu i receive an error

Application object ....

if i used "=s4"

everything is ok.

I am looking to keep the Contents of a cell ready to be pasted in an email textbox.

I have a bunch of emails appended in a cell with the help of a macro however going one step further I would like the contents of the cell and not the cell to be stored in the Copy ClipBoard and when I put my cursor in the To,CC,BCC text boxes then I can simply CONTRL+V or Right Click Paste

Warm Regards

e4excel

Problem:

I am running through a loop looking for a value so I don't know what row I am on when I hit that value.

When I hit it, I need to know the row number and how to use that number to reference another cell.

Poor code example of what I am after:

rowlocation = get.current.cell.row

activate.cell("A "& rowlocation &" ")

Thanks for any tips....

Sorry if it is a simple question, second day of learning VB for excel...

I want to Transpond and fast link cells with drag function in bottom right corner of an cell.

Example:

Stand in cell A1, enter "=C1"uses the "drag link cell" in the down right corner of a cell. I will automaticly link

A1 - C1

A2 - C2

A3 - C3 ect

My question is if it is possible to drag horisonal and link vertically.

A1 - C1

A2 - D1

A3 - E1

Are the any button command that i can ues/press so this is done automatically or an easy funtion?

Appreciate your help!

example : if formula is "=if(a3=2;a3;a2)"

i used

cells(2,3).Formula="=if(a3=2;a3;a2)"

bu i receive an error

Application object ....

if i used "=s4"

everything is ok.

TIA.

Any one knows how to get a substring from a cell?

If a cell contains this:

"Rate Plan for SPLAN2DATA"

I need to know if the cell contains "SPLAN" from the cell using VBA

There is no fixed starting position for "SPLAN" it can be either in the middle or in the beginning of a CELL.

Can anyone help?

Thanks in advance.

I'm trying to query a query in Access 2003, from Excel 2003.

The query in Access looks like:

AccessQuery: [SELECT VBAFunction(field1) FROM Table]

The query in Excel looks like:

ExcelQuery: [SELECT * FROM AccessQuery]

I use the following VBA code in Excel to excecute the query:

Code:

With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _ Destination:=ThisWorkbook.Worksheets(cDataSheetName).Range("A1"), Sql:=strQuery) .RowNumbers = True .Refresh BackgroundQuery:=False iResultRowCount = .ResultRange.Rows.Count End WithWhen I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.

So my question is; why can't I query a query with a VBA function in Access from Excel?

when the answer is like "because it can't be done", my next question will be:

Who can give me a suggestion for a work-around to achieve the same results?

thanx!

It occurred to me that one way of facilitating such a feat would be through the use of textboxes, or by simply referring to the applicable cells in VBA.

The following are the calculations that I'm trying to perforn in VBA:

E:E = B:B / C:C

F:F = E:E + G:G

G:G = F:F - (C:C / D:D)

H:H = (F:F - (C:C / D:D)) / (C:C / D:D)

I:I = D2 * (F:F - (C:C / D:D))

J:J = D:D * (C:C * (F:F - (B:B / C:C)))

I try to insert a formula with VBA, but fore some reason I get the error message "Application-defined or object-defined error"

With Worksheets(TempSheet) .... PBSadr = .Cells(n, NewCol + 6).Address(rowabsolute:=False, ColumnAbsolute:=False) SumUSDadr = .Cells(n, NewCol + 5).Address(rowabsolute:=False, ColumnAbsolute:=False) .Cells(n, NewCol + 7).Formula = "=IF(" & PBSadr & "=0;" & SumUSDadr & ";0)"'Formula should look like this: "=IF(M11=0;L11;0)"

....

Wend

Any Idea what might be wrong?

I'm creating a Function that looks at the max value between two cells.

I can get the row number of the distant cell as I'm looking for a specific value.

However, I'm struggling to get the code to return the row number of the cell that the Function is currently being run on.

Any ideas?

Thanks

I am trying to get to grips with VBA right now, and searching this forum has been a great help so far.

But right now I’m stuck at a problem (or three so to say) and hope to find some help here.

Please don’t bite me if my questions sound stupid.

So here is my problem.

I’ve got three ranges defined in a sheet ( A;B;C), each range consisting of a number of cells in a single column.

The goal is that as soon I type a number in Column A or B the sum of the Celle in A + B is shown in column C.

‘ I know I could do this by inserting =A+B in column C, but this is not the point

I’ve got the following code in the Sheet1 Object

VB:I tried a lot and the Workbook_change event for my range works well, but the rest of the code doesn’t.Private Sub rangeC_sum() Dim RngACell As Range Dim RngBCell As Range Dim RngCCell As Range RngACell = Intersect(Targetrow, Range("A")) RngBCell = Intersect(Targetrow, Range("B")) RngCCell = Intersect(Targetrow, Range("C")) If IsNumeric(RngACell, RngBCell) Then RngCCell = RngACell + RngBCell End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) ' This line checks whether a call in Range a has been changed If Not Intersect(Target, Range("A")) Is Nothing Then ' this line checks whether the entered value is numeric ' and calls the sub for calculation rangeC_sum End If End If End If If Not Intersect(Target, Range("B")) Is Nothing Then ' this line checks whether the entered value is numeric ' and calls the sub for calculation If IsNumeric(Target) Then rangeC_sum End If End If End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

So here are my questions

Do I need to have the line below really twice or is there a way of having two argument in a single “IF” loop

VB:Why do I need to negate the Intersect option with the NOT before it? I think I have to somehow handover some variable to my Private Sub rangeC_sum(), how do I do this?What’s wrong with my definition of these variables?If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:Thank you all very much for your helpRngACell = Intersect(Targetrow, Range("A")) RngBCell = Intersect(Targetrow, Range("B")) RngCCell = Intersect(Targetrow, Range("C"))If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Regards

Felix

For example, I have a column1 named "projects" and a columnb named "employees". How do I get all the names that have the same projects?

Thx in advance, and sorry if my post is not quite technical. I'm still just beginning to know Excel.

How to insert colours in excel cells using VBA codes?anyone experts can help me please?

VB:Above is the full codes. I just want to insert colour in results which display:Dim a1, a2, w(), i As Long, c As Byte ' declare variable type (value can be stored in memory) With Sheets("Sort4") a1 = .Range("b4:z45").Value ' a1 is declaration for cell with range b4 to z45 in sort4 End With Redim w(1 To UBound(a1, 1), 1 To UBound(a1, 2)) With Sheets("PV") a2 = .Range("b4:z45").Value ' a2 is declaration for cell with range b4 to z45 in PV End With For i = 1 To UBound(a1, 1) ' Ubound returns the upper bound(highest-numbered index value) in memory locations(array). For c = 1 To UBound(a1, 2) ' The comparison between sort4 and pattern verification (PV). If a1(i, c) = 1 And a2(i, c) = 1 Then w(i, c) = "PP" ' i)Value=1 in sort4 is equal to the value=1 in PV, result will display PP(Pass/Pass. If a1(i, c) = 1 And Not IsEmpty(a2(i, c)) And a2(i, c) 1 Then w(i, c) = "PF" ' ii)Value=1 in sort4 is equal to value other 1 in PV, result will display PF (pass/fail). If a2(i, c) = 1 And Not IsEmpty(a1(i, c)) And a1(i, c) 1 Then w(i, c) = "FP" 'iii)Value other 1 in sort4 is equal to value=1 in PV, result will display FP (fail/pass). If a2(i, c) 1 And Not IsEmpty(a2(i, c)) And a1(i, c) 1 And Not IsEmpty(a1(i, c)) Then w(i, c) = "FF" ' iv)Value other 1 in sort4 is equal to value other than 1 in PV, result will display FF (fail/fail). Next Next ' Display results after comparison. With Sheets("Result") .Range("b4:z45").ClearContents .Range("b4:z45").Value = w .Range("e49") = WorksheetFunction.CountIf(.Range("b4:z45"), "PP") ' Count quantity of PP .Range("e50") = WorksheetFunction.CountIf(.Range("b4:z45"), "PF") ' Count quantity of PF .Range("e51") = WorksheetFunction.CountIf(.Range("b4:z45"), "FP") ' Count quantity of FP .Range("e52") = WorksheetFunction.CountIf(.Range("b4:z45"), "FF") ' Count quantity of FF End With End Sub ' The sort4 and PV wafer map have to be located within the range of b4 to z45 only. ' There are marks from 0 to 41 according to the size of the wafer map which match the fix range(b4 to z45).If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

1)PP = yellow

2)PF = green

3)FP = blue

4)FF = grey

Can any experts modify the codes that I gaves above with inserting the colour codes?Thanks

VB:Range) Static rngPrev As Range, PrevColor As Integer Dim TempColor As Integer TempColor = Target.Cells(1, 1).Interior.ColorIndex If Not rngPrev Is Nothing Then rngPrev.Interior.ColorIndex = PrevColor PrevColor = TempColor With Target.Interior .ColorIndex = 35 .Pattern = xlSolid End With Set rngPrev = Target End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks Mark

i always thought is was impossible to change a cell with a function in another cell.

however i saw the function bdh of the bloomberg addin able to do this.

so in cell a1 you type bdh(...) and executing this function gives results in range a1:b252.

how can you do it?

can anybody tell me how I can insert into the comment of the cell from which a vba Function is called a message ?

Example:

VBA custom Function: GetPrice(Date As String; ProductCode As String)

Say that in cell A1 I put: =GetPrice("01/02/2005";"AAAX001")

I would like the function to return the price if available or write into the comment the explanation of an error (if any)

Thanks in advance

Paolo

From Italy

I need a vba that will delete all cells with value of "" or zero, not deleting the row or the column just deleting any contents, so that a hide row macro ( which i have set up) will not pick up these "" values & regard them as nonblank.

Thanks

Mark

I have an Excel file with VBA function

The problem is after a while of working with this file, the VBA function "get tired" , that mean they not refresh or return zero or value instead of valid number.

some pepole told me it's becouse the RAM memory is full (even it's nice strong PC).

Does someone know how to Handle this problem ? (F9 doesn't help)

Is there any way to clear the memory that those Function take ?

Thank's for any help!

I am a newcomer into Excel programming (VBA) and most probably my question is not quite challenging:

I want to associate a function to a cell inside VBA, so that if I put an Y (for Yes) in that specific cell it automatically takes the value of other cells around and perform some tasks.

Thanks a lot.

Caio