Excel VBA Help constructing a function please?

  Simsy 22:15 06 Jun 2003
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 2003

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

  Simsy 11:33 07 Jun 2003

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 2003

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

Thanks,

Regards,

Simsy

  Simsy 17:07 07 Jun 2003

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

  Simsy 17:32 07 Jun 2003

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

  Simsy 17:40 07 Jun 2003

Our my last post has crossed with yours....

That explanation you have just given is a big help to me. The reason for starting this project was as a means to learn how to use VBA, hence I dn't like to get things working without understanding what's going on!

I'll now try what you've suggested.

Thanks again,

Regards,

Simsy

  Simsy 17:47 07 Jun 2003

the "ByRef argument..." is appearing again. This time however it's "TargetDate" that is highlighted, in the following line near the top of the calling routine. I have tried "setting" this as well, using

Set TargetDate = ActivCell.Value

but this made no difference.

This is obviuosly getting confusing, and not helped I think because you don't have the full picture... I'll email you.

thanks,

Regards,

Simsy

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

Elsewhere on IDG sites

Huawei Mate 20 Pro review: In-depth hands-on

See concept art from groundbreaking video games including The Last of Us, Journey and No Man's Sky

iPhone XR release date, price & specs

Les meilleurs VPN pour Kodi (2018)