1. G

    Variant Array from one Procedure to Another

    Hi, I've got a novice question on arrays. Here is a snippet of code that is embedded in a greater procedure I have. This part of the code does the following: Loop calls FirstCalc procedure, passing an input data array (inarr), output array (outarr) and the Loop x value (x) Calls FirstCalc...
  2. Jaafar Tribak

    Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)... Cancel Argument ?

    Hi, Contrary to many other excel object model events, the Window Deactivate event does not provide a ByRef Cancel argument. I was wondering if there is some workaround so that we could choose whether to allow the workbook window to be closed or to abort the closing action. I am targeting the...
  3. spencer_time

    Trying to understand ByRef and ByVal

    Hello again guys, I'm still trying to learn VBA and excel macros as best and fast as I can and one of the ways I have been doing that is posting my problems for you guys to help me with AND looking at others posts and attempting to help them with their problems. I was trying to help someone and...
  4. CsJHUN

    type mismatch error passing multiple items with byref

    Hello guys, in advance im just learing the passing values between subs (no clue about classes too :) ), also a lazy a.. for declare items. I have a userform with (for example) a simple 2x2 + headline (3x3) crosstable. I have option buttons in the data field. I want to color the x(A or B) and...
  5. M

    .dll function in code

    Hi, I received a model with some strange function declarations. Does anyone know what this type of declaration is supposed to do? Declare Function closesession Lib "vpmsdl32.dll" (ByVal Session As Integer) As IntegerDeclare Function opensession Lib "vpmsdl32.dll" () As Integer Declare...
  6. M

    Should Worksheets And Ranges Be Passed ByVal Or ByRef?

    Hi, I need some clarity on whether it makes more sense to pass objects such as worksheets and ranges ByVal or ByRef. When it comes to simple data types the answer is clear. If you change the value of a variable in the called procedure and want that change to be passed back to the CALLER then...
  7. T

    Let Property passing argument

    I seem to be confused with regard to ByRef and ByVal. Here, regardless of ByRef or ByVal, the result in the immediate window is 100. I though ByVal DOESN'T change things. Option Explicit Dim pabc As Long Sub Test() abc = 10 Debug.Print pabc End Sub Property Let...
  8. Jaafar Tribak

    Application.Run .. (Argument Passed ByRef)

    Hi all, As you know, when passing an argument ByRef the Callee procedure code can change the value underlying the argument in the calling code as shown in the following example : Sub Caller1() Dim X As Long X = 1 Call Callee(X) MsgBox X ' returns 2 End Sub Sub...
  9. A

    VBA pass collection to collection by value, not by reference.

    I am learning VBA and trying to master collections. I am using collections because the ability to remove items seems likes it gives an advantage over arrays when using 2D. I am looking to compare source 1 to source 2. I load both sets into collections. Sample Data source 1 <tbody> A B c...
  10. A

    Need Help Again :( Sending values to a Function

    Sub Worksheet_Change(ByVal Target As Range) intColumnData = HCCRange() StrClmData = CNumToLetter(intColumnData) End Sub Function CNumToLetter(iCol As Integer) As String Dim iAlpha As Integer Dim iRemainder As Integer iAlpha = Int(iCol / 27) iRemainder = iCol - (iAlpha *...
  11. E

    Macro containing ByVal not running

    I'm attempting to call out an array in a sub through "ByVal as Varient" in another sub and thought I had done it correctly, only the macro is not running with the ByVal in it. here's the code: 'code is long, just taking relevant data. For i = 1 To NumElements MWComp(i) = (MolPerc(i) /...
  12. Luke M

    Passing an array to a function byref (default)

    Hey everyone, I've been scouring forums and tutorials looking for the answer, but I have yet to find it. I have created an array that I assign the starting and ending cell values to when the user selects a checkbox (I used the checkbox changed event). The problem I'm having is with passing the...
  13. M

    byVal vs Public

    I generally define shared variables as Public as opposed to passing them via byVal or byRef. Most commonly, I call a subroutine or function to establish a value for said variable, making it usable anywhere in my script. Will someone please explain to me the benefit of using byVal/byRef vs...
  14. J

    Passing a workbook as an object in VBA ByRef - having a problem

    I am working with multiple workbooks from a master workbook and need to pass a workbook object and worksheet object as ByRef parameters. I am successful in other functions, but am not figuring out how to do it into a function that is generic. I am using Excel 2010, workbooks are made in 2003...
  15. J

    Difficulties in calling arrays to functions or procedures using ByRef

    Good evening, Mr Excel! I'm in the process of writing a dynamic programming piece of code as part of a university case study. The assignment itself is very open-ended and doesn't mention VBA so there's no need to worry that I'm simply scounting for answers. This would be the largest piece of...
  16. L

    Using Call function in Sheet Button

    I have a macro that copies a sheet to a new workbook, opens Outlook & attaches the workbook to email and it works fine as macro called from the workbook. However when I use the call function to active this macro via a sheet button it does not work. I am guessing I will need to use ByRef but not...
  17. JenniferMurphy

    ByVal v ByRef

    It is my understanding that I should use the ByVal keyword if I am planning to modify the parameter. Is that correct? Or is it bad form to modify the function parameters at all? Example: Public Sub Write2File(byval FileID As String, Data As String) FileID = FileID & ".txt" ...
  18. N

    Need to pass only one value from procedure

    I need to pass the value of a variable from one procedure to another without running the entire called procedure Private Sub openReport(ByVal strProductFile as string) MsgBox "Open previous week's report.", vbOKOnly + vbExclamation, "Open" Application.Dialogs(xlDialogOpen).Show...
  19. Jaafar Tribak

    RaiseEvent not modifying ByRef argument as expected !

    Hi Dear all. Strictly speaking RaiseEvent is not a procedure but looks and does the same as one (ie: Executes code) . It also accepts arguments which can be passed ByVal or ByRef. When an argument is passed ByRef to a Sub/Function , the procedure can modify the argument itself however this...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back