byref

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...

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...

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...

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...

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...

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...

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...

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...

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...

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 *...

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) /...

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...

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...

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...

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...

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...

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" ...

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...

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...