Free Microsoft Excel 2013 Quick Reference

Get current cell with VBA function

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


Post your answer or comment

comments powered by Disqus
I have a problem with the order in which Excel calls VBA functions
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).

Hi to all, I'm new to this forum. I have a really simple question: what's the right method to assign a value to a cell with VBA?
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 Function
Without 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. =__=

I have a bunch of cells with IF functions inside to give me a dollar amount
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?

How to insert a formula in a cell with VBA
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.

Want to Copy the COntents of a cell and not the cell with VBA Macro

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

What is the code to return the address of the current cell ?

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

Hi,

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!

How to insert a formula in a cell with VBA
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.

If I activate a cell with VBA macro code, if the cell is already visible on screen, the window doesn't scroll at all. If the cell isn't currently visible, Excel scrolls the window so that the selected cell's row is roughly in the middle of the window. Is there any way to scroll the window with VBA code so that the selected cell is near the top of the window. I want as much of the text below the selected cell to be visible on screen as possible.

TIA.

Hi everyone,

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.

Hello,

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 With
When 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!

I'm trying to figure out how to perform math functions with VBA. My ultimate goal is to create a 3-cell circular reference, with data being able to be entered into any of them.

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

Hi,

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?

Hi,

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

Hello Everybody

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:
	
 
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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I tried a lot and the Workbook_change event for my range works well, but the rest of the code doesn’t.

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:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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?

	VB:
	
RngACell = 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
Thank you all very much for your help

Regards
Felix

Is it possible to get multiple cells with the criteria? it always happen to only show the first cell in a column that have that criteria.
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.

hi..

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


	VB:
	
    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
Above is the full codes. I just want to insert colour in results which display:
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

I have a spreadsheet with cells containing different names. I want all of the cells containing the word Volitility to be colored one color, all of the cells with the word Momentum colored another color, and so on. I have more than three names so I cannot use conditional formatting. Attached is a workbook that has the uncolored cells in worksheet 1 and an example of what I want it to end up looking like in worksheet 2. Please help me write a program that does this. I have very limited exposure to VBA.

From searching old post I found this code by Derek and Thomach. This works great for highlighing current cell and return cell to orginal color. the What I am now wanting to do if the the cell has an active conditional format also highlight that cell. Currently if the cell has an active conditional format nothing happens


	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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks Mark

hi,

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?

Hi,

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

Hi All

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

Hi
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!

Hello all

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


No luck finding an answer? You could always try Google.