Resources

    All about Visual Basic for Application (VBA) especially for Microsoft Excel.

Subscribe

  • Subscribe

Understanding ByVal and ByRef in VBA – Pass by Value vs Pass by Reference

Posted by | January 12, 2014.

In Visual basic for Applications when dealing with very long code we need to create another sub procedure for simplicity and better understanding because certain portion of code is repeated and can be group into one sub procedure. This sub procedure can be call when necessary from another sub procedure with calling methods but how to transfer variables between two sub procedure? The answer is ByVal and ByRef.

ByVal – Data transfer from calling procedure to called procedure
byRef – Data transfer from called procedure to calling procedure

Option Explicit
Sub CallingProcedure()
Dim x As Integer
Dim y As Integer
Dim z As Integer

    x = 10
    CalledProcedure i:=x, j:=y
    MsgBox y
    z = 20
    CalledProcedure i:=z, j:=y
    MsgBox y

End Sub 

Private Sub CalledProcedure(ByVal i As Integer, ByRef j As Integer)
    j = i * 20
End Sub

Base on the above code, CalledProcedure were call twice with different input from calling procedure. The output from CalledProcedure will be multiply by 20.

1st input x = 10, the output from CalledProcedure will be = 200
2nd input z = 20, the output from CalledProcedure will be = 400

Hope all of you understand the concept.

Click here to get your project done with 5 Dollar
Click here to buy a book
OR
Click Here!
Thanks

Share Button

Microsoft Excel Pivot Table Tutorial for Beginners – Excel 2003, 2007, 2010

Posted by | December 3, 2013.

For ordinary person sometime to understand pivot table is very difficult but with video below it will be easy. This video actually for my own reference to refresh my knowledge because I seldom use this function.

If you have any question or project please e-mail to me, I will reply and create for free depending on my free time.
Click Here to E-mail
Or
Click here to get your project done with 5 Dollar
Thanks

Share Button

Hide images together with rows or column in excel

Posted by | August 21, 2013.

Sorry, no post since January 2013! Too busy and eye problem caused by computer I think.

This morning my friend asked? He got image in excel sheet but he wanted to hide when rows or columns affected is hide. After he tried several time the image still there.  Actually the solution is under image property itself, when we select image, right click and choose “size and properties” and again under “properties”  tab we have selection as below.

  1. Move and size with cells
  2. Move but don’t size with cells
  3. Don’t move or size with cells

These 3 selection is the setting what we can do or don’t to your selected image?  In this case we have to select (1) as option.

Under the same tab “properties” we have another selection as below for image.

  1. Print Object
  2. Locked

If selection (1) is unchecked your image disappeared when print but not in excel sheet. For option (2) this locked function available when excel sheet is protected otherwise remain unchanged.

If you have any question or project please e-mail to me, I will reply and create for free depending on my free time.
Click Here to E-mail
Or
Click here to get your project done with 5 Dollar
Thanks

Share Button

How to Create Summary Sheet after processing Active or specific sheets?

Posted by | January 25, 2013.

Actually this code is for my own reference as a template for future to make my life easier because many people ask me to create summary sheet after processing certain sheets. For example we need to extract or sort specific data from current sheet and place sorted data at new summary sheet.

Option Explicit
Sub CreateSummaryList()
Dim IntSht As Integer
Dim ShtSummary As Worksheet
Dim ShtActive As Worksheet
Dim LngRow As Long, LngLstRow As Long
Dim i As Integer
'To check either user work with summary sheet or not? Yes then exit
If UCase(ActiveSheet.Name) = "SUMMARY" Then
    MsgBox "Sorry! You are trying to work with summary sheet."
    Exit Sub
'To check active sheet empty or not? Empty then exit
ElseIf WorksheetFunction.CountA(Cells) = 0 Then
    MsgBox "Sorry! Active Sheet is empty."
    Exit Sub
End If
i = 1 'Starting Row for summary
Set ShtActive = ActiveSheet 'Current sheet

'To check existance of summary sheet and delete
For IntSht = Sheets.Count To 1 Step -1
    If Sheets(IntSht).Name = "Summary" Then
        Application.DisplayAlerts = False
        Sheets(IntSht).Delete
        Application.DisplayAlerts = True
    End If
Next

'To create new summary sheet
Set ShtSummary = Sheets.Add(After:=ActiveWorkbook.Sheets(Sheets.Count))
ShtSummary.Name = "Summary"

'To work with active sheet and  place new data into summary
ShtActive.Activate
LngLstRow = ShtActive.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For LngRow = 1 To LngLstRow
    If ShtActive.Range("A" & LngRow) <> "" Then
        ShtSummary.Range("A" & i) = ShtActive.Range("A" & LngRow)
        'More code goes here
        i = i + 1
    End If
Next

'To work with Summary sheet
ShtSummary.Activate
LngLstRow = ShtSummary.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For LngRow = 1 To LngLstRow
    If ShtSummary.Range("A" & LngRow) <> "" Then
        'Your code goes here
    End If
Next
Range("A1").Select
End Sub

The above code will:

  • Confirm current sheet is not summary sheet? Summary then exit
  • Confirm current sheet empty or not? Empty then exit
  • Loop all sheet – if found summary sheet then delete (Previous summary sheet)
  • Create new summary at last sheet
  • Start working with active sheet
  • Start working with Summary sheet.

DONE..

If you have any question or project please e-mail to me, I will reply and create for free depending on my free time.
Click Here to E-mail
Or
Click here to get your project done with 5 Dollar
Thanks

Related Articles to insert code into Module:
Learn To Record And Playback Macro
Get To Know VBE (Visual Basic Editor)

Share Button

How to get Text to Display and Also link address via VBA?

Posted by | January 25, 2013.

In Microsoft Excel when have hyperlink normally the text in cell become blue and when we move our cursor on top, it will display the link address which is hidden inside. Therefore some time necessary to get Text to Display and also the hyperlink
address via code or Visual Basic for Applications. Below example will do the job for you. Please try by inserting this code into module, run and feedback to me the result, please don’t forget to put hyperlink into cell A1.

Option Explicit

Sub GetHyperlinkAndText()
Dim GetText As String
Dim GetAddress As String
On Error GoTo line1
GetText = Range("A1").Hyperlinks(1).TextToDisplay
GetAddress = Range("A1").Hyperlinks(1).Address
MsgBox "Your anchor Text = " & GetText & Chr(10) & "Your link = " & GetAddress
End
line1:
MsgBox "Sorry! Link not found."
End Sub

If you have any question or project please e-mail to me, I will reply and create

for free depending on my free time.
Click Here to E-mail
Or
Click here to get your project done with 5 Dollar
Thanks

Related Articles to insert code into Module:
Learn To Record And Playback Macro
Get To Know VBE (Visual Basic Editor)

Share Button