pacificwaters


Using VB 6.3.

Trying to call a sub within a function, and when I call it in Excel via User-Defined-Function, it doesn't work.

But when I do 'F8' to step into the code of the sub, it works fine:

Public function functionEXE(...)
call cellref
end function


Sub cellref() ' If I 'F8' and step into the code from here, it works fine. But when I execute the functionEXE on sheet.xls,

' this part doesn't work, and I'm not getting any error messages, why
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.Range("A65536").Value = 12345
Cells.Range("A65536").Clear
Cells.Range("A1").Select
End Sub

Regards,

Brent




Re: calling sub within function. works fine as sub, but not in function

Andy Pope


Hi,

Functions used on a worksheet can only altered the cell which it was called from.
If the function tries to alter other cells on the sheet it will fail, as is the case with your code.

To get around this problem you need to use a worksheet event to run your function from.






Re: calling sub within function. works fine as sub, but not in function

pacificwaters

How would I use a worksheet event to run my function

This is what I tried, but didn't work.

Code Block

public function functionName(...)

Call Worksheet_SelectionChange(ActiveSheet.Name)

End Function

Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveWindow
Cells.Select
.Selection = Target.Copy
.Selection = Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks :=False, Transpose:=False
End With
End Sub






Re: calling sub within function. works fine as sub, but not in function

Andy Pope

More like this. And normally you would test that the Target range is a specific one otherwise it will run functionEXE every time the selection changes.


Sub Worksheet_SelectionChange(ByVal Target As Range)
'
' possible include test for specific cell
'
functionEXE

End Sub








Re: calling sub within function. works fine as sub, but not in function

pacificwaters

I don't think we can test for a specific cell [ range("A1").select ], because all code must be dynamic and not hard coded.

This time, I tried this:

Code Block

Sub Worksheet_SelectionChange(ByVal Target As Range)

Call functionEXE(I CAN'T PASS ANYTHING IN HERE BECAUSE USERS MUST ENTER IN User Defined Function)
Target.Select

target.copy

End Sub

Public Function functionEXE(ByRef MemberName As String)
call cellref

End Function

Sub cellref()
Cells.Select
Selection.Copy
End Sub

If I can get something simple to work, like selecting all cells and copy, then I can probably solve the rest (or make strong progress).

Thanks for your assistance.





Re: calling sub within function. works fine as sub, but not in function

pacificwaters

Basically, I just want to perform a "Copy and PasteSpecial Values on the whole sheet" after my function generates the correct values for each cell.





Re: calling sub within function. works fine as sub, but not in function

Andy Pope

I'm confused as to how and what your function is doing, which may explain why trying to position it in a event is not suitable.

What would the formula look like in the cell What are the arguments
Can a use enter this formula anywhere on the sheet as many times as they like







Re: calling sub within function. works fine as sub, but not in function

pacificwaters

here's what i originally wanted to do.

  1. users enter a formula in a cell, if there is an error in the formula, then a msgbox displays the "activecell.address".
  2. so let's say that users enter a formula in cell C2 that references a value in B2.
  3. they then click on cell C2 and drag down to C10, that formula pertains to all those cells and performs the calculations.
  4. if there is an error in cell C10, the activecell.address in error msgbox still points to cell C2.
  5. here's the question, how do i set the activecell.address to equal to the cell that's being calculated

here was my proposed solution

  1. when there's an error, the formula places a 0 or #VALUE in the cell.
  2. macro then performs a "copy" and "paste-special-values", and then "find" all 0 and #VALUE and display the proper activecell.address
  3. this way we can trap the correct activecell.address and display the correct error message

any other ideas





Re: calling sub within function. works fine as sub, but not in function

Andy Pope

You could use this event code. It will fire every time the sheet calculates, which may or may not get on the users nerves.

Private Sub Worksheet_Calculate()

Dim rngErrors As Range
Dim rngCell As Range

Set rngErrors = Cells.SpecialCells(xlCellTypeFormulas, 16)
If Not rngErrors Is Nothing Then
For Each rngCell In rngErrors.Cells
MsgBox rngCell.Address
Next
End If

End Sub






Re: calling sub within function. works fine as sub, but not in function

pacificwaters

Because I'm using VBA to interact with Essbase, the Cells.SpecialCells(xlCellTypeFormulas, 16) will not work as the errors only pertain to Excel errors.

Now, I would like to have something like this in my module: (by the way, this is for an add-in)

Code Block

If hMember = 0 then

Dim rngErrors As Range
Dim rngCell As Range

' this won't work:: Set rngErrors = Cells.SpecialCells(xlCellTypeFormulas,16)

' 23,2, 4, 6
If Not rngErrors Is Nothing Then
For Each rngCell In rngErrors.Cells
MsgBox rngCell.Address

'and this should only display the correct cell address when hMember=0
Next
End If
end if

'i think we're almost there, thanks for your help.





Re: calling sub within function. works fine as sub, but not in function

Andy Pope

If you are coding for an Add-In you will need to use application level events. This requires using a class object to capture all events.
For more information read Chip Pearson's page.
http://www.cpearson.com/excel/AppEvent.aspx





Re: calling sub within function. works fine as sub, but not in function

pacificwaters

'In this Workbook

Option Explicit
Dim mEvents As New Class1 'Create a reference to the class module

'The above variable needs to persist after the Workbook_Open event below finishes
' It is of type WorkbookOpen so that it has a WorkbookOpen event

Private Sub Workbook_Open()
' Need to 'turn on' the event procedure in the class module
Set mEvents.ExcelApp = Application 'Set Excel as the event source
End Sub

'In this Class-Module

Option Explicit

Public WithEvents ExcelApp As Application 'This creates a variable that can have events

Private Sub ExcelApp_WorkbookOpen(ByVal Wb As Workbook) 'This event applies to any workbook opened
Dim aSheet As Worksheet
For Each aSheet In Wb.Worksheets 'Wb.Worksheets represents all sheets of the just-opened workbook
aSheet.Cells.Replace What:="'*!", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next aSheet
End Sub