PDA

View Full Version : Custom function help (Probably just me being stupid!)



Phelony2
08-01-2013, 02:49 AM
Hi Guys

Had to create a new profile, so I'm not as green as I look!

I'm trying to create a custom function to turn .close .save etc into a variable that can be applied to the thisworkbook function.

I suspect the problem I'm having is largely due to not knowing what to call the function in terms of string, object, action etc.

What I've got is as follows (I know that DoAsTold shouldn't be a string, but I'm lost for what it should be!)


Public Function DoAsTold() As String
Dim dothis As String
Select Case Sheets("sheet1").Range("C1").Value
Case "A"
'close
dothis = Sheets("sheet1").Range("B2").Text
Case "B"
'save
dothis = Sheets("sheet1").Range("B3").Text
End Select
DoAsTold = dothis

'MsgBox DoAsTold
End Function


Which in turn should then interact with:


Sub doaction()
ThisWorkbook.DoAsTold
End Sub


Can anyone shed any light on where I'm going wrong, or even just tell me what the function after "thisworkbook" is actually called so I can go Google a solution?

Thanks

Phel

patel
08-01-2013, 04:05 AM
Why do you want use a function ? you don't need in this case because you are using function without parameter
http://www.cpearson.com/excel/writingfunctionsinvba.aspx

Paul_Hossler
08-01-2013, 04:50 AM
Good morning --

There's a few issues that I can see

1. This is how an object's (e.g. a Workbook's) method is called. Since .DoAsTold is not a WB method it doesn't work



ThisWorkbook.DoAsTold


2. You could combine both into a sub as was suggested



Public Sub DoAsTold1()
Select Case Sheets("sheet1").Range("C1").Value
Case "A"
'close
Call ThisWorkbook.Close(False)
Case "B"
'save
Call ThisWorkbook.Save
End Select
End Sub


3. The other way that I know is to use CallByName and invoke the WB objects' methods, but I think that'd be over kill

Paul

SamT
08-01-2013, 06:41 AM
Put DoAsTold in the ThisWorkbook Code Page, then from another book use
Workbooks("ThatThereBook").DoAsTold

IF DoAsTold is a Function, it should be returning a value. Similar to a Property

If it's a sub, it should do something to ThisWorkbook, similar to a method.

I use the same style when coding data entry userforms. Say I have some data table on sheet 3, I will rename the sheets code name to something meaningful like "SomeDB." In the sheet's code page I'll put some code like
Function NextRecordRow() As Long
NextRecordRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
End Function
In the UserForm, when I place the Controls values in the Data Sheet
Dim R As Long
With SomeDB
R = .NextRecordRow
.Cells(R, Colnum) = TextBox1.Value
'Etc

Phelony2
08-01-2013, 08:09 AM
Thanks Gents, I knew it was possible.

The reason for making it a method in itself is for a larger automation project that will cycle through a number of sheets which need to be closed and saved in sequence. The main automation code doesn't like it when the workbooks it opens close themselves, so it's handled internally by this master workbook.

Rather than coding for each one, I'm making the workbook names variables so it can then cycle through them and refer to cell stored cues to close or leave open as required based on the dependancies of the workbooks.

Thank you for your help. :cloud9:

Paul_Hossler
08-01-2013, 08:30 AM
The reason for making it a method in itself is for a larger automation project that will cycle through a number of sheets which need to be closed and saved in sequence. The main automation code doesn't like it when the workbooks it opens close themselves, so it's handled internally by this master workbook.


Wouldn't it be easier to just have the master WB loop and open the other workbooks in sequence, use the cell stored cue in the other WB, do what needs to be done to the other WB, and then go get the next one?

Paul