Excel VBA Help constructing a function please?

  Simsy 22:15 06 Jun 03
Locked

Hi chums,

I've very nearly finished a project in Excel (2000) I've been working on for some time. Most of the coding I've managed to work out myself, with little bits of help from another site, click here There is one outstanding bit however and I've not got what I need there...

I haven't got the hang of putting together functions. I think I understand how to use them, but the penny just hasn't dropped on how to construct them...

The situation is this...

I need a function that will look for the existance of a variable on a specified sheet, (LNEM), in a workbook, returning a true or false value.

If "True" then that worksheet is selected and another procedure, a deletion, is applied to that sheet.

If "False" is returned, then the function looks for the same variable on another specified sheet,(ExtraLNEM). This should return "True", at which point the deletion procedure is applied to this other sheet. If the return should be "False" again, then something has gone awry and a message box should appear.

I hope that make sense.... this is what I have come up with so far, it's just constructing the function that I can't do...(consider this psuedocode.. there may be syntax errors here!)

The variable that needs to be looked for is called "TargetDate" (It only needs to look in the range A10:A29)

TargetDate = ActiveCell.Value


(The Active Cell is on another sheet, and is selected before things get to this next stage)


Create a Function "IsThisDataOnThisSheet" as Boolean


Then use this function in the following way;

Remove Sub()

If Sheet(LNEM) IsThisDataOnThisSheet = True then
select Sheet(LNEM)
call deletedata
Else If
Sheet(ExtraLNEM) IsThisDataOnThisSheet = true
select Sheet(ExtraLNEM)
call deletedata
Else
msgbox "The data to be removed can't be found."
End If

End Sub()

Can anyone help me with the code for the functon? I'd welcome comment if you think there is a better way to approach this as well. It's my first VBA project, and I've been working on it since November! I'm very nearly there, this is, I think, the last piece of the jigsaw!

Thanks in anticipation,

Regards,

Simsy

  Simsy 22:24 06 Jun 03

here it is clearer..

Remove Sub()

If Sheet(LNEM) IsThisDataOnThisSheet = True then

select Sheet(LNEM)

call deletedata

Else If

Sheet(ExtraLNEM) IsThisDataOnThisSheet = true

select Sheet(ExtraLNEM)

call deletedata

Else

msgbox "The data to be removed can't be found."

End If


End Sub()


I hope this appears clearer!

Regards,

Simsy

  VoG™ 07:53 07 Jun 03

I'm afraid that I don't really understand what you are trying to do - probably because it is a bit early.

Is TargetDate a named range on a sheet or is it a variable name that you are using in VBA?

Is this going to be a worksheet function used like a normal Excel function or is it going to run as part of a larger VBA procedure?

  VoG™ 08:40 07 Jun 03

Something like this:

Function DExists(rng As Range) As Boolean

Dim cel As Range

DExists = False

For Each cel in rng

If '(your test goes here) Then

DExists = True

Exit Function

Next cel

End Function


Your routine would then look like

If DExists("LNEM!A10:A29") Then

'Do something

ElseIf DExists("ExtraLNEM!A10:A29") Then

'Do something else

Else

MsgBox("error")

End If


Hope this helps.

  Simsy 11:33 07 Jun 03

I've just seen this... and now I have to go out so I can't, right now assess this too much. However, in answer to your question;

"Is TargetDate a named range on a sheet or is it a variable name that you are using in VBA?"

The answer is that it is a variable in the VBA.

Basically there are lots of checkboxes on Sheet(timesheet"), each one corresponding to a single day.

If the checkbox for a day is clicked then the date,start time and finish time is copied from "timesheet" on to "LNEM" ,(or "ExtraLNEM") if it exists. That is happening no problem.

If a human error has been made, the user can untick the checkbox and this is where this sequence comes in.....

On unticking the checkbox, the date cell associated with that checkbox is selected, and is therefore the ActiveCell The value of this activecell, (ActiveCell.value) is a variable that I have called "TargetDate"

I need to check if that date appears on LNEM. If it doesn't I need to check if it appears on ExtraLNEM. It should appear on one or the other. If it does then the appropriate code can be called, if it doesn't then the message box.

I hope this make sense?

Thanks for your time,

Regards,

Simsy

  Simsy 11:35 07 Jun 03

is that it is just needed for this one larger VBA procedure/sequence.

Thanks,

Regards,

Simsy

  VoG™ 11:40 07 Jun 03

OK then your function would include instead of

If '(your test goes here) Then

If cel.Value = TargetDate Then


You will need to either

a) declare TargetDate as a public variable at the top of the module (outside of a Sub), or (probably clearer when you come back to the code in a few months time)

b) include it in the Function declaration and the function call, e.g.

Function DExists(rng As Range, TargetDate as Date) As Boolean

and

If DExists("LNEM!A10:A29", TargetDate) Then

  Simsy 17:07 07 Jun 03

but something isn't working correctly...

I've got the following:

Function DExists(rng As Range, TargetDate As Date) As Boolean


ActiveCell.Value = TargetDate

Dim cel As Range

DExists = False


For Each cel In rng

If cel.Value = TargetDate Then

DExists = True

Exit Function


Next cel

End Function


Sub RemoveEntry()

If DExists("LNEM!A10:A29", TargetDate) Then

'Delete from LNEM sheet

Sheets("LNEM").Select
'deletion sequence

ElseIf DExists("ExtraLNEM!A10:A29", TargetDate) Then

'Delete from ExtraLNEM sheet

Sheets("ExtraLNEM").Select

'deletion sequence

Else

MsgBox "Can't find the date you are trying to delete the entry for"

End If

I am getting an eror message "Compile error. Type Mismatch" and the following is highlighted in the first line of the Remove Entry Sub;

"LNEM!A10:A29"

Can you help further.

By the way, I think I understand most of what you've come up with.... except the line that says, "For Each cel in rng"

I need the True response if the date appears in ANY cell in the range... (does this matter in your code?)

Also I'm not clear how the line "Dim cel as rng" ties in with the code. Presumeably it's saying make a "cel" a range.... can't see that it knows that "cel" is in fact a cell.

Please forgive me if I'm being thick! I do appreciate your help.

Regards,

Simsy

  VoG™ 17:25 07 Jun 03

Sorry, try this. Add near the top of the calling routine:

Dim MyRng As Range

Replace

If DExists("LNEM!A10:A29", TargetDate) Then

with

Set MyRng = Sheets("LNEM").Range("A10:A29")

If DExists(MyRng, TargetDate) Then

(and similarly with the other one).

================================

If that still generates an error, post back and I'll write a hard-coded function that simply loops explicitly through A10:A29. Using the Range is better in principle should you change the layout of your sheet you would not need to change the function, only the calling parameters.

================================

The DExists function will return True if ANY cell in the range contains the value you are looking for.

Dim cel as Range

declares cel as a Range. Because we don't specify a Range, VBA interprets this as the smallest component of a Range, i.e. a cell.

For Each cel in rng

....

Next cel


is a loop that goes through each cell in the specified Range. If it finds a match, DExists is set to True and it exits the loop.

  Simsy 17:32 07 Jun 03

I have now named the range on LNEM to be checked as "ClaimDates" and have substituted this in the code, in place of "LNEM!A10:A29"

I am geting a slightly different error message;

Compile error; ByRef argument type mismatch

Any more info I can give?

thanks in anticipation,

regards,

Simsy

  VoG™ 17:36 07 Jun 03

It's my fault for trying to be too clever, and not testing the code.

If you use the Set statement as in my previous post, VBA should accept it as a Range.

This thread is now locked and can not be replied to.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…